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 database test_cs
go
use test_cs

-- create table t
--
create table t (c int)
insert into t values (42)

-- create a bogus user to own t
--
create user bogus without login
alter authorization on t to bogus

-- create a certificate for signing
--
create certificate cs_select_t
  encryption by password = 'SimplePwd01'
  with subject = 'Certificate used to grant SELECT on t'
create user ucs_select_t from certificate cs_select_t
grant select on t to ucs_select_t

-- create a principal with low privileges
--
create login alice with password = 'SimplePwd01'
create user alice

-- verify alice cannot access t
--
execute as login = 'alice'
    select * from sys.login_token
    select db_name()
    select * from sys.user_token

    select * from t
revert

-- create a procedure that directly accesses t
--
create procedure proc_select_t as
begin
    select * from sys.login_token
    select db_name()
    select * from sys.user_token

    print 'Now selecting from t...'

    select * from t
end
go
grant execute on proc_select_t to public

-- verify procedure
--
exec proc_select_t

-- verify alice cannot access t through procedure
--
execute as login = 'alice'
    exec proc_select_t
revert

-- create special procedure for accessing t
-- this will call proc_select_t but will do some extra processing
--
create procedure proc_select_t_for_alice as
begin
    select * from sys.login_token
    select db_name()
    select * from sys.user_token

    if user_id() <> user_id('alice')
    begin
        print 'Only alice can use this'
        return
    end

    exec proc_select_t
end
go
grant execute on proc_select_t_for_alice to public

-- verify procedure
--
exec proc_select_t_for_alice

-- alice still can't use the procedure yet
--
execute as login = 'alice'
    exec proc_select_t_for_alice
revert

-- Sign procedure to grant it SELECT permission
--
add signature to proc_select_t_for_alice by certificate cs_select_t with password = 'SimplePwd01'

-- retry - it still won't work, but we'll see some nice tokens
--
execute as login = 'alice'
    exec proc_select_t_for_alice
revert

-- Counter sign proc_select_t, to make this work
--
add counter signature to proc_select_t by certificate cs_select_t with password = 'SimplePwd01'

-- retry - now it finally works
-- note that calling proc_select_t directly still doesn't work
--
execute as login = 'alice'
    exec proc_select_t_for_alice

    exec proc_select_t
revert

-- cleanup
--
use master
go
drop database test_cs
drop login alice
--
-- EOD