Why can I not DENY permissions through module signing?

In the documentation for ADD SIGNATURE, they have the following caveat:


Module signing should only be used to grant permissions, never to deny or revoke permissions.

My question is: why? Conceptually, I think of signature possibly adding to the user token. If the user linked to the certificate or key that signed the module has a DENY permission associated with it, I'd think that it would come along for the ride. My testing shows that it doesn't, but I'd like to understand why it doesn't.

Actually, DENY permissions do get applied when used via module signing. If your test indicated that nothing happened, then most likely you were experiencing the wonder of ownership chaining ;-). If a User has an explicit DENY for INSERT on a Table, but the Stored Procedure does an INSERT and has the same authorization as the Table, then the User's permission isn't even checked for what the Stored Procedure does, only that the User can EXECUTE the Stored Procedure. In order to test you need to prevent ownership-chaining from interfering, and you do that by making the statement(s) within the Stored Procedure Dynamic SQL (i.e. wrap the statement(s) in EXEC (N'...'); ). Then you can (and will, as my testing shows) see the effect of DENY permissions assigned to the Certificate- / Key- based User.

As to why the recommendation to not use Module Signing to restrict access, I would say that:

-
The default state is that permission is already implicitly denied for something, else there is no need for the module signing. There is no purpose to adding restrictions in this manner. If restrictions are needed, they can be added to the User / Login / Role.

Meaning: module signing exists because there are restrictions that need to be overcome (and impersonation is not a great way of overcoming them), not because the system is wide-open and restrictions are needed.

-
The permissions apparatus is, in itself, conceptually different: regular permissions (where DENY does work) deals with who can do certain operations (i.e. Users / Logins / Roles), but module signing deals with what can do certain operations (i.e. code such as Stored Procedures, Triggers, etc). Generally speaking, if you don't want a module to be able to do a particular operation, just don't code it to do that operation in the first place.

You can't prevent someone from attempting to execute queries that do operations that they shouldn't do, hence we have implicit and explicit DENYs. But you do have control over the code ;-)

-
While probably not a reasoning for Microsoft to have made that statement, I would say that a minor reason to avoid it is simply the confusion caused by situations such as your testing (where it appeared to not even work due to ownership-chaining doing its job).

The only time that I can think of, at the moment, to have a DENY in module signing is if module signing is used to allow Dynamic SQL to work, and the Dynamic SQL is structured to allow for something along the lines of the User passing in a Table name to do an operation on and you want to ensure that some tables are considered "off limits", though I am not sure that the DENY is even needed in this scenario (could perhaps be a case of it simply being easier to GRANT access to a Schema but DENY one or two Tables within it).

StackExchange Database Administrators Q#182818, answer score: 4