Re: Column Level Permissions Security Issue

Ross Nornes (RossNornes@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
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]

Loggin in a developer and doing a SELECT * FROM TableNameHere throws a
permission error 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 spProcThatShows_strCC and again it
shows the denied credit card field. Again, oh, oh.

Ownership chaining I suppose. That is, the procedure and the view are owned
by the same database user that owns the table. In such case the permissions
of the owner applies.

You could deny permission on the view column. You should probably deny
execution on procedures that disclose sensitive data as well.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Data security
    ... SQL Server security is reliable and it works.... ... sensitive data most of the time... ... > database. ...
    (microsoft.public.sqlserver.security)
  • Share permissions problem
    ... I had a system with sensitive data in the Shared Docs with an open ... The local user and one other system needed access to the data. ... Said I didn't have permission. ... sharing not only the folder but everything in it. ...
    (microsoft.public.windowsxp.network_web)
  • Permissions problem
    ... I had a system with sensitive data in the Shared Docs with an open ... The local user and one other system needed access to the data. ... Said I didn't have permission. ... sharing not only the folder but everything in it. ...
    (microsoft.public.windows.server.sbs)
  • RE: Permission Denied Create Object
    ... You do not have write permission to one of the directories that the component ... The best thing is to install "Filemon" (you can ... > I can run the excel on the server when I am logged on into it. ... Gave script and execute rights ...
    (microsoft.public.inetserver.iis)
  • Re: Database Role that allows execution of stored procedures?
    ... No built-in role like that in SQL Server 2000. ... > interaction with the database must be done through stored procedures i.e. ... > create my own role and grant it execute rights on each SP and no rights to ...
    (microsoft.public.sqlserver.server)