0

I have created a database level role: NonBillingRole.

CREATE ROLE NonBillingRole

then granted it all access and revoked/denyed access of Billing table through following command:

REVOKE SELECT, INSERT, UPDATE, DELETE ON TblBilling TO NonBillingRole
DENY SELECT, INSERT, UPDATE, DELETE ON TblBilling TO NonBillingRole

Then I have added a user to this role.

This works fine if I directly tries to select or modify the table TblBilling. But the user can select and modify the table through any stored procedure.

My goal is to create a role that have all access to the database including executing the stored procedure but do not have direct or indirect access (even through stored procedure) to the table TblBilling.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Yash
  • 356
  • 1
  • 5
  • 22
  • 1
    When objects are used within stored procedures and other modules, permissions are not checked on indirectly referenced objects when the objects have the same owner (ownership chain). The implication is you should grant execute permissions on stored procedures only to authorized users/roles. You can assign a different owner (e.g. `ALTER AUTHORIZATION ON OJBECT::TblBilling TO BillingOwner`) to break the chain but that will require granting direct permissions on the TblBilling table to authorized users. – Dan Guzman Mar 02 '20 at 11:15
  • 1
    Adding there is no need to use `DENY` in your example since the role doesn't have any granted permissions to deny. – Dan Guzman Mar 02 '20 at 11:17

1 Answers1

0

Deny's are higher than allows - but procedures can have impersonation abilities, or run as owner, which can circumvent your intentions.

When you add access to the role for table, and DENY that will put some higher level access constraints in place, but it can also cause issues down the road for Views, Stored Procedures, and Functions. I rarely use DENY as it causes unseen issues with getting to data.

Make sure the user you have is public only, and make a role for only the access you want them to have to the objects you intend for them to use. If an application controls access, you will be hard pressed to constrain access in the database without consequences in the application.

J. Frank Johnson
  • 24
  • 2