April 19, 2010 at 4:21 am
I am trying to Deny select on SSN column to few users using below statment. But I have noticed that even though it is preventing user from getting ssn value using "SELECT SSN FROM student" statement. It is returning SSN when executes GetStudent SP which has the same select statment. How to prevent user from selecting this SSN in any form.
DENY SELECT (ssn),references ON student TO UserID -- DENY SELECT on column SSN
April 19, 2010 at 10:05 am
if you grant execute of this Stored Procedure for this user Explicitly he gets back the permissions to read all the columns selected by this SP
what you need to do is, leave the permissions of the SP blank without explicit any permissions for him
April 19, 2010 at 10:59 am
I think you have a larger problem.
The users you don't want to have access to the data should be at LEAST not granted EXEC rights on the sproc, and perhaps even DENY. Also, by and large why do users have access to the base objects anyway. Access should be limited to sprocs and perhaps views..
It is important to understand the SQL Security model, unless ownership of the objects in a chain changes permissions are not checked. What this means is that the user that compiled the sproc had the rights to see the contents of that column and that the sproc and table are owned by the same user (probably dbo). So, the sproc can access anything in the local database owned by dbo without additional permissions.
Your might want to create an application role and give it a password for users that are allowed to call this sproc. Then the application has to take explicit steps to access the SSN data.
Is any of this clear?
CEWII
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply