Re: Column Level Permissions Security Issue

Thx for the reply Erland!

All objects are owned by DBO.

Ya, I could deny access on the view column and the procs, if I have to I
will, but the issue is these are legacy applications and will take HOURS and
HOURS to dig through all the code to determine all places where sensitive
data fields are located and grant specific permissions on each object.

I'm hoping I just have a huge laps in my understanding here, but, if a role
is granted SELECT rights as in "GRANT SELECT TO RWE", then a DENY is placed
on a table or column, it should not matter how the table is called, the deny
should be enforced. Since the deny works if I hit table directly with a
SELECT * FROM [TableNameHere], why would a view, proc, function or anything
else not obey the deny permission? This seems to be a HUGE hold in basic
security enforcement unless I'm totally missing how this works. If this is
true, shops like my shop that need to allow developers read access to their
databases but also need to not allow them access to secure files like credit
card numbers, we are going to have to code review ever single view, proc,
function, etc, thats being moved out since those appear to not obey the deny
permissions.

Is there a better way to achieve this same protection without the huge hours
of item by item code review? Whats the point of deny permissions if this is
how they function?

I really hope I'm just missing something here, but at this point I pretty
frustrated with this aspect of the security system right now...

Ross

"Erland Sommarskog" wrote:

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: how to restrict users to search in their own Organizational Unit
    ... I also want to say that in fact you shouldn't deny the read permission to anyone and this scenario the MOSS Administrators or who is responsible for Add users to Your Sites should be carefull when performing this action. ... Now, because you're dealing with many users, my recommendation is to create THE NECESARY Security Groups in each OU and related them with your MOSS2007 existing security groups, in future when someone creates some user, you just have to add that user to the necessary group and that user will be given the necessary permissions. ... decided a script can make it possible to accomplish, ... > If I need to create a security group per OU and then add all users ...
    (microsoft.public.windows.server.active_directory)
  • Re: Share Permissions: Deny behaviour
    ... Deny overrides all other permissions. ... There are two types of Deny (again goes for share and NTFS). ... explicit allow permission, then you're stuck with implicit deny. ...
    (microsoft.public.windows.server.general)
  • Re: how to restrict users to search in their own Organizational Unit
    ... decided a script can make it possible to accomplish, ... You could also TRY removing the "Authenticated Users" ... Domain level since using a lot of DENY ... permissions is in and of itself a poor practice. ...
    (microsoft.public.windows.server.active_directory)
  • Re: NTFS Security Question.
    ... I was not sure that deleting the special permissions would work but you ... Since Windows 2000 deny NTFS permission does not work ... originally configured "closer" to the object in the chain of folders. ...
    (microsoft.public.windowsxp.security_admin)
  • RE: Exmerge errors
    ... To do this open regedit on the system you are administering Exchange ... A Deny does overrule an allow IF they are both inherited. ... An explicite allow at the store level will over-ride the inherited Deny. ... I cannot see where or how to override these permissions. ...
    (microsoft.public.exchange.admin)