Procedure with Execute as login?

Sometimes we need a low-privileged user to do a specific administration task or task that require some server-level permissions (such as VIEW SERVER STATE, ALTER TRACE etc). Of course, we do not want to give that account server-level privilege, because it would not comply to the “least possible set of privileges” security recommendation. Sometimes we need a finer grained server-level permission, or to limit server-level permission to certain databases for example. That is advanced security task for a DBA, and I will show you here how to do it through an example.

Custom sp_who2

We will build a procedure that calls sp_who2, let low-privileged user to execute it and see all the processes on the server, without giving the VIEW SERVER STATE to that low-privileged account, as that privilege would give the user much broader rights than just calling sp_who2. The problem is that procedure is by default executed as caller, and the low-privileged caller of sp_who2 can’t see any sessions except his own. Let’s try:

Output of the procedure is below:

whoami_lowpriv

The first recordset shows the database user, impersonated login, and my real (original) login. Second recordset (sys.login_token) shows we have LowPrivLogin and public server role context execution tokens. Third recordset shows that we have CONNECT SQL and VIEW ANY DATABASE server privileges we received from the public role. Fourth recordset is the output of sp_who2: only one row is there, our session, because we do not have VIEW DATABASE STATE on any database, and we certainly do not have VIEW SERVER STATE to view them all.

Even if we uncomment “WITH EXECUTE AS ‘dbo’” (or SELF, or OWNER, or any other user) the sp_who2 inside a procedure won’t give us all the sessions on the server.

Why “WITH EXECUTE AS ‘dbo’” won’t help?

In batch statements we can use:

The problem is that database user can only receive database-level privileges. VIEW SERVER STATE is a server-level privilege and cannot be assigned to a database user. Server-level privilege can only be assigned to a server-level principal: login.

That is promising!

When creating a procedure, function, DML trigger, or database-scoped DDL trigger there is “WITH EXECUTE AS <something>” clause available. But, unfortunately, that clause only accepts database users, not logins! SELF and OWNER are also resolved to a certain database user: SELF is resolved to a db user that executed a “CREATE PROCEDURE” statement, and OWNER is resolved to a db user that owns a procedure. Even “dbo”, a special database user whose rights are not checked at all, is scoped to a database and can’t receive a server-level privilege. Only server-scoped triggers can have “with execute as <login>” clause: server-level DDL triggers and logon triggers.

Since we can’t define a procedure to use “WITH EXECUTE AS <login>”, our plan to create a procedure to execute under server-level privilege is doomed to a failure! Or is it?

Procedure “WITH EXECUTE AS <login>” is possible ?

Not really, not without the tricks. And the “trick” is in signing the procedure. Signing the procedure with a certificate makes it executing under login associated with that certificate. Certificate is like a glue that connects the procedure to execute under specific login. That login is a special kind of login called “LOGIN mapped to a CERTIFICATE”. Nobody can’t actually log-in with that login. It servers just to receive a server-level permissions. And the code (procedure) can run under that login if it is signed by that login’s certificate. Sounds complicated? And it is! But once you understand how it works, it’s not so hard.

Use certificate or asymmetric key?

We cannot sign the procedure with symmetric key. That leaves us to choose between certificate and asymmetric key. Certificate is asymmetric key with some metadata added, like subject and expiration date. But the most important difference for us is that certificate can be backed-up and restored separately from database. Asymmetric key can only be backed-up and restored with a database backup/restore he lives in. We will need the same certificate to be in two databases:

  • master – because certificate mapped to a login can only be in master database
  • our target database – we can sign a procedure only with a certificate that is in the same database as the procedure

Because we need to copy the same certificate to another database, it is much easier to do it with certificate. With asymmetric key we could probably restore the whole database over another database, and recreate all the objects and data previously exported, or mess with updating system tables which is not supported and of course, not recommended.

Get our hands dirty!

First, we will create a certificate in the master database, and create a login mapped to that certificate. Then copy that certificate from master to our database via backup/restore of the certificate. Then we will sign our procedure with that certificate.

Now, let’s see the result:

whoami_hipriv

As you can see in first recordset, I am still low-privileged login and user. But in second recordset you see two new rows: HighPrivCert and HighPrivCertLogin. In third recordset you can see one new permission is added: VIEW SERVER STATE. And the last recordset shows all the processes on the servers, exactly what we wanted!

You can view the list of signed code through sys.crypt_properties view:

This is tested on SQL2012, but should work on 2005+.

Conclusion

You can grant server-level privileges to a code (procedure, UDF, trigger) indirectly, through a code signing. We had example with sp_who2, but you can encapsulate any functionality that requires elevated privileges in a secure way, without giving that high-level privilege to a user.

 

Posted in Security and encryption
3 comments on “Procedure with Execute as login?
  1. Andre says:

    I liked your code and examples very much.

    I took the code verbatim from your article and although it ran in SQL 2008r2, it failed to show any information outside of login LowPrivLogin for the procedure

    execute as login=’LowPrivLogin’
    exec dbo.show_processes

    Output did show as below. Any ideas?
    ——————————————-
    database_user login original login
    LowPrivUser LowPrivLogin SW\aquitta

    312 0x932E9FFE314652468C8E1EBAF7F1F028 LowPrivLogin SQL LOGIN GRANT OR DENY
    2 0×02 public SERVER ROLE GRANT OR DENY

    entity_name subentity_name permission_name
    server CONNECT SQL
    server VIEW ANY DATABASE

    SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID REQUESTID
    63 RUNNABLE LowPrivLogin PDX-L-AQUITTA . TestCert SELECT INTO 327 562 01/16 20:02:34 Microsoft SQL Server Management Studio – Query 63 0

    • Vedran Vedran says:

      The results from your case show that the procedure is not executed under elevated rights. You probably did not signed the procedure, or you altered the procedure after signing it (“alter” deletes signature). Without a signature with certificate, procedure is not connected to a login, and thus does not execute under the high level priviledges granted to HighPrivCertLogin. After every ALTER of the procedure, you need to sign it again (ADD SIGNATURE TO … BY CERTIFICATE …).
      Have you tried to run the example script from the blog post, exactly as it is, without any changes from start to end – does that work for you?

  2. Davorin says:

    Great article.I would like to thank you for this article coz it solved my problem for creating backup on ms sql express.While i was testing i had simular problem.When stored procedure had some error while executing,signiture was deleted so i created new procedure which task was creating signiture for second procedure before second procedure was executed.

    Something like this:

    If (Select Count(*)
    From sys.crypt_properties cp
    Left Join sys.certificates c on c.thumbprint = cp.thumbprint
    Left Join sys.asymmetric_keys ak on ak.thumbprint = cp.thumbprint
    Where object_name(major_id)=’sp_BackupDatabase’)=0
    Begin
    ADD SIGNATURE TO OBJECT::dbo.sp_BackupDatabase
    By CERTIFICATE BackupCert
    WITH PASSWORD=’YOUR_PASSWORD’
    End

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

M.Sc. Vedran Kesegić

M.Sc. Vedran Kesegić

A Random Thought

You don't have a backup until you tried to restore it! Validate your backups. At least, use RESTORE VERIFYONLY immediately after a backup, and use CHECKSUM option when doing a backup.