0

Deny usually has more priority than grant. For example grant select is overriden if there is deny select.

However, when it comes to permission chaining, suppose user has access to the stored procedure or view, and the owner of the view is same as the owner of the table used in the view, then even if the user has deny on the table, he can still see the table data.

Is there any way to enforce deny when permission chain is involved?

variable
  • 8,262
  • 9
  • 95
  • 215
  • 1
    This is by design. If you don't want a user to be able to access an object via permission chaining they shouldn't have access to the object they are getting the chaining permissions from. Or, perhaps, you should be changing the owner of one of the objects and then the people who should have access to the object should be `GRANT`ed explicit access to it. – Thom A Oct 16 '21 at 12:03

1 Answers1

1

I think you mean ownership chain rather than permission chain. When the owners of indirectly referenced objects are the same as the directly referenced object (proc, view, etc.), permissions on indirectly referenced objects are not checked. Users only need permission on the directly referenced object.

To avoid ownership chaining behavior, change the owner (authorization) of at least one of the indirectly referenced objects. This will break the ownership chain so that GRANT and DENY permissions permission are honored as if the objects were accessed directly. Keep in mind that no access is permitted unless a GRANT without an overriding DENY exists.

Alternatively, simply do not grant permissions on a directly referenced object unless you want someone to use the encapsulated functionality. This allows you to leverage ownership chaining as a security measure, granting permissions only on procs or views while preventing direct ad-hoc access to underlying tables.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71