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?
