locked
Deny Permissions Not Being Honored!! RRS feed

  • Question

  • SQL2K5 SP2

    I’m trying to lock down PCI sensitive data columns in some product databases
    from our developers. We need developers to have data reader rights to the
    production database to perform general maintenance and troubleshooting of the
    application BUT minus the specific secured columns like credit card number,
    etc.

    I have a user role setup called RWE created by:
    CREATE ROLE RWE
    GRANT SELECT TO RWE
    GRANT INSERT TO RWE
    GRANT UPDATE TO RWE
    GRANT DELETE TO RWE
    GRANT EXECUTE TO RWE

    A standard SQL user is placed inside this role allowing them full read,
    write, and execute rights on everything in the DB which is fine.
    BUT, now we want those same rights except for the sensitive data files so I
    updated the rule with the following script:

    DENY SELECT ON [dbo].[TableNameHere] ([strCC]) TO [RWE]

    Logging in a developer and doing a SELECT * FROM TableNameHere throws a
    permission error on the strCC column as expected, so far so good.

    But, I did a SELECT * FROM ViewThatContainsField_strCC and shows them the
    denied field. Oh, oh!  I also did EXEC spStoredProcThatShows_strCC and again it
    shows the denied credit card field. Again, oh, oh!

    So, long story short, I’m stuck. My understanding of SQL permissions, I was
    under the understanding that placing a deny at the lowest level (column)
    should throw errors in all methods of access that column (either by direct
    select, or select through views, procs, etc).

    Any suggestions or any hints on what I’m missing here?

    Thx ahead of time!
    Friday, November 9, 2007 4:46 PM

Answers