September 13, 2007 at 2:00 pm
I currently have a schema "DBA" to hold Database Administrative stored procedures/tables etc.. I need to deny access to this schema to all users. This is not a problem of course...just deny all actions in the schema permissions window.
The rub is that I need to grant insert to one table within that schema and also execute to one stored procedure within that schema (has to do with a DDL trigger and auditing).
Is there a way to deny permissions at schema level and then add them back at the object level? When I try granting the specific permissions I need directly to these objects, the users still get denied based on the broad schema level permissions. Thoughts????
September 26, 2007 at 9:58 am
Why do you need to specifically deny privileges? If a user is not granted access to object then it cannot access the object. The deny is used to override privileges the user was assigned somewhere else. So if user X was assigned to Role A and Role A has read privileges to all tables. Now you do not want user X to be able to read data from a specific table, rather then removing the user from Role A and creating a whole new role for one user, you can DENY user X read privileges to the table. All other users in Role A can read the table except user X but user X can still read all the other table.
In your example, once you deny a user from your schema, you cannot override this at an object level since the deny privileges takes precedence. If you do not grant any privileges to the user except for SELECT on one table and EXECUTE on one stored procedure, then those actions is all the user will be able to do. I would recommend you assign this to a database role then add the user to the role.
If you are really concerned, then you could still add the Deny privileges to the schema, just leave the user out of the deny select, execute and control actions. You could then deny the user select and execute to all the other tables and procedures individually, then Grant the user Select and Execute to the one procedure. I would think this was overkill but maybe you may have compliance/security reasons to specifically add the Deny.
One other thought, you could create a stored procedure to read the table. Add the EXECUTE AS statement to execute the procedure as the Owner or a new user that has privileges to the table. The user would only need execute rights to this procedures and not the underlying table. Generally a good practice to not give access directly to table but rather use procedures when you can.
Hope this helps
David
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply