A while ago, I wrote a post showing how signatures can be used to allow users to perform operations without explicitly granting them the permissions required for that operation. In this post I'll present more details about the use of signatures.
One important thing to keep in mind when working with signatures is that, normally, they will only have effect while the signed module is executed. When executing a signed module, the signatures will be temporarily added to the SQL token, but they will be lost if the module executes another one or if it terminates execution. In some situations, you may want the signatures to be persisted across multiple module calls. This is where counter signatures can be used. A counter signature is a special form of signature: by itself, it does not grant any permissions; however, it allows signatures made by the same certificate or asymmetric key to be kept for the duration of the call made to the counter signed module.
I bet this sounds rather complicated, so let's go over an example that is slightly less complicated (TSQL code is at the end of this post). Let's say we have user Alice calling procedure ProcAlice, which calls procedure ProcT, which selects from table T. Alice has EXECUTE permission on ProcAlice and ProcT, but she cannot select from T, and no ownership chaining is involved in this entire chain. So Alice cannot access T - neither directly, nor through the use of ProcAlice and ProcT. The tricky part is that we want Alice to always use ProcAlice for access - we don't want her to use ProcT. How can we accomplish this? Sure, we could sign ProcT, such that ProcT can access T, but then Alice can invoke ProcT directly - she doesn't have to call ProcAlice. We could deny EXECUTE permission on ProcT to Alice, but then Alice would not be able to call ProcT through ProcAlice either (remember that no ownership chaining is involved). Signing ProcAlice would not work by itself, because the signature would be lost in the call to ProcT. However, by counter signing ProcT with the same certificate used to sign ProcAlice, SQL Server will keep the signature across the call chain and will allow access to T; also, if Alice will attempt to call ProcT directly, she still won't be able to access T, because the counter signature doesn't grant any rights.
This is the essence of the TSQL example shown below. In the example, ProcAlice appears as proc_select_t_for_alice, and ProcT is proc_select_t. I have not tried to eliminate the ownership chaining between these two. While running through this example, I recommend examining how the state of the login and user tokens is changed by adding the signatures. Finally, do not use this example as an example for how to select passwords :)
-- A demo for counter signatures---- create test database--
-- create table t--
-- create a bogus user to own t--
-- create a certificate for signing--
-- create a principal with low privileges--
-- verify alice cannot access t--
select * from trevert
-- create a procedure that directly accesses t--
print 'Now selecting from t...'
-- verify procedure--
-- verify alice cannot access t through procedure--
-- create special procedure for accessing t-- this will call proc_select_t but will do some extra processing--
exec proc_select_tendgogrant execute on proc_select_t_for_alice to public
-- alice still can't use the procedure yet--
-- Sign procedure to grant it SELECT permission--
-- retry - it still won't work, but we'll see some nice tokens--
-- Counter sign proc_select_t, to make this work--
-- retry - now it finally works-- note that calling proc_select_t directly still doesn't work--