When designing an application for SQL Server, you rarely want users to have full permissions to access the tables in the database. Many applications are designed to perform all database access through stored procedures, and it is through the stored procedures users can access and update data. The procedures perform validations of business rules to protect the integrity of the database.
In this article I will in depth discuss three different ways to achieve this:
I will also briefly cover three other ways:
This article applies to SQL 2005 SP2 and later. Particularly, there were some bugs and limitations in the RTM version of SQL 2005, that I don't touch, as there is no reason why you should be running SQL 2005 RTM or SP1. (And for that matter, SP2. You should have installed at least Service Pack 3 by now, if not SP4.) If you are using SQL 2000, you should know that this article focuses on features added in SQL 2005.
Notes on the Example Scripts
Some Philosophy on Security
Creating Objects in a Schema
How it Works
Example of Ownership Chaining
Not All Permissions are Transferable through Ownership Chaining
Signing Procedures with Certificates
Using Certificates within a Database
Creating the Certificate
Creating the Certificate User
Granting Rights to the Certificate User
Signing the Procedure
Putting it All Together
Using Certificates with Bulk Copy
Full Example for Bulk-load
CREATE CERTIFICATE FROM BINARY in SQL 2012
Using Asymmetric Keys
Which Procedures are Signed by Which Certificates?
Managing Certificates and Passwords
Impersonation with EXECUTE AS
The Statement EXECUTE AS
Using EXECUTE AS to Grant Permissions within the Database
The Side Effects of EXECUTE AS
WITH EXECUTE AS CALLER | SELF | OWNER
The EXECUTE AS CALLER statement
Using DDL Triggers to Stop All This
EXECUTE AS and Monitoring
EXECUTE AS and Access Outside the Database
Using EXECUTE AS to Give Bulk-Copy Permissions
Considerations on TRUSTWORTHY
Yet another Side Effect – WindowsIdentity
Acknowledgments and Feedback
The classic method for granting permissions through stored procedures is ownership chaining. This is the prime method for plain table access, but there are permissions that are not grantable through ownership chaining. Two such cases that we will look at in this article are dynamic SQL and reloading a table through BULK INSERT. Due to its importance, ownership chaining is the first mechanism that I will cover in this article. However, before that I will discuss owner/schema-separation, a change in SQL 2005 that may boggle the mind of old-time users of SQL Server and which has some effects on ownership chaining.
SQL 2005 introduced two new methods to give users access through stored procedures: you can sign procedures with certificates, and you can use impersonation with the EXECUTE AS clause. Both these methods permit you to encapsulate any permission in a stored procedure. Certificates are more complex to use, whereas EXECUTE AS can be deceivingly simple. To wit, EXECUTE AS has some side effects that can be nasty. If you are a developer, this text tries to make you aware of what harm casual use of EXECUTE AS could cause. And if you are a DBA, this article warns you of what creative developers can inflict to your database with EXECUTE AS.
Whereas the above-mentioned methods can be applied to individual procedures, application roles, "application proxies" and Terminal Server are solutions that you typically use on an application-wide scale. (I have put "application proxy" in quotes throughout the article, as this is a term that I've coined myself and it may not be established terminology.)
This article includes several example scripts that demonstrate the various methods. Before you start to run these scripts all over town, I like to point out a few things.
All these scripts assume that you are logged in with sysadmin rights, and I strongly recommend that you run the examples on a development machine. Some scripts assume that you have enabled xp_cmdshell, which is disabled by default. Enable it with sp_configure, if this is acceptable with your local security policy. The use of xp_cmdshell is mainly for convenience, and it is not required to demonstrate the essentials of the examples. You can perform those actions manually if needed.
Furthermore, all scripts create at least one database and at least one login. Some scripts also create files in the file system. If the scripts run uninterrupted, all objects are dropped at the end; logins, databases and files alike. (So first check that you don't have any database with names that coincide with the databases in the scripts!)
The reason the scripts create databases is simplicity. That permits me to create objects, users etc in the database, and clean up all by dropping the database. The scripts create logins because it's difficult to demonstrate security features when running as sysadmin.
To contain everything into one script, I make heavily use of the EXECUTE AS and REVERT statements, although it will take until the second half of the article before I discuss them in detail. For now, just think of them as an alternative to open a second query window to run as a test user. If you prefer, you can stop the scripts at EXECUTE AS, log into a second query window as the test user to run the part up to REVERT.
Before I go on to the main body of this text, I would like to make a short digression about security in general.
Security is often in conflict with other interests in the programming trade. You have users screaming for a solution, and they want it now. At this point, they don't really care about security, they just want to get their business done. But if you give them a solution that has a hole, and that hole is later exploited, you are the one that will be hung. So as a programmer you always need to have security in mind, and make sure that you play your part right
One common mistake in security is to think "we have this firewall/encryption/whatever, so we are safe". I like to think of security of something that consists of a number of defence lines. Anyone who has worked with computer systems knows that there are a lot of changes in them, both in their configuration and in the program code. Your initial design may be sound and safe, but as the system evolves, there might suddenly be a security hole and a serious vulnerability in your system.
By having multiple lines of defence you can reduce the risk for this to happen. If a hole is opened, you can reduce the impact of what is possible to do through that hole. An integral part of this strategy is to never grant more permissions than is absolutely necessary. Exactly what this means in this context is something I shall return to.
Before we look at any of the methods to grant permissions, we need to look at a change in SQL 2005 which can be a bit breath-taking to users coming from older versions of SQL Server.
Since the dawn of time, SQL Server have permitted a four-part notation of objects, and it has usually been presented as
But in SQL 2005 this changed to
You may ask, what is this schema? The answer is that schema has always been there, but up to SQL 2000, schema and owner was always the same. In SQL 2005 owner and schema are two different entities.
The purpose of a schema is simple to understand: it permits you to have different namespaces in database. Say that for a larger application, there are several groups that work more or less independently. Each group could have their own schema for their specific objects to avoid name clashes. While you could do this in SQL 2000 as well, the fact that all schemas had different owners, made this unpractical. In SQL 2005 all schemas can have the same owner.
An example of a database with several schemas is the AdventureWorks database; the database which Microsoft use for all their samples since SQL 2005.
SQL Server comes with no less than 13 pre-defined schemas. That's a lot, but ten of them exist solely for backwards compatibility, and they are namesakes with predefined users and roles in SQL 2000. (Since users and roles also were schemas in SQL 2000, Microsoft figured that there could be applications using them.) You can drop the nine schemas that stem from roles (db_owner etc) from your database, and if you drop them from the model database, the schemas will not appear in new databases. For some reason you cannot drop the guest schema.
Two schemas, sys and INFORMATION_SCHEMA, are reserved for system objects, and you cannot create objects in these schemas.
Finally, there is the dbo schema, which is the only predefined schema you normally create objects in. The tacky name is short for database owner, and is a heritage from the previous days of owner/schema-unification.
There are several statements related to schemas and users, and I will give a brief overview here to point out the differences between the new commands added in SQL 2005, and the older system procedures from previous versions.
To create a schema, you use not surprisingly CREATE SCHEMA, and most often you just say like:
CREATE SCHEMA myschema
CREATE SCHEMA is one of these statements that must be alone in batch. That is, no statements can precede or follow it. That may seem a little funny for such a simple command, but there is an older form of CREATE SCHEMA which is more complex that was introduced in SQL 6.5 and which serves a different purpose. (Please see Books Online for details, if you really want to know.)
The preferred way to create a user since SQL 2005 is:
CREATE USER newuser [WITH DEFAULT_SCHEMA = someschema]
There are two system procedures to create users, sp_adduser and sp_grantdbaccess. They are both deprecated and will be removed eventually. There is an important difference between CREATE USER and the two system procedures: CREATE USER creates a user whose default schema is dbo, unless you specify otherwise. On the other hand, sp_adduser and sp_grantdbaccess for compatibility reasons perform the corresponding to:
CREATE SCHEMA newuser go CREATE USER newuser WITH DEFAULT_SCHEMA = newuser go ALTER AUTHORIZATION ON SCHEMA::newuser TO newuser
(The last command makes newuser owner of the schema created in his name.) Most likely, you don't need that schema, so there is good reason to avoid these old system procedures entirely. CREATE USER also has some options not offered by sp_adduser and sp_grantdbaccess. For instance, you can say:
CREATE USER thisdbonly WITHOUT LOGIN
This creates a database user that is not tied to a login. In some of the the test scripts, I use this option to create test users, but you will also see examples where WITHOUT LOGIN can be used to create a user that is a container for a certain permission. We will look at other options later in this article.
There is also CREATE ROLE that replaces sp_addrole in the same vein that CREATE USER replaces sp_adduser. That is, CREATE ROLE creates the role only. sp_addrole also creates a schema that you are unlikely to have any need for. And while we are at it, there is a CREATE LOGIN which replaces sp_addlogin. As with CREATE USER, CREATE LOGIN has some new options, that we will come back to later in this article.
Finally, there is DROP USER instead of sp_dropuser etc. A little note here: if you have users created with sp_addlogin or sp_grantdbaccess, sp_dropuser is the most convenient way to drop them, since there is a schema that needs to be dropped before you can drop the user, and DROP USER will not do that for you.
If you create objects in a schema that is owned by another user, the schema owner will be the owner of the objects you create, not you. Thus, if you give a user permission to create objects in a schema you own, but no other permissions in the schema, he will not be able to access the objects he creates.
This can be a bit of a surprise, but it's actually logical. Assume that all developers of an application have their own user, while they create objects in a common schema. For ownership chaining to work (which we look at in a second), all objects must have the same owner, so it much simpler if all objects are owned by the schema owner from the start. Else you would constantly have to change the ownership of the procedures.
Ownership chaining is the classical way of giving users access to objects through stored procedures in SQL Server. And while SQL Server provides two other methods, ownership chaining is what you will use 99 % of the time. Certificates and impersonation is something you only have reason to use when ownership chaining does not do the job.
How does ownership chaining work? Say that you have a procedure sp1 owned by user A. sp1 performs a SELECT from tbl1 and tbl2. tbl1 is owned by A, whereas tbl2 is owned by user B. User C has permission to execute sp1. To be able run this procedure successfully, C needs SELECT permission on tbl2 but not on tbl1. Since sp1 and tbl1 have the same owner, the permission check is suppressed, and this is ownership chaining. Ownership chaining is also in effect in triggers, user-defined functions and views.
Now, this may seem a little complex to grasp, but in real life it is often a lot simpler. In my experience, having several object owners in a database is not very common. In very many cases, dbo, the database owner, owns all objects in a database. A common way to implement security in a database application is to perform all access through stored procedures that validates input parameters, enforces business rules etc. When dbo owns all procedures and tables, users only need permissions to execute the stored procedures. Thanks to ownership chaining, they do not need any direct permissions on the tables. But as we will learn soon, there are permissions that cannot be transferred through ownership chaining.
Note: in older versions of SQL Server, applications might have used different object owners in order to implement different namespaces, that is schemas. But since in SQL 2005, dbo can own all schemas, this should no longer be necessary.
Here is an example script that demonstrates ownership chaining. Despite what I said in the previous section about dbo owning everything, the example includes two objects owned by other users, to demonstrate what happens when the ownership chain is broken.
(Please refer to the introductory note about the example scripts in this article.)
USE master go -- Create a test user and a test database. CREATE LOGIN testuser WITH PASSWORD = 'TesT=0=UsEr' CREATE DATABASE ownershiptest go -- Move to the test database. USE ownershiptest go -- Create a user to run the tests. CREATE USER testuser go -- Create two database-only users that will own some objects. CREATE USER procowner WITHOUT LOGIN CREATE USER tableowner WITHOUT LOGIN go -- Create three test tables. As this is an example to demonstrate -- permissions, we don't care about adding any data to them. CREATE TABLE tbl1 (a int NOT NULL) CREATE TABLE tbl2 (b int NOT NULL) CREATE TABLE tbl3 (c int NOT NULL) go -- Make the user tableowner owner of tbl3. ALTER AUTHORIZATION ON tbl3 TO tableowner go -- Create a couple of stored procedures. CREATE PROCEDURE sp1 AS SELECT a FROM tbl1 go CREATE PROCEDURE sp2inner AS SELECT a FROM tbl1 go CREATE PROCEDURE sp2 AS SELECT b FROM tbl2 EXEC sp2inner go CREATE PROCEDURE sp3 AS SELECT c FROM tbl3 go CREATE PROCEDURE sp2procowner AS SELECT b FROM tbl2 EXEC sp2inner go -- Make procowner the owner of sp2procowner. ALTER AUTHORIZATION ON sp2procowner TO procowner go -- Grant permissions to testuser to execute all procedures, -- except for sp2inner. GRANT EXECUTE ON sp1 TO testuser GRANT EXECUTE ON sp2 TO testuser GRANT EXECUTE ON sp2procowner TO testuser GRANT EXECUTE ON sp3 TO testuser go -- Run some commands as testuser, with its permissions etc. EXECUTE AS LOGIN = 'testuser' go -- sp1 runs fine, as dbo owns both sp1 and tbl1. PRINT 'EXEC sp1, this runs fine' EXEC sp1 go -- Also sp2 runs fine. Note that testuser can run sp2inner, when -- it's called from sp2. Ownership chaining applies here as well. PRINT 'EXEC sp2, this runs fine, despite no priv on sp2inner' EXEC sp2 go -- But sp2procowner fails twice. Because sp2procowner has a different -- owner than tbl2 and sp2inner, testuser would need direct permission on -- these objects, but he hasn't. PRINT 'EXEC sp2procowner, two permission errors' EXEC sp2procowner go -- And this fails as well, because while sp3 is owned by dbo, tbl3 is -- owned by another user, so ownership chaining is broken. PRINT 'EXEC sp3, permission error' EXEC sp3 go -- Stop being tester and clean up. REVERT go USE master go DROP LOGIN testuser DROP DATABASE ownershiptest
Since ownership chaining is so commonly used, and works so smoothly when all objects are owned by dbo, it often comes as a surprise when users get a permission error when they run a stored procedure.
The story is that ownership chaining does not apply to all statements. Essentially, ownership chaining applies to DML statements (SELECT, INSERT, DELETE, UPDATE and MERGE) and EXECUTE of stored procedures and functions. If you put a statement like CREATE TABLE into a stored procedure, the user must have permissions to create tables (which a plain user rarely has, save for temp tables). Same goes for many other administrative commands.
A statement that is worth special mention here is TRUNCATE TABLE, which logically is a DML statement; a quicker way to delete all rows in a table. But the permissions for this command are not transferable through ownership chaining, so if you want to write a stored procedure to permits users to empty a table, you may prefer to use DELETE although this is less efficient.
Another example of a command where ownership chaining does not work is BULK INSERT; this command requires a server-level permission.
These are situations that can be resolved by signing procedures with certificates or by using impersonation with EXECUTE AS, methods that we shall look into later in this article.
Another case where ownership chaining does not work is dynamic SQL. Consider:
CREATE PROCEDURE myproc AS EXEC('SELECT a, b FROM tbl')
(This is certainly not how you would use dynamic SQL in real life, but I wanted to keep the example short. Please see my article The Curse and Blessings of Dynamic SQL for a longer discussion on dynamic SQL, when to use it – and when to not.)
To run this procedure, a user needs SELECT permissions on tbl. The reason for this is that the batch of dynamic SQL is a scope of its own that is not part of the stored procedure. And this batch does not really have any owner at all, and thus the ownership chain is broken.
Since dynamic SQL is very powerful for some tasks – dynamic search conditions being the prime example – it was not uncommon in SQL 2000 and earlier version to give users SELECT rights, as long as this was compliant with corporate security policy. But since SQL 2005 this is not necessary; you can use procedure signing and impersonation to give users permission to execute dynamic SQL.
In SQL Server you can write stored procedures, triggers and user-defined functions in a CLR language such as C# or Visual Basic. You can perform data access from a CLR module by running a batch of SQL statements, but ownership chaining does not apply in this case. The reason for this is the same as with dynamic SQL: the SQL batch is a scope of its own that does not have any owner.
So when you write CLR modules that accesses tables, you must either grant the users direct permissions to these tables or employ module signing or impersonation. I am not covering how to use these mechanisms with CLR modules in this article, but the topic Module Signing in Books Online includes an example.
If a stored procedure sp1 in database A accesses a table tbl2 in database B, ownership chaining can apply as well, if the procedure owner also owns tbl2. In the trivial case, the two databases have the same owners and all involved objects are owned by dbo. The user running sp1 must also be a user in database B. (Unless you have enabled access for the guest user in database B, something I don't recommend.)
However, starting with SQL 2000 SP3, ownership chaining across databases is turned off by default. You can enable it on server level, or per database. To enable it on server level, set the configuration option cross db ownership chaining to 1 (with sp_configure or through SQL Server Management Studio). Now all databases on the server will be open for cross-db chaining.
To open an individual database for cross-db chaining, use the command ALTER DATABASE db SET DB_CHAINING ON. In the example above, both A and B must be enabled for DB chaining for users being able to access B..tbl2 through sp1 without any own permission on tbl2. To enable a database for chaining, you need sysadmin privileges.
As you might guess, there is a reason for database chaining being off by default. Assume that Jack and Jill own one database each. Jack is a user in Jill's database, but he only has permissions to run a few stored procedures there. If their databases are enabled for database chaining, Jack can get to Jill's inner secrets, by taking the following steps.
Jack can now access all tables in Jill's database as he likes.
Microsoft are very discouraging about turning on database chaining, but for a server that hosts a single application that uses several databases, turning on database chaining on server level appears uncontroversial. It's a different thing on a consolidated server that hosts databases for many unrelated applications. Here, you should most probably never turn on the configuration option to open DB chaining for all databases. What if a user who owns two databases asks you to turn on chaining on these databases? As long it's only those two, it's fine, but then the next guy comes with his two databases. There is no way to say that db1 may chain to db2 but not to db3 or db4.
According to Books Online, you cannot enable master, model and tempdb for database chaining with ALTER DATABASE. It does not really say whether chaining is enabled for these databases if you turn on cross db ownership chaining, but some quick tests that I did indicate that even if this option is on, it does not apply to master, model, msdb and tempdb.
Personally, I recommend that you try to keep cross-database access to stored procedure calls. That is, rather than directly access a table in the other database, call a procedure in that database. In this case, ownership chaining across database is not really needed – instead give the users EXECUTE permission to the procedures in the other database.
Later in this article we will look at how to implement cross-database through certificate signing and through impersonation.
We will now turn to the first of the two methods added in SQL 2005 to grant permissions through stored procedures, signing a procedure with a certificate.
We will first look at using certificates for giving permissions on database level. As an example, I will use dynamic SQL, which probably is the most common situation where you will want to use certificates as a supplement to ownership chaining.
Our example setup is this one:
CREATE TABLE testtbl (a int NOT NULL, b int NOT NULL) go CREATE PROCEDURE example_sp AS EXEC ('SELECT a, b FROM testtbl') go GRANT EXECUTE ON example_sp TO public go
As noted above, ownership chaining does not work in this case, because the batch of dynamic SQL does not have any real owner, and thus the chain is broken. To make it possible for a user to run this procedure without SELECT permission on testtbl, you need to take these four steps:
When the procedure is invoked, the rights of the certificate user
added to the rights of the actual user.
Technically, we can describe this as the certificate user is added to the
current user token. If the procedure invokes another SQL module –
stored procedure, trigger, function etc – the certificate user is removed
from the user token (unless that module is also signed by the
certificate). There are two exceptions to this rule: system procedures and
dynamic SQL invoked through
This example shows the four steps in code.
CREATE CERTIFICATE examplecert ENCRYPTION BY PASSWORD = 'All you need is love' WITH SUBJECT = 'Certificate for example_sp', START_DATE = '20020101', EXPIRY_DATE = '21000101' go CREATE USER examplecertuser FROM CERTIFICATE examplecert go GRANT SELECT ON testtbl TO examplecertuser go -- And each time you change the procedure: ADD SIGNATURE TO example_sp BY CERTIFICATE examplecert WITH PASSWORD = 'All you need is love'
In the following sections, we will look closer at each of these statements.
CREATE CERTIFICATE examplecert ENCRYPTION BY PASSWORD = 'All you need is love' WITH SUBJECT = 'Certificate for example_sp', START_DATE = '20020101', EXPIRY_DATE = '21000101'
The statement CREATE CERTIFICATE has several options, but for our purposes the form above suffices. Here we create a new self-signed certificate which is protected by a password. The password is not awfully strong; I will return to the topic of passwords in the section Managing Certificates and Passwords.
The WITH SUBJECT clause is part of the metadata for the certificate; in the catalog view sys.certificates the subject appears in the column issuer_name.
There is no requirement to enter a start date and an expiry date for the certificate, but for practical reasons you may want to enter both. If you enter neither, the certificate is valid one year from now. Since it is likely that your procedure will be in use for more than one year, it's recommendable to give an expiry date far into the future. If you leave out the start date, SQL 2005 may produce this message:
Warning: The certificate you created is not yet valid; its start date is in the future.
The message is bogus since the default for the start date is the same second as you issue the command. The message is not an error, but informational only. If you don't want to see it, specify a start date. This issue has been fixed in SQL 2008.
CREATE USER examplecertuser FROM CERTIFICATE examplecert
We see here one more option for CREATE USER: we create a user from a certificate. Such a user exists in the database only and is not associated with any login. You can only create one user for each certificate.
GRANT SELECT ON testtbl TO examplecertuser
Here's the beauty of it: we grant examplecertuser exactly the rights it needs for our stored procedure to work. Of course, if you use a lot of dynamic SQL, you may prefer to grant the certificate user SELECT on the dbo schema or add it to db_datareader. You might even consider to add it to db_owner to relieve you from any further hassle, as you add more dynamic SQL to other stored procedures.
But stop there! Recall that discussion on philosophy in the beginning of the article and that one line of defence is to not grant more rights than necessary. This very much applies when you work with dynamic SQL. You know about SQL injection, don't you? If not, a quick recap: if you build SQL strings from input data, a malicious user might be able to inject SQL commands you did not intend your code to execute by including a single quote (') in the input data. For a longer recap, see the section on SQL injection in my article on dynamic SQL.
You may already be aware of the risk of SQL injection, and you have taken the steps necessary to protect your procedure against this attack. But that is today. Code changes throughout the life-time of an application, and one day there is a need for an enhancement of the procedure, and the task is given to an inexperienced programmer who, unaware of the dangers of SQL injection, breaks that line of defence. By giving the certificate user exactly the rights needed for the stored procedure, you have set up a second line of defence that reduces the potential damage significantly.
This is the syntax to sign a procedure:
ADD SIGNATURE TO example_sp BY CERTIFICATE examplecert WITH PASSWORD = 'All you need is love'
To use the certificate, you need to specify its password. You can sign a procedure with more than one certificate to add permissions from several certificate users.
If you change the procedure, the signature is lost, and you need to resign the procedure. Given for what we want to use certificates for, this may seem impractical. When we grant someone execution rights on a stored procedure, these permissions are retained when we alter the procedure. So why do we need to resign a procedure when we change it? Isn't that a shortcoming? It may seem so, but it is worth to understand the general purpose of signing things with certificates, which extends far beyond stored procedures in SQL Server. Say that you have a important message you want to pass to someone else, for instance over e-mail. You want to make it possible for the receiver to verify that he got exactly the message you sent him. Therefore you sign your message, which requires both a public and a private key. You make your public key available, and the receiver can then apply that key to message and the signature to verify that they agree. If someone has altered the text or the signature, the validation will fail.
That is, every time you change the stored procedure, the signature will change, and this is why you must resign the procedure. It could seem that for the particular purpose that we are using certificates for here, that this is just hassle. But as I discuss in the section Managing Certificates and Passwords, the fact that the procedure must be resigned can in fact be a considerable security advantage.
Here is a full-fledged example that you can play with. To show the difference, there are two procedures, of which only one is signed. (Please refer to the introductory note on the examples in this article.)
USE master go -- Create a test login and test database CREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST' CREATE DATABASE certtest go -- Move to the test database. USE certtest go -- Create the test user. CREATE USER testuser go -- Create the test table and add some data. CREATE TABLE testtbl (a int NOT NULL, b int NOT NULL) INSERT testtbl (a, b) VALUES (47, 11) go -- Create two test stored procedures, and grant permission. CREATE PROCEDURE unsigned_sp AS SELECT SYSTEM_USER, USER, name, type, usage FROM sys.user_token EXEC ('SELECT a, b FROM testtbl') go CREATE PROCEDURE example_sp AS SELECT SYSTEM_USER, USER, name, type, usage FROM sys.user_token EXEC ('SELECT a, b FROM testtbl') -- EXEC unsigned_sp go GRANT EXECUTE ON example_sp TO public GRANT EXECUTE ON unsigned_sp TO public go -- Create the certificate. CREATE CERTIFICATE examplecert ENCRYPTION BY PASSWORD = 'All you need is love' WITH SUBJECT = 'Certificate for example_sp', START_DATE = '20020101', EXPIRY_DATE = '20200101' go -- Create the certificate user and give it rights to access the test table. CREATE USER examplecertuser FROM CERTIFICATE examplecert GRANT SELECT ON testtbl TO examplecertuser go -- Sign the procedure. ADD SIGNATURE TO example_sp BY CERTIFICATE examplecert WITH PASSWORD = 'All you need is love' go -- Run as the test user, to actually see that this works. EXECUTE AS USER = 'testuser' go -- First run the unsigned procedure. This gives a permission error. EXEC unsigned_sp go -- Then run the signed procedure. Now we get the data back. EXEC example_sp go -- Become ourselves again. REVERT go -- Clean up USE master DROP DATABASE certtest DROP LOGIN testuser
As you can see, I added this statement to the two test procedures in the example:
SELECT SYSTEM_USER, USER, name, type, usage FROM sys.user_token
When we run unsigned_sp, this returns
SYSTEM_USER USER name type usage ------------ ---------- --------- --------- -------------- testuser testuser testuser SQL USER GRANT OR DENY testuser testuser public ROLE GRANT OR DENY
What this tells us is that we are logged in as testuser, and this is also the name of the user in the database. There are two rows in sys.user_token, one for the user, and one for the single role that testuser is a member of.
But when we run example_sp, which is signed, there is an extra line:
SYSTEM_USER USER name type usage ---------- -------- ----------- --------------------------- --------------- testuser testuser testuser SQL USER GRANT OR DENY testuser testuser public ROLE GRANT OR DENY testuser testuser examplecertuser USER MAPPED TO CERTIFICATE GRANT OR DENY
We see here that the user for the certificate has been added to the user token, so its permissions can apply as well. We can also see that we still are testuser, and no one else. This may seem like a pointless thing to mention, but as we shall see later, this is not the case when you use EXECUTE AS.
As you see, example_sp includes a call to unsigned_sp that has been commented out. If you remove that comment, and run the script again, when you call unsigned_sp from example_sp, you get a permission error just like when unsigned_sp is called directly. You will also see in the output from sys.user_token, that examplecertuser is not there.
There is one situation where certificate signing does not work. If the user has explicitly been denied access to one or more of the tables in the query with the DENY command, this takes precedence over the permissions granted to the certificate user. This is different from ownership chaining, where DENY never interferes with the permissions given through the stored procedure. (This is because ownership chaining suppresses the permission check altogether.) As will see later, this obstacle does not exist when you use impersonation with EXECUTE AS.
Another common situation where ownership chaining does not suffice is when you need to give users permissions to empty a table and reload it with BULK INSERT from a file. Here is a very simple procedure for this task:
CREATE PROCEDURE reload_sp AS TRUNCATE TABLE reloadable BULK INSERT reloadable FROM 'E:\temp\reloadable.csv' WITH (FIELDTERMINATOR=',', ROWTERMINATOR='\n')
Ownership chaining fails here for two reasons: 1) it does not apply to TRUNCATE TABLE. 2) to perform bulk operations, you need the server-level permission ADMINISTER BULK OPERATIONS or membership in the fixed server role bulkadmin.
You can address this by signing reload_sp, but this is more complicated than in the previous example, because you can only add server permissions when you are in the master database. Therefore, to set up reload_sp so it can be executed by an unprivileged user, there are no less than ten steps to go through:
In SQL Server 2012 the steps 4, 6 and 7 can be carried out in a different way. Since SQL 2012 at this writing still is in beta, I put the focus on the steps that works in all versions from SQL 2005 and on, and cover the new features in SQL 2012 later.
First some example code for the bit in master.
USE master go CREATE CERTIFICATE reloadcert ENCRYPTION BY PASSWORD = 'All you need is love' WITH SUBJECT = 'For bulk-load privileges', START_DATE = '20020101', EXPIRY_DATE = '20200101' go CREATE LOGIN reloadcert_login FROM CERTIFICATE reloadcert go GRANT ADMINISTER BULK OPERATIONS TO reloadcert_login go BACKUP CERTIFICATE reloadcert TO FILE = 'C:\temp\reloadcert.cer' WITH PRIVATE KEY (FILE = 'C:\temp\reloadcert.pvk' , ENCRYPTION BY PASSWORD = 'Tomorrow never knows', DECRYPTION BY PASSWORD = 'All you need is love') go
The creation of the certificate is the same as in the example with dynamic SQL. Since we need to grant a server permission, a mere certificate user won't do, but we must associate the certificate with a login. (Or more in line with the lingo introduced in SQL 2005, a server principal. "Login" is a misnomer here, as the login created for a certificate cannot actually log in.) Next we grant the certificate login the rights to run bulk load.
Finally we export the certificate to disk with the command BACKUP CERTIFICATE. The certificate consists of two parts: a public key which goes into the first file, and a private key. The private key requires a password on its own, Tomorrow never knows, in this example. The path where to write the files is a small complication that I will come back to. In this example I use C:\temp to keep the script simple. However, you may find that C:\temp does not work for you, because it does not exist at all, or the service account for SQL Server does not have permission to this directory.
Here are the parts you would run in the application database:
CREATE CERTIFICATE reloadcert FROM FILE = 'C:\temp\reloadcert.cer' WITH PRIVATE KEY (FILE = 'C:\temp\reloadcert.pvk', DECRYPTION BY PASSWORD = 'Tomorrow never knows', ENCRYPTION BY PASSWORD = 'A day in life') go EXEC xp_cmdshell 'DEL C:\temp\reloadcert.*' go CREATE USER reloadcert_user FOR CERTIFICATE reloadcert go GRANT ALTER, INSERT ON reloadable TO reloadcert_user go -- Sign the test procedure each time you have changed it. ADD SIGNATURE TO reload_sp BY CERTIFICATE reloadcert WITH PASSWORD = 'A day in life' go
Here we use CREATE CERTIFICATE in a different way than before. Instead of creating a new certificate, we import the certificate that we exported from master. We need to specify the password for the private key to be able to access the file. We must also define a password for the certificate in this database. In this example, I'm using different passwords for the certificate in master and in the application database just to show you that this is possible. It's probably more practical to use the same password in both databases, though.
We delete the files with the certificate from disk. This is a security precaution, since any database owner on the machine could load the certificate into his database. But it is also a matter of convenience; if you re-run the script and the certificate files are already on disk, BACKUP CERTIFICATE will fail.
Note: xp_cmdshell is disabled by default. An alternative is to delete the file directly from Windows manually.
Next, we create the certificate user. This user is not related to the login for the certificate, and I've stressed this by giving them different names. Again, in practice, you may prefer to use the same name for both. We grant the certificate user the database permissions that are needed: ALTER permission for TRUNCATE TABLE, and INSERT permission for BULK INSERT. Finally, we sign the procedure, using the password for the certificate in this database.
We are almost done, but if you do all this and try to run the procedure reload_sp as a non-privileged user, you will nevertheless get an error message that you don't have permissions to do bulk load. Because of a bug in SQL Server, we need to modify the procedure:
CREATE PROCEDURE reload_sp AS TRUNCATE TABLE reloadable EXEC('BULK INSERT reloadable FROM ''C:\temp\reloadtest.csv'' WITH (FIELDTERMINATOR='','', ROWTERMINATOR=''\n'')')
This bug is specific to bulk-load permissions, and I have not found any other server-level permission that has the same issue. (The specifics of the bug are that SQL Server checks the permissions for BULK INSERT before the certificate has been added to the user token. By putting BULK INSERT in an inner scope with dynamic SQL, we can work around the bug.)
As in the previous example there are two procedures, one signed and one unsigned, and I've added SELECT from sys.login_token and sys.user_token, so that you can see how the certificate login and the certificate user are added and deleted. (Again, please refer to the introductory note for general notes on the examples.) If you get errors when you run the script that C:\temp does not exist, or you get permissions errors with C:\temp, see below.
USE master go -- Create a test file for bulk load. EXEC xp_cmdshell 'ECHO 978,123,234 > C:\temp\reloadtest.csv', no_output EXEC xp_cmdshell 'ECHO -98,13,85 >> C:\temp\reloadtest.csv', no_output go -- Create a test login. CREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST' go -- Create test database. CREATE DATABASE bulkcerttest go -- Create certificate in master. CREATE CERTIFICATE reloadcert ENCRYPTION BY PASSWORD = 'All you need is love' WITH SUBJECT = 'For bulk-load privileges', START_DATE = '20020101', EXPIRY_DATE = '20200101' go -- Create a login for the certificate. CREATE LOGIN reloadcert_login FROM CERTIFICATE reloadcert go -- Grant rights for the certificate login. GRANT ADMINISTER BULK OPERATIONS TO reloadcert_login go -- Save the certificate to disk. BACKUP CERTIFICATE reloadcert TO FILE = 'C:\temp\reloadcert.cer' WITH PRIVATE KEY (FILE = 'C:\temp\reloadcert.pvk' , ENCRYPTION BY PASSWORD = 'Tomorrow never knows', DECRYPTION BY PASSWORD = 'All you need is love') go -- Move to test database. USE bulkcerttest go -- Create the non-priv user. CREATE USER testuser go -- A test table. CREATE TABLE reloadable (a int NOT NULL, b int NOT NULL, c int NOT NULL) go -- Insert some test data. If test succeeds, this data should disappear. INSERT reloadable (a, b, c) VALUES (12, 23, 34) go -- Test procedure with BULK INSERT. BULK INSERT needs to be in -- EXEC() because of a bug in SQL Server. CREATE PROCEDURE reload_sp AS SELECT name, type, usage FROM sys.login_token SELECT name, type, usage FROM sys.user_token TRUNCATE TABLE reloadable EXEC('BULK INSERT reloadable FROM ''C:\temp\reloadtest.csv'' WITH (FIELDTERMINATOR='','', ROWTERMINATOR=''\n'')') go -- The same code, but this procedure we will not sign. CREATE PROCEDURE unsigned_sp AS SELECT name, type, usage FROM sys.login_token SELECT name, type, usage FROM sys.user_token --TRUNCATE TABLE reloadable EXEC('BULK INSERT reloadable FROM ''C:\temp\reloadtest.csv'' WITH (FIELDTERMINATOR='','', ROWTERMINATOR=''\n'')') go -- Give test user right to execute the procedures. GRANT EXECUTE ON reload_sp TO testuser GRANT EXECUTE ON unsigned_sp TO testuser go -- Import the certificate we created in master into the test database. CREATE CERTIFICATE reloadcert FROM FILE = 'C:\temp\reloadcert.cer' WITH PRIVATE KEY (FILE = 'C:\temp\reloadcert.pvk', DECRYPTION BY PASSWORD = 'Tomorrow never knows', ENCRYPTION BY PASSWORD = 'A day in life') go -- Delete the files. EXEC master..xp_cmdshell 'DEL C:\temp\reloadcert.*', 'no_output' go -- And create a user for the certificate. CREATE USER reloadcert_user FOR CERTIFICATE reloadcert go -- Grant this user rights to truncate and insert to the test table. GRANT ALTER, INSERT ON reloadable TO reloadcert_user go -- Sign the test procedures. ADD SIGNATURE TO reload_sp BY CERTIFICATE reloadcert WITH PASSWORD = 'A day in life' go -- Switch to the test user. EXECUTE AS LOGIN = 'testuser' go -- Run the unsigned procedure. You will get a permission error. EXEC unsigned_sp go -- Run the real reload procedure. EXEC reload_sp go -- Back to ourselves. REVERT go -- The data in the table has been replaced. SELECT a, b, c FROM reloadable go -- Clean up. USE master go DROP DATABASE bulkcerttest DROP LOGIN reloadcert_login DROP CERTIFICATE reloadcert DROP LOGIN testuser EXEC xp_cmdshell 'DEL C:\temp\reloadtest.csv', 'no_output'
In unsigned_sp I have commented TRUNCATE TABLE, in order to demonstrate the error you get because lack of bulk permissions. If you uncomment TRUNCATE TABLE, you will get a different permission error from unsigned_sp.
One problem here is that we need to bounce the certificate over disk. To do this, you need to determine a directory where you can write the certificate. This can be particularly difficult if you need to do this in a deployment script to be run on servers you never have seen. In this example, I used C:\temp for the sake of simplicity, but C:\temp does not exist on all servers. Even if it does, the service account for SQL Server may not have write access to that folder. If you leave out the path entirely, BACKUP CERTIFICATE will write the files to the default directory for new databases and likewise CREATE CERTIFICATE will read from this directory. It's reasonable to expect that SQL Server has write access to this folder, so far so good. Unfortunately, this path is not easily determined from within SQL Server, so there is a challenge if you want to delete the files programmatically from your deployment script. One way to get a path that is known to be writeable is this SELECT:
SELECT substring(physical_name, 1, len(physical_name) -
charindex('\', reverse(physical_name)) + 1)
WHERE file_id = 1
This retrieves the path to the directory where the first file for the current database resides. You would use this path throughout the script, which means that BACKUP/CREATE CERTIFICATE has to be embedded in dynamic SQL, as they don't accept variables for the file name.
It would certainly be convenient, if you could copy a certificate directly from one database to another without bouncing it over disk, and there is a new feature in SQL Server 2012 that permits you to do this. There is a new clause to CREATE CERTIFICATE: FROM BINARY which permits you to specify the certificate as a binary constant. SQL 2012 also offers two new functions certencoded and certprivatekey which permits you to retrieve the public and the private keys of the certificate. Thus, you can say:
DECLARE @public_key varbinary(MAX) = certencoded(my_cert), @private_key varbinary(MAX) = certprivatekey(my_cert, 'private_key_password', 'public_key_password') CREATE CERTIFICATE newcert FROM BINARY = @public_key WITH PRIVATE KEY (BINARY = @private_key, DECRYPTION BY PASSWORD = 'private_key_password', ENCRYPTION BY PASSWORD = 'new_public_key_password')
Almost. The functions do not accept the name for the certificate, but they want the the certificate id in sys.certificates. You can retrieve it with the cert_id function, see example below for how to use it. Furthermore, CREATE CERTIFICATE does not accept variables for the binary value, but you must provide a constant, which means that you are in for some dynamic SQL.
To use this new functionality to copy a certificate between databases, we replace the steps 4-7 above to read:
The reason we like to use a temp table is that our example script is split into a number of batches. We cannot use variables, as they exist only for the duration of a batch. Whence the temp table. This is how step 4 looks like:
CREATE TABLE #keys (pubkey varbinary (MAX) NOT NULL, privkey varbinary(MAX) NOT NULL) INSERT #keys (pubkey, privkey) SELECT certencoded(cert_id('reloadcert')), certprivatekey(cert_id('reloadcert'), 'Tomorrow never knows', 'All you need is love')
The passwords you pass to certprivatekey correspond to the passwords we used with BACKUP CERTIFICATE above. That is, the first password is the password for the private key, which you have to make up at this point. The second password is the password for the public key that you used when you created the certificate in master.
Since we need to use dynamic SQL to create the certificate from the data in the temp table, this part gets a little more complicated than it would have to be. Here is how it looks with our bulk-copy example:
DECLARE @sql nvarchar(MAX) SELECT @sql = 'CREATE CERTIFICATE reloadcert FROM BINARY = ' + convert(nvarchar(MAX), pubkey, 1) + ' WITH PRIVATE KEY (BINARY = ' + convert(nvarchar(MAX), privkey, 1) + ', DECRYPTION BY PASSWORD = ''Tomorrow never knows'', ENCRYPTION BY PASSWORD = ''A day in life'')' FROM #keys
PRINT @sql EXEC (@sql) DROP TABLE #keys
A key here is the third argument to the convert function; this converts the binary value to a hex-string with a leading 0x. This style to convert was added in SQL 2008, in case you are not familiar with it. If you compare with the CREATE CERTIFICATE command when we imported the certificate from a file, this is very similar; all that has changed is that FILE is now BINARY and the extra syntactical fireworks imposed to us because we have to use dynamic SQL.
Before we execute the command, we print it, so we can understand what is going on if there is a syntax error in our dynamic SQL. Finally, we drop the temp table as a safety precaution. If we would leave it around, we could run into problems later if we would re-run the script or run a similar script from the same query window.
The script bulkcopy-2012.sql has the full example for bulk-load for SQL 2012, using CREATE CERTIFICATE FROM BINARY.
All and all, this is a welcome addition to SQL 2012, since it makes it easier to copy certificates between databases. Not so much for the different syntax, but you don't have to worry about disk paths in your scripts.
When you need to write a stored procedure that accesses data in another database, you can arrange permissions by signing your procedure with a certificate that exists in both databases. The steps are similar to the bulk-copy case, so I will go directly to an example script.
There are two things to note with this script: 1) testuser is never granted access to db1. That is, by signing your procedures with a certificate, you can give users access to data in a database they do not have access to themselves. This is different from ownership chaining, where the user must have been granted access to the target database. 2) I don't create any user for the certificate in db2, simply because in this example no permissions are needed to be granted through the certificate in db2.
Here is the script (please see the introductory note for general notes on the example scripts):
USE master go -- Create a test login. CREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST' go -- Create test two databases CREATE DATABASE db1 CREATE DATABASE db2 go -- Move to first test database. USE db1 go -- Create certificate in db1 CREATE CERTIFICATE crossdbcert ENCRYPTION BY PASSWORD = 'Lucy in the Skies with Diamonds' WITH SUBJECT = 'Cross-db test', START_DATE = '20020101', EXPIRY_DATE = '20200101' go -- Save the certificate to disk. BACKUP CERTIFICATE crossdbcert TO FILE = 'C:\temp\crossdbcert.cer' WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk' , ENCRYPTION BY PASSWORD = 'She said She said', DECRYPTION BY PASSWORD = 'Lucy in the Skies with Diamonds') go -- Create the certificate user. Note that we do not grant access to -- testuser. CREATE USER certuser FROM CERTIFICATE crossdbcert go -- A test table. CREATE TABLE testtbl (a int NOT NULL, b int NOT NULL, c int NOT NULL) go -- Insert some test data. INSERT testtbl (a, b, c) VALUES (12, 23, 34) go -- The certificate user needs to access this table. GRANT SELECT ON testtbl TO certuser go -- Switch to the second database. USE db2 go -- Welcome the test user to this database. CREATE USER testuser go -- Signed test procedure. CREATE PROCEDURE signed_sp AS SELECT a, b, c FROM db1..testtbl go -- Same code, but we will leave this one unsigned. CREATE PROCEDURE unsigned_sp AS SELECT a, b, c FROM db1..testtbl go -- Give test user right to execute the procedures. GRANT EXECUTE ON signed_sp TO testuser GRANT EXECUTE ON unsigned_sp TO testuser go -- Import the certificate we created in the first test database into the second. CREATE CERTIFICATE crossdbcert FROM FILE = 'C:\temp\crossdbcert.cer' WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk', DECRYPTION BY PASSWORD = 'She said She said', ENCRYPTION BY PASSWORD = 'Helter Skelter') go -- Delete the file with the certificate. EXEC master..xp_cmdshell 'DEL C:\temp\crossdbcert.*', 'no_output' go -- Sign the test procedures. ADD SIGNATURE TO signed_sp BY CERTIFICATE crossdbcert WITH PASSWORD = 'Helter Skelter' go -- Switch to the test login. EXECUTE AS LOGIN = 'testuser' go -- Run the unsigned procedure. You will get a permission error. EXEC unsigned_sp go -- Run the signed procedure. testuser can now access testdbl, even though -- he is not a user of db1. EXEC signed_sp go -- Back to ourselves. REVERT go -- Clean up. USE master go DROP DATABASE db1 DROP DATABASE db2 DROP LOGIN testuser
See the file crossdb-2012.sql for a version that uses the new FROM BINARY clause on SQL Server 2012 to copy the certificate.
If you look up the command ADD SIGNATURE in Books Online, you will find that there is an optional keyword COUNTER which you can put before SIGNATURE, but in Books Online for SQL 2005 and SQL 2008 there is no information what this keyword means. It was added to Books Online first with SQL 2008 R2. Before that, the only place to learn about counter signatures was a a blog post from Laurenţiu Cristofor. He was one of the Program Managers for the security enhancements in SQL 2005.
When you counter-sign a procedure P1 with a certificate C, this has in itself no effect at all, even if permissions has been granted to a user for that certificate. But assume that there is also a procedure P2 that has been signed (and not counter-signed) with C, and that P2 calls P1. Normally, when you call an inner procedure from a signed procedure, the certificate user is removed from the user token. But when P1 is counter-signed with C, the certificate user remains in the user token. The net effect of this is that you can get the powers of P1 only if you call it through P2.
How could we use this? Here is one example. Assume that we have generic search procedure that in itself permits users to search all data. However, there are business rules that say that users may only see customers (or products or whatever) they have access to according to some scheme. These rules are enforced by an outer procedure that computes the values for some of the parameters to the inner procedure, thereby constraining the search. In the example below, this is extremely simple: the user may only see rows he owns. In a real-world scenario, both procedures would be far more elaborate. (Please see the introductory note for general notes on the example scripts).
USE master go -- Create a test login and test database. CREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST' CREATE DATABASE certtest go -- Move to the test database. USE certtest go -- Create the test user, and grant him permission to execute any -- stored procedure. CREATE USER testuser GRANT EXECUTE TO testuser go -- Create a test table and add some data. CREATE TABLE testtbl (a int NOT NULL, b int NOT NULL, owner sysname NOT NULL) INSERT testtbl (a, b, owner) VALUES (47, 11, 'testuser') INSERT testtbl (a, b, owner) VALUES (17, 89, 'someotheruser') go -- This is the inner procedure that permits you to view all data, -- but the selection could be constrained to a certain owner. CREATE PROCEDURE inner_sp @owner sysname = NULL AS SELECT SYSTEM_USER, USER, name, type, usage FROM sys.user_token DECLARE @sql nvarchar(MAX) SELECT @sql = N'SELECT a, b FROM testtbl WHERE 1 = 1 ' IF @owner IS NOT NULL SELECT @sql = @sql + ' AND owner = @owner' EXEC sp_executesql @sql, N'@owner sysname', @owner go -- The outer procedure which forces the owner to be the current user. CREATE PROCEDURE outer_sp AS SELECT SYSTEM_USER, USER, name, type, usage FROM sys.user_token DECLARE @owner sysname SELECT @owner = SYSTEM_USER EXEC inner_sp @owner go -- Create the certificate. CREATE CERTIFICATE examplecert ENCRYPTION BY PASSWORD = 'Being for the benefit of Mr Kite' WITH SUBJECT = 'Certificate for counter-sign example', START_DATE = '20020101', EXPIRY_DATE = '20200101' go -- Create the certificate user and grant access the test table. CREATE USER examplecertuser FROM CERTIFICATE examplecert GRANT SELECT ON testtbl TO examplecertuser go -- Sign the outer procedure. ADD SIGNATURE TO outer_sp BY CERTIFICATE examplecert WITH PASSWORD = 'Being for the benefit of Mr Kite' go -- And counter-sign the inner procedure. ADD COUNTER SIGNATURE TO inner_sp BY CERTIFICATE examplecert WITH PASSWORD = 'Being for the benefit of Mr Kite' go -- Run as the test user, to actually see that this works. EXECUTE AS USER = 'testuser' go -- First run the inner procedure directly. This gives a permission -- error. EXEC inner_sp go -- Then run the outer procedure. Now we get the data back, but -- only what we are permitted to see. EXEC outer_sp go -- Become ourselves again. REVERT go -- Clean up. USE master DROP DATABASE certtest DROP LOGIN testuser
True, this could also be implemented by signing inner_sp with the certificate directly, and then make sure that users does not have EXECUTE permission on this procedure, for instance with an explicit DENY. Thanks to ownership signing, users would still be able to call the inner procedure if they come from the outer procedure. But this would require you to manage two security mechanisms to achieve your goal, whereas with counter-signing you only need one.
Here is a second example, inspired by a newsgroup question. A poster wanted users of an application to be able to start a certain job with sp_start_job. To be able to start a job owned by someone else, you need to be member of the fixed role SQLAgentOperatorRole in msdb. A start is to write a stored procedure that calls sp_start_job for this specific job, sign that procedure with a certificate, and then create a user from the certificate and make that user a member of SQLAgentOperatorRole.
We have learnt previously that when you call a system procedure, the certificate user remains in the user token, and thus you can take benefit of the permissions granted to the certificate user. But it turns out that the procedures in msdb are not system procedures in that sense. So we need to sign sp_start_job as well, but a normal signature is not a very good idea since this would permit users to start any job. Instead we counter-sign sp_start_job with the same certificate that we sign the wrapper procedure with, and we are almost there. I found by testing that sp_start_job calls two other procedures, sp_sqlagent_notify and sp_verify_job_identifiers, and they must be counter-signed as well.
I should hasten to add, that this solution is not unquestionable. Does Microsoft support signing of msdb procedures? If you install a service pack or a hotfix, you will need to reapply the signatures if Microsoft replaces the procedures with updated versions. They may also restructure the code, requiring you to counter-sign a different set of procedures.
Nevertheless, here is a complete script that demonstrates this technique. Note that to run it successfully, you need to have SQL Server Agent running, and you need to create a job called Testjob (which can do
PRINT 'Hello world!' or whatever.) As always, please see the introductory
note for general notes on the example scripts. For the SQL 2012 version, please see the file jobstart-2012.sql.
USE master go -- Create a test login. CREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST' go -- Create test database. CREATE DATABASE jobstarttest go USE msdb -- Create certificate in msdb. CREATE CERTIFICATE jobstartcert ENCRYPTION BY PASSWORD = 'Strawberry Fields Forever' WITH SUBJECT = 'To permit starting the Testjob', START_DATE = '20020101', EXPIRY_DATE = '20200101' go -- Create a user for the certificate. CREATE USER jobstartcert_user FROM CERTIFICATE jobstartcert go -- Grant rights for the certificate login to run jobs. EXEC sp_addrolemember SQLAgentOperatorRole, jobstartcert_user go -- Counter-sign sp_start_job and its subprocedures. ADD COUNTER SIGNATURE TO sp_start_job BY CERTIFICATE jobstartcert WITH PASSWORD = 'Strawberry Fields Forever' ADD COUNTER SIGNATURE TO sp_verify_job_identifiers BY CERTIFICATE jobstartcert WITH PASSWORD = 'Strawberry Fields Forever' ADD COUNTER SIGNATURE TO sp_sqlagent_notify BY CERTIFICATE jobstartcert WITH PASSWORD = 'Strawberry Fields Forever' go -- Save the certificate to disk. BACKUP CERTIFICATE jobstartcert TO FILE = 'C:\temp\jobstartcert.cer' WITH PRIVATE KEY (FILE = 'C:\temp\jobstartcert.pvk' , ENCRYPTION BY PASSWORD = 'Looking through a Glass Onion', DECRYPTION BY PASSWORD = 'Strawberry Fields Forever') go -- Move to test database. USE jobstarttest go -- Create a database user for the test login. CREATE USER testuser go -- Create a procedure that starts a certain job. CREATE PROCEDURE start_this_job AS EXEC msdb..sp_start_job 'Testjob' go -- Give test user right to execute the procedure. GRANT EXECUTE ON start_this_job TO testuser go -- Import the certificate we created in msdb into the test database. CREATE CERTIFICATE jobstartcert FROM FILE = 'C:\temp\jobstartcert.cer' WITH PRIVATE KEY (FILE = 'C:\temp\jobstartcert.pvk', DECRYPTION BY PASSWORD = 'Looking through a Glass Onion', ENCRYPTION BY PASSWORD = 'Fixing a Hole') go -- Delete the files. EXEC master..xp_cmdshell 'DEL C:\temp\jobstartcert.*', 'no_output' go -- Sign the test procedures. ADD SIGNATURE TO start_this_job BY CERTIFICATE jobstartcert WITH PASSWORD = 'Fixing a Hole' go -- Switch to the test user. EXECUTE AS LOGIN = 'testuser' go -- Start the job, this succeeds. EXEC start_this_job go -- Back to ourselves. REVERT go -- Clean up. USE msdb go DROP COUNTER SIGNATURE FROM sp_sqlagent_notify BY CERTIFICATE jobstartcert DROP COUNTER SIGNATURE FROM sp_verify_job_identifiers BY CERTIFICATE jobstartcert DROP COUNTER SIGNATURE from sp_start_job BY CERTIFICATE jobstartcert DROP USER jobstartcert_user DROP CERTIFICATE jobstartcert go USE master go DROP DATABASE jobstarttest DROP LOGIN testuser
Instead of signing your procedure with certificate, you can use asymmetric keys. You create an asymmetric key in SQL Server with the command CREATE ASYMMETRIC KEY. The syntax is similar, but not identical, to CREATE CERTIFICATE. Please see Books Online for details.
From a cryptographic point of view, a certificate is an asymmetric key that has an issuer and an expiration date. Since it has an issuer, a certificate can participate in a chain of trust, which is important in for instance Service Broker dialogues. When it comes to signing stored procedures, I have (with quite some help from Razvan Socol) identified the following practical differences:
All and all, I can't find any of these points convincing enough to mandate any over the other. I have preferred to talk only about certificates in the main part of this text to simplify the presentation.
To see which procedures that have been signed in a database, you can run this query. crypt_type_desc will tell you whether the procedure is signed with a certificate or an asymmetric key, and whether it's regularly signed or counter-signed.
SELECT Module = object_name(cp.major_id), [Cert/Key] = coalesce(c.name, a.name), cp.crypt_type_desc FROM sys.crypt_properties cp LEFT JOIN sys.certificates c ON c.thumbprint = cp.thumbprint LEFT JOIN sys.asymmetric_keys a ON a.thumbprint = cp.thumbprint
To find the users mapped to certificates, you can use this query:
SELECT certname = c.name, "username" = dp.name FROM sys.certificates c JOIN sys.database_principals dp ON c.sid = dp.sid
In the same vein, to find logins mapped to certificates:
SELECT certname = c.name, loginname = sp.name FROM master.sys.certificates c JOIN sys.server_principals sp ON c.sid = sp.sid
(Queries for users/logins mapped to asymmetric keys are similar.)
If you want to find all databases where a certificate has been used, you will need to query them all, using the thumbprint and/or the subject as the key.
Normally passwords should be strong and kept secret, but I have already hinted that for procedure signing this may not always be necessary.
Let's first consider the case when you use a certificate to grant permissions on database level. What if an unauthorised user learns the password for a certificate that is used to sign one or more procedures? To be able to use the password for some evil, he would first need to have the rights to create procedures in some schema. Furthermore, to use ADD SIGNATURE he needs CONTROL permission on the certificate. In practice you would only have that permission if you are member of the db_owner role, in which case you can create your own certificates and sign procedures with them all day long. The potential threat I can see is that another database owner could borrow your keyboard while you are away, and export a certificate that gives access to some sensitive table. He could import the certificate into his database and sign a procedure that reads this data. Of course, he could just as well create a new certificate when he uses your keyboard, but if he uses an existing certificate the data theft is more likely to go unnoticed.
All and all, for certificates used for procedure signing on database level, the password is not your biggest secret. Nevertheless, below I present an approach that permits throw away the password altogether so that no one knows it, not even you.
Let's now look at using certificates to grant server-level permissions. (I am not discussing cross-database access specifically, but what I say here can be applied to cross-database access as well.) There is the plain and simple case where everyone who has db_owner rights in the user databases also are members of sysadmin or have CONTROL SERVER. This scenario is no different from database permissions: there isn't really anyone to hide the password for. All examples presented this far have been written under this assumption, since I wanted to focus on the mechanism as such. But if there are users who have db_owner rights in a database without being sysadmin, it's a different story. Here you need to apply care.
Say that you are the DBA on a consolidated server and you are approached by an application admin, let's call her Anna DeMin who has db_owner rights in the database for her application. Anna has written the procedure reload_sp and wants you to sign it. To this end, you first review her code to ensure that she reads from the directory allotted for her application. You can then follow the steps outlined in the example script we saw previously.
In this situation, you need to make sure that Anna's does not learn the password for the certificate that you create in her database, since else she could change the procedure to read from somewhere else. You also need to hide the password for the private key, or delete it from disk directly so she cannot import it. Here is much of the beauty with certificates: as a server DBA, you can have full control over what permissions you have granted to user databases and to what code. To do that, you need to be able to manage your passwords.
Now, if you are a server DBA, I can hear you say that you don't have the time for all this, and you trust your application admins, so you will give Anna the cert and the password, and that's that. Of course, if you trust your colleagues that's great, but no matter whether you do or not, I have a script for you that permits you to automate most of this process. The one step I cannot automate for you is the code review.
The key points of the script.
ALTER CERTIFICATE cert REMOVE PRIVATE KEY. Once the private key has been removed, the certificate is only good for validation, but cannot be used to sign any new procedures.
The script does have any support for counter-signatures, but if you need this, you could extend the script for this purpose.
The script consists of two parts. The first part is the setup part, where you need define three things: 1) The target database. 2) The stored procedure (or function or trigger) to sign. 3) The server-level permission(s) to grant. Everything below the line with
====== is the fixed part that you normally don't have to change. (Why is this a script and not a stored procedure? I wrote it as a script, because I figure that if you administer many servers, it is better to have as script on disk than installing a stored procedure on every server. Particularly, if you change the script by time, it's good to have a single copy of it.)
Here are all the steps the script takes. Note that if there is an error, the script aborts on the spot.
If you want to test the script, you can use the bulk-load example above with some modifications: remove the certificate handling in master, and change CREATE CERTIFICATE in the user database to create a local certificate. Keep in mind that you still need to sign the procedure to grant ALTER and INSERT permissions on the table. Once you have run the script below, you can run the EXECUTE AS part in the bulk-load script to verify that the test user have all permissions. You find such a prepared version of the bulk-load example in the file grantrights-test.sql, instructions are included.
-- This script takes it base in the master database. USE master go DECLARE @procname nvarchar(260), @database sysname, @perms nvarchar(4000), @sp_executesql nvarchar(150), @certname sysname, @username sysname, @subject nvarchar(4000), @pwd char(39), @sql nvarchar(MAX), @filename nvarchar(1024), @cmd varchar(1024), @debug bit -- Set up parameters: the procedure to sign and the database it belongs to. SELECT @procname = 'reload_sp', @database = 'bulkcerttest' -- The permissions to grant through the certificate. Set NULL if you only -- want to remove current signature. SELECT @perms = 'ADMINISTER BULK OPERATIONS' -- Run with debug or not? SELECT @debug = 1 --============================ END OF SETUP ========================== -- A big TRY-CATCH block around everything to abort on first error. BEGIN TRY -- First verify that the database exists. IF db_id(@database) IS NULL RAISERROR('Database %s does not exist', 16, 1, @database) -- Make sure that database name is quoted and appears exactly as in sys.databases. SELECT @database = quotename(name) FROM sys.databases WHERE name = @database -- We will call sp_executesql a number of times in the target database. SELECT @sp_executesql = @database + '.sys.sp_executesql' -- Next we verify that the procedure exists and make sure that -- we have a normalised quoted name. We need to run a query in the -- target database. SELECT @sql = 'SELECT @procname = MIN(quotename(s.name) + ''.'' + quotename(o.name)) FROM sys.objects o JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.object_id = object_id(@procname)' IF @debug = 1 PRINT @sql EXEC @sp_executesql @sql, N'@procname nvarchar(260) OUTPUT', @procname OUTPUT IF @procname IS NULL RAISERROR('No procedure with the given name in database %s', 16, 1, @database) -- Construct name, subject and password for the certificate. SELECT @certname = 'SIGN ' + @database + '.' + @procname, @subject = 'Signing ' + @database + '.' + @procname + ' for ' + @perms, @pwd = convert(char(36), newid()) + 'Aa0' -- If a login exists for the cerficiate, we drop it IF EXISTS (SELECT * FROM sys.server_principals WHERE name = @certname AND type = 'C') BEGIN SELECT @sql = 'DROP LOGIN ' + quotename(@certname) IF @debug = 1 PRINT @sql EXEC (@sql) END -- And drop the certificate itself. IF EXISTS (SELECT * FROM sys.certificates WHERE name = @certname) BEGIN SELECT @sql = 'DROP CERTIFICATE ' + quotename(@certname) IF @debug = 1 PRINT @sql EXEC(@sql) END -- In the target database, we must remove the signature from the procedure, -- so that we can drop the certificate. SELECT @sql = ' IF EXISTS (SELECT * FROM sys.crypt_properties cp JOIN sys.certificates c ON cp.thumbprint = c.thumbprint WHERE cp.major_id = object_id(@procname) AND c.name = @certname) DROP SIGNATURE FROM ' + @procname + ' BY CERTIFICATE ' + quotename(@certname) IF @debug = 1 PRINT @sql EXEC @sp_executesql @sql, N'@certname sysname, @procname nvarchar(260)', @certname, @procname -- No user should have been created from the cert, but if so, we drop it. -- Since this may been performed by some else, we cannot trust the username -- to be the same as the certificate name. SELECT @sql = ' SELECT @username = NULL SELECT @username = dp.name FROM sys.database_principals dp JOIN sys.certificates c ON dp.sid = c.sid WHERE c.name = @certname' IF @debug = 1 PRINT @sql EXEC @sp_executesql @sql, N'@certname sysname, @username sysname OUTPUT', @certname, @username OUTPUT IF @username IS NOT NULL BEGIN SELECT @sql = 'DROP USER ' + quotename(@username) IF @debug = 1 PRINT @sql EXEC @sp_executesql @sql END -- And here goes the old cert. SELECT @sql = ' IF EXISTS (SELECT * FROM sys.certificates WHERE name = @certname) DROP CERTIFICATE ' + quotename(@certname) IF @debug = 1 PRINT @sql EXEC @sp_executesql @sql, N'@certname sysname', @certname IF @perms IS NULL PRINT 'No new permissions set, cleanup completed.' ELSE BEGIN -- Now we start to (re)create things. First create the certificate in master. SELECT @sql = 'CREATE CERTIFICATE ' + quotename(@certname) + ' ENCRYPTION BY PASSWORD = ''' + @pwd + ''' WITH SUBJECT = ''' + @subject + ''', START_DATE = ''20020101'', EXPIRY_DATE = ''20200101''' IF @debug = 1 PRINT @sql EXEC(@sql) -- And the login for the certificate. SELECT @sql = 'CREATE LOGIN ' + quotename(@certname) + ' FROM CERTIFICATE ' + quotename(@certname) IF @debug = 1 PRINT @sql EXEC(@sql) -- Grant the permissions. SELECT @sql = 'GRANT ' + @perms + ' TO ' + quotename(@certname) IF @debug = 1 PRINT @sql EXEC(@sql) -- Determine a path to where we can write the files for the certs. SELECT @filename = substring(physical_name, 1, len(physical_name) - charindex('\', reverse(physical_name)) + 1) + convert(char(36), newid()) FROM sys.database_files WHERE file_id = 1 -- And backup up the certificate to disk. SELECT @sql = ' BACKUP CERTIFICATE ' + quotename(@certname) + ' TO FILE = ''' + @filename + '.cer' + ''' WITH PRIVATE KEY (FILE = ''' + @filename + '.pvk' + ''', ENCRYPTION BY PASSWORD = ''' + @pwd + ''', DECRYPTION BY PASSWORD = ''' + @pwd + ''')' IF @debug = 1 PRINT @sql EXEC(@sql) -- And then restore in the target database. SELECT @sql = ' CREATE CERTIFICATE ' + quotename(@certname) + ' FROM FILE = ''' + @filename + '.cer' + ''' WITH PRIVATE KEY (FILE = ''' + @filename + '.pvk' + ''', ENCRYPTION BY PASSWORD = ''' + @pwd + ''', DECRYPTION BY PASSWORD = ''' + @pwd + ''')' IF @debug = 1 PRINT @sql EXEC @sp_executesql @sql -- If possible, delete the certs from disk. SELECT @cmd = 'DEL "' + @filename + '.*"' IF (SELECT value_in_use FROM sys.configurations WHERE name = 'xp_cmdshell') = 1 BEGIN IF @debug = 1 PRINT @cmd EXEC xp_cmdshell @cmd END ELSE BEGIN PRINT '******** xp_cmdshell disabled, you need run this command manually' PRINT @cmd END -- We can now sign the procedure. SELECT @sql = 'ADD SIGNATURE TO ' + @procname + ' BY CERTIFICATE ' + quotename(@certname) + ' WITH PASSWORD = ''' + @pwd + '''' IF @debug = 1 PRINT @sql EXEC @sp_executesql @sql -- Finally, drop the private key of the cert from the databases. SELECT @sql = 'ALTER CERTIFICATE ' + quotename(@certname) + ' REMOVE PRIVATE KEY' IF @debug = 1 PRINT @sql EXEC (@sql) SELECT @sql = 'ALTER CERTIFICATE ' + quotename(@certname) + ' REMOVE PRIVATE KEY' IF @debug = 1 PRINT @sql EXEC @sp_executesql @sql END END TRY BEGIN CATCH DECLARE @msg nvarchar(4000) SELECT @msg = error_message() RAISERROR(@msg, 16, 1) END CATCH
Before we move on, I like to point out a few virtues for dynamic SQL, even if they are not directly related to the topic of this article:
There is a version for SQL 2012 of this script in the file grantrights-2012.sql. It is worth dwelling on the piece where the certificate is copied for a second:
-- Copy the certificate to the target database. SELECT @sql = ' CREATE CERTIFICATE ' + quotename(@certname) + ' FROM BINARY = ' + convert(varchar(MAX), certencoded(cert_id(quotename(@certname))), 1) + ' WITH PRIVATE KEY (BINARY = ' + convert(varchar(MAX), certprivatekey(cert_id(quotename(@certname)), @pwd, @pwd), 1) + ', ENCRYPTION BY PASSWORD = ''' + @pwd + ''', DECRYPTION BY PASSWORD = ''' + @pwd + ''')' IF @debug = 1 PRINT @sql EXEC @sp_executesql @sql
Since the script is a single batch, there is no need for temp tables. Instead, I copy the certificate in a single group of statements. The above three statements replaces no less than four groups of statements in the script above. Observe also that in the call to cert_id, I apply qoutename on @certname. This is required, since the name of the certificates includes brackets.
We will now turn to the third method in SQL Server to provide permission through stored procedures: the EXECUTE AS clause. On the surface, EXECUTE AS is much simpler to use than certificates, but as it works through impersonation, there are side effects which may be unacceptable. We will also see that for granting server-level permissions, EXECUTE AS is inappropriate in environments where there are users who have full permissions on database level, but not on server-level.
EXECUTE AS is two things. It is a clause that you can add to a stored procedure or any other SQL module, and that is what you can use to grant permissions to non-privileged users. There is also a statement EXECUTE AS, and we will look at the statement before we turn to the clause.
The statement EXECUTE AS permits you to switch your execution context to impersonate another login or user. Here are examples of the two possibilities:
EXECUTE AS LOGIN = 'somelogin' EXECUTE AS USER = 'someuser'
Once you want to become your original self, you use the REVERT statement. (If you have changed databases, you will first need to return to the database where you issued the EXECUTE AS statement.) If the EXECUTE AS statement is executed in a lower-level scope – that is, in a stored procedure or a batch of dynamic SQL – there is an implicit REVERT when the scope exits. Thus if you run:
EXEC('EXECUTE AS LOGIN = ''frits''; SELECT SYSTEM_USER') SELECT SYSTEM_USER
the second SELECT will not return frits, but your own login name.
To perform EXECUTE AS you need IMPERSONATE rights on the login/user in question. (This permission is implied on all logins if you have sysadmin rights and on all users in a database where you have db_owner rights.)
As an extra thrill, you can stack EXECUTE AS, so you could first become login1, then user2 etc. Each REVERT would take you back to the previous context. This would require each login/user to have impersonation rights on the next login/user in the chain.
There are two apparent uses for the EXECUTE AS statement:
In the latter case, the application should add the clause WITH NO REVERT or WITH COOKIE to the EXECUTE AS statement. Else a malicious user could inject a REVERT statement and gain the rights of the proxy login. (As this goes a little beyond the scope for this article, I refer you to Books Online for further details.)
If you use EXECUTE AS LOGIN this is exactly the same as if you had logged into SQL Server as that user directly. You will have the permissions of that login, you can access the databases that login can access and so on. I have not been able to detect any difference at all, save for the function
If you use EXECUTE AS USER it is a little different. As long as you only run commands within the database, it is just as if you had logged in as that user. But if you try to access another user database you will get an error message, and if you try to perform some server-level action like sp_who you will only get back a minimum of data, even if this user maps to a login in the sysadmin role. When you impersonate a database user you are by default sandboxed into that database. We will look more into this later. For now, I say that if you use EXECUTE AS to test permissions, you should in most cases use EXECUTE AS LOGIN. The exception is when you are testing access rights for users that are purposely created WITHOUT LOGIN.
I should also mention that there is an impersonation shortcut for the
EXECUTE(@somesql) AS LOGIN = 'somelogin' EXECUTE(@somesql) AS USER = 'someuser'
The purpose of this is the same as for the EXECUTE AS statement; for a high-privileged user to impersonate a low-privileged user.
Before I move on, I should mention that there is an older command SETUSER which also can be used for impersonation. The semantics for SETUSER are less clear than for EXECUTE AS, and SETUSER is deprecated. If you are still using SETUSER, there is all reason to change to EXECUTE AS.
Note: When impersonating a Windows user, it's a common mistake to put the name in brackets, but this does not work and results in somewhat cryptic error message. That is, it should be
EXECUTE AS 'Domain\User', not
EXECUTE AS '[Domain\User]'.
So far the statement EXECUTE AS. We will now look at the clause WITH EXECUTE AS you can add to your stored procedure. As for certificates, we will first look at using the EXECUTE AS clause to give users rights for actions within the database, and as with certificates we will use dynamic SQL as our example.
To repeat, these were the presumptions for the dynamic SQL example:
CREATE TABLE testtbl (a int NOT NULL, b int NOT NULL) go CREATE PROCEDURE example_sp AS EXEC ('SELECT a, b FROM testtbl') go GRANT EXECUTE ON example_sp TO public go
As we saw earlier, ownership chaining does not work here. To use EXECUTE AS to make it possible for users to run example_sp without SELECT permission on testtbl, the steps to take are:
In code, it looks like this:
-- Create a proxy user. CREATE USER exampleproxy WITHOUT LOGIN -- Give it permissions on the table. GRANT SELECT ON testtbl TO exampleproxy go -- Add EXECUTE AS to the procedure. CREATE PROCEDURE example_sp WITH EXECUTE AS 'exampleproxy' AS EXEC ('SELECT a, b FROM testtbl') go
Since the sole purpose for this user is to carry permissions, we create the user WITHOUT LOGIN. As for what rights to grant to the proxy user, the discussion in the section Granting Rights to the Certificate User applies here as well: only grant the permissions needed.
The effect of the EXECUTE AS clause is the same as of the EXECUTE AS USER statement: that is, impersonation. As with certificates, the user gets the rights of exampleproxy, but there are two important differences: 1) It's not that the rights of the proxy user are added to your rights, but you are John Malkovich. 2) If there is a call to an inner stored procedure or a trigger fires, you are not reverted back to your original self; you continue to execute in the context of the proxy user. It is not until you exit the stored procedure with the EXECUTE AS clause that you return to your true self.
This can have drastic and far-reaching consequences, which we shall look into in a moment. First though, a complete script that shows the use EXECUTE AS to grant permissions for dynamic SQL. (Again, please refer to the introductory note about the example scripts in this article):
USE master go -- Create a test login. CREATE LOGIN testuser WITH PASSWORD = 'ExECaS=0=TeST' go -- Create the database to run the test in. CREATE DATABASE execastest go USE execastest go -- Create the test user. CREATE USER testuser go -- Create the test table. CREATE TABLE testtbl (a int NOT NULL, b int NOT NULL) INSERT testtbl (a, b) VALUES (47, 11) go -- Create a proxy user and give it rights to access the test table. CREATE USER exampleproxy WITHOUT LOGIN GRANT SELECT ON testtbl TO exampleproxy go -- Create two test stored procedures, one with EXECUTE AS and one -- without, and grant permission. CREATE PROCEDURE noexecas_sp AS SELECT SYSTEM_USER, USER, name, type, usage FROM sys.user_token EXEC ('SELECT a, b FROM testtbl') go CREATE PROCEDURE example_sp WITH EXECUTE AS 'exampleproxy' AS SELECT SYSTEM_USER, USER, name, type, usage FROM sys.user_token EXEC ('SELECT a, b FROM testtbl') EXEC noexecas_sp go GRANT EXECUTE ON example_sp TO public GRANT EXECUTE ON noexecas_sp TO public go -- Switch to the test user. EXECUTE AS LOGIN = 'testuser' go -- First run the procedure without EXECUTE AS. This gives a permission -- error. EXEC noexecas_sp go -- Then the signed procedure with EXECUTE AS. Now get the data back. EXEC example_sp go -- Become ourselves again. REVERT go -- Clean up USE master DROP DATABASE execastest DROP LOGIN testuser
This is similar to the script for certificates, but you will notice that the outcome is different. When the test user runs noexecas_sp directly, he gets a permission error as expected. But when example_sp calls noexecas_sp, there is no permission error, as was the case when we used a certificate. And when we look at the output from sys.user_token we see why. When noexecas_sp is called directly, we get:
SYSTEM_USER USER name type usage ------------- ------------ ---------- --------- -------------- testuser testuser testuser SQL USER GRANT OR DENY testuser testuser public ROLE GRANT OR DENY
But when noexecas_sp is called from example_sp, we see this:
SYSTEM_USER USER name type usage --------------- ----------- ----------- --------- -------------- S-1-9-3-2024... exampleproxy exampleproxy SQL USER GRANT OR DENY S-1-9-3-2024... exampleproxy public ROLE GRANT OR DENY
As you see, there is no trace of testuser. (The data in the column for SYSTEM_USER is due to that exampleproxy was created WITHOUT LOGIN. In lieu of a login name, SYSTEM_USER returns the SID.)
With certificates, the permissions of the certificate user are added to the rights of the current user. This means that if there is some basic permission granted to everyone, say SELECT permission in a certain schema, you don't have to grant that permission to the certificate user. For a proxy user for EXECUTE AS you must grant all permissions needed. But this cuts both ways. Recall that certificates do not help when users have been explicitly denied permission, since DENY takes precedence over GRANT. This limitation does not exist with EXECUTE AS, since it's only the permissions of the proxy user that count.
SQL Server has a couple of functions that returns the
current login or user: SYSTEM_USER, SESSION_USER, USER,
Now, where do you use these functions? I can think of two of very typical cases.
When you use EXECUTE AS both these schemes break. Code that implements row-level security will return no data, or even worse, data that the real user does not have permission to see. Auditing will be useless, as all updates will appear to come from the same user.
Had the effect been constrained only to the very procedure with the EXECUTE AS clause, it could have been somewhat manageable. But since the impersonation lingers when other SQL modules are invoked, for instance triggers, this means that code that are not aware of the EXECUTE AS clause, will cease to work. Now, how is that for backwards compatibility?
Another side effect concerns existing code. Say that a procedure with
EXECUTE AS calls an existing stored procedure old_sp, and this procedure
makes some assumptions of what rights the current user
There are also concerns for the DBA who likes to monitor his system with help of Profiler and various DMVs, that I will look into separately a little later.
What can you do to mitigate these consequences? We will look at four
different possibilities: 1) EXECUTE AS CALLER, 2)
Before looking into the methods above, we need to look at the EXECUTE AS clause in full, to see its full powers – or I am tempted to say its full horrors.
Rather than specifying an explicit user in the EXECUTE AS clause, you can specify any of the keywords CALLER, OWNER and SELF.
CALLER is innocent. This means that the procedure should execute in the context of the calling user. That is, how stored procedures how normally work, so EXECUTE AS CALLER is merely a way of explicitly expressing the default.
EXECUTE AS SELF is short for EXECUTE AS 'yourusername'. That is, if you create a procedure and add WITH EXECUTE AS SELF to it, anyone who runs the procedure will execute with your permissions. (And anything they update, you will be held accountable for.)
EXECUTE AS OWNER, finally, means that the procedure executes in the context of the procedure owner. As I discussed in the beginning of the article this is normally the schema owner. Thus, if the procedure is created in the dbo schema, or any other schema owned by the database owner, the procedure will execute with permissions to do anything in the database!
Here are some serious implications. If all you care about is simplicity, then you can ignore all about creating proxy users and granting them permissions. All you need to do is:
CREATE PROCEDURE example_sp WITH EXECUTE AS OWNER AS --SELECT SYSTEM_USER, USER, name, type, usage FROM sys.user_token EXEC ('SELECT a, b FROM testtbl') go
And no more permissions problems!
But remember that philosophy about multiple lines of defence in the beginning of this text. As we discussed for certificates, by using a dedicated proxy user you add one more line of defence, so if your procedure would be open for SQL injection, an exploiter can only do a limited amount of harm. On the other hand, if you use EXECUTE AS OWNER, the database will be wide open to an intruder. (Access outside the database is another matter, that we will come back to.) Again, keep in mind that even if your use of dynamic SQL is tight and free from injection vulnerabilities, someone who modifies the procedure tomorrow may make a blunder and change that.
Note here also a possible fatal consequence for a row-level security scheme. It is not unlikely that such scheme is set up so that dbo is permitted see all rows. This means that casual use of EXECUTE AS can result int users having access to data they don't have permission to see.
If you are the DBA (or at least the database owner) and are fortunate to have full control of all code that is added to the database (because you write all the code, or at least review all of it), it is only up to you. But if you are responsible for a larger application with many stored procedures, contributed by many developers, be afraid, be very afraid. One day you find that your auditing records say that a lot of data was changed by dbo, instead of the actual user. Some developer ran into an urgent problem with his dynamic SQL, posted a question on the forums and quickly learnt the four magic words WITH EXECUTE AS OWNER. His problems were solved, but yours had only just begun.
We will now look into what methods you can use to reduce the impact of the EXECUTE AS clause.
It's possible to do this in a procedure with an EXECUTE AS clause:
CREATE PROCEDURE some_sp WITH EXECUTE AS 'proxyuser' AS DECLARE @realuser sysname EXECUTE AS CALLER SELECT @realuser = SYSTEM_USER REVERT -- Do whatever requires extra privileges go
That is, with the EXECUTE AS CALLER statement, you revert to the context of the caller, and you can find out who actually called the procedure. Provided, that is, there were no impersonation on upper levels.
If the procedure is a longer one, and there is only one action that needs special privileges, for instance dynamic SQL, you can even do:
CREATE PROCEDURE someother_sp WITH EXECUTE AS 'proxyuser' AS DECLARE ... EXECUTE AS CALLER ... -- Here we need the powers of the proxy user REVERT EXEC sp_executesql @sql, ... -- Or something else which needs privs. EXECUTE AS CALLER -- Rest of the procedure
While this certainly is recommendable from the philosophy of not using more permissions than necessary, it takes more effort than just adding the EXECUTE AS clause in the beginning and run with it. It would be more reasonable to write:
CREATE PROCEDURE someother_sp AS DECLARE ... ... -- Here we need the powers of the proxy user EXECUTE AS USER = 'proxyuser' EXEC sp_executesql @sql, ... -- Or something else which needs privs. REVERT -- Rest of the procedure
Alas, this does not work An unprivileged user will get a permission error, as the rights to impersonate someone can not be given to a user through the body of a stored procedure, only the header. (Of course, by signing the procedure with a certificate you can grant that permission, but if you use certificates, you don't really need EXECUTE AS at all.)
There are many situations where EXECUTE AS CALLER does not help. If that dynamic SQL accesses a view with row-level security, it does not help to save the real user's name into a variable, as the call to SYSTEM_USER (or similar) is in the text of the view itself. The same applies if the dynamic SQL performs an update, and the auditing is based on a trigger or a default constraint. Furthermore, if a procedure sp1 with an EXECUTE AS clause calls sp2, sp2 cannot use EXECUTE AS CALLER to set its context to the caller of sp1, as the caller to sp2 is the user in the EXECUTE AS clause in sp1.
On top of that, EXECUTE AS CALLER requires a conscious action from the programmer. Someone who just heard about EXECUTE AS OWNER on the forums is not going to get through that extra hoop.
While SYSTEM_USER, USER,
Thus, anywhere you have schemes for row-level security or code for auditing it's better to use
If your row-level security and auditing schemes are based on the username in the database rather than the server-level login name, you are likely
to ask for an
Why isn't there any
If you are really paranoid and want to make sure that your procedure are not run with elevated privileges because the calling procedure has an EXECUTE AS clause, you could add this test to the beginning of your procedure:
IF SYSTEM_USER <> original_login() BEGIN RAISERROR('This procedure does not support impersonated users', 16, 1) RETURN 1 END
One possible way out here is the command SET CONTEXT_INFO and the
Here is how you would use it. When connecting for a user, the application would do something like:
DECLARE @contextinfo varbinary(128) SELECT @contextinfo = convert(varbinary(128), N'JoeCool') SET CONTEXT_INFO @contextinfo
A table with an auditing column could look like this:
CREATE TABLE audited (somedata int NOT NULL, moduser sysname NOT NULL CONSTRAINT def_moduser DEFAULT coalesce(convert(nvarchar(64), substring(context_info(), 1, charindex(0x0000, context_info()) - 1)), original_login()) ) go
The expression to get data from
On top of that, we use
I feel obliged to point out that the solution with SET CONTEXT_INFO is not entirely secure. If there are SQL injection holes in the application, a malicious user could inject a SET CONTEXT_INFO command to hide his identity. This could permit him to do actions anonymously, and to access data from row-level security schemes that he should not see.
One more thing to add about SET CONTEXT_INFO: normally the effect of a SET statement issued in a stored procedure is reverted when the procedure exits. SET CONTEXT_INFO is an exception to this rule, and the effect of SET CONTEXT_INFO is always global to the connection.
If you are a DBA who is not in the position that you can review all code that is deployed into the database (or a lead programmer/database architect who cannot review all code that is checked into the version-control system) and you are scared of the damage that EXECUTE AS could cause to your application, you may ask: is there a way to stop all this? After all, there is no need to use EXECUTE AS to grant permissions, when you can use certificates without side effects.
Microsoft touts SQL Server as "secure by default", so you would expect a knob to control whether the EXECUTE AS clause is available, and you would expect that knob to be in the OFF position by default. Not so. There is no knob at all. But you can implement your own.
If you are the permissive sort of person, you may be content to every once in a while run:
SELECT module = object_name(object_id), execute_as = CASE m.execute_as_principal_id WHEN -2 THEN 'OWNER' ELSE d.name END FROM sys.sql_modules m LEFT JOIN sys.database_principals d ON m.execute_as_principal_id = d.principal_id WHERE m.execute_as_principal_id IS NOT NULL
This displays which modules have been decorated with the EXECUTE AS clause and with which user name.
If you are the more evil sort of person, then you can put this DDL trigger in place:
CREATE TRIGGER stop_execute_as ON DATABASE FOR CREATE_PROCEDURE, ALTER_PROCEDURE, CREATE_FUNCTION, ALTER_FUNCTION, CREATE_TRIGGER, ALTER_TRIGGER AS DECLARE @eventdata xml, @schema sysname, @object_name sysname, @object_id int, @msg nvarchar(255)
-- Get the schema and name for the object created/altered. SELECT @eventdata = eventdata() SELECT @schema = C.value(N'SchemaName', 'nvarchar(128)'), @object_name = C.value(N'ObjectName', 'nvarchar(128)') FROM @eventdata.nodes('/EVENT_INSTANCE') AS E(C)
-- Find its object id. SELECT @object_id = o.object_id FROM sys.objects o JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.name = @object_name AND s.name = @schema
-- If we don't find it, it may be because the creator does not have -- have permission on the object. (Yes, this can happen.) IF @object_id IS NULL BEGIN SELECT @msg = 'Could not retrieve object id for [%s].[%s], operation aborted' RAISERROR(@msg, 16, 1, @schema, @object_name) ROLLBACK TRANSACTION RETURN END
-- Finally check that the catalog views whether the module has any -- EXECUTE AS clause. IF EXISTS (SELECT * FROM sys.sql_modules WHERE object_id = @object_id AND execute_as_principal_id IS NOT NULL) BEGIN ROLLBACK TRANSACTION SELECT @msg = 'Module [%s].[%s] has an EXECUTE AS clause. ' + 'This is not permitted in this database.' RAISERROR (@msg, 16, 1, @schema, @object_name) RETURN END go
The trigger first retrieves the schema and object names for the created
object from the
Variations of this theme include checking execute_as_principal_id for -2 (OWNER) and power users, or permit EXECUTE AS if the proxy user does not map to a login. (That is, a user created WITHOUT LOGIN.)
Would anyone be this evil? Well, if you have an auditing scheme that relies on SYSTEM_USER or similar function, and you don't want to rewrite your code right now, do you have any choice?
Note: If you are on SQL 2008, you may ask if this could be implemented with Policy-Based Management. It probably can, but I would not recommend that use you the On Prevent option in PBM, as PBM may silently decide to turn off checking if it deems your conditions to be too complex. (See this Connect bug for details.) Possibly you could use PBM to monitor the use of EXECUTE AS.
As I mentioned, EXECUTE AS also has implications for the DBA who likes to monitor his system. Say that there is a procedure which has the heading:
CREATE PROCEDURE some_sp WITH EXECUTE AS 'proxyuser' AS
Say that the user Nisse runs this procedure, and there is a trace with captures the statements in this procedure. What will the column LoginName display? That depends. If proxyuser was created from a login with the same name, the value in LoginName will be proxyuser. If proxyuser was created WITHOUT LOGIN, the value will be a SID, that is, a value starts like S-1-9-3-913356... But in no case the name Nisse will be displayed.
This has some ugly ramifications. If you commonly apply filters on LoginName, EXECUTE AS can cause users to fall off the radar for the duration of the procedure with the clause. If you rely on tracing for auditing, EXECUTE AS can also result in the wrong person being credited/blamed for a certain action.
LoginName is not the only column that is affected, but also the column NTUserName, although this column does not always change. It changes if the impersonated user is a Windows user or a user created WITHOUT LOGIN, but not if the user is created from an SQL Server login. At least, that is what my quick testing indicates.
This also extends do DMVs like sys.dm_exec_sessions. The columns login_name and nt_user_name behaves like LoginName and NTUserName in Profiler and reflect the name of the impersonated user. The same is true for sysprocesses etc.
Thankfully, there are alternatives. In Profiler you can use the column SessionLoginName. The value in this column corresponds to the value returned by the function
Likewise, in sys.dm_exec_sessions there is the column original_login_name, which is one of the columns at end of the table; it was added in SP2 of SQL 2005. In sysprocesses, there is no value corresponding to
So far we have looked at using EXECUTE AS to give permissions within a single database. What happens if you try to access other databases or perform an action that requires a server-level permission?
Answer: you run into a roadblock. Consider this procedure created in some other database than AdventureWorks:
CREATE PROCEDURE crossdb WITH EXECUTE AS OWNER AS SELECT COUNT(*) FROM AdventureWorks.Person.Address go EXEC crossdb
If you run this logged in as sa you get:
Server: Msg 916, Level 14, State 1, Procedure crossdb, Line 2 The server principal "sa" is not able to access the database "AdventureWorks" under the current security context.
Since sa usually can access everything, this comes as quite unexpected. But this is because there is a safeguard here. The EXECUTE AS clause always impersonates a database user, never a server login. And when you impersonate a user, you are sandboxed into the current database, and you are denied any access outside that database. This applies to the EXECUTE AS clause in a procedure as well as the statement EXECUTE AS USER. (But not to EXECUTE AS LOGIN.)
The same is true for server-level permissions. If you try:
CREATE PROCEDURE reload_sp WITH EXECUTE AS OWNER AS TRUNCATE TABLE reloadable EXEC('BULK INSERT reloadable FROM ''C:\temp\reloadtest.csv'' WITH (FIELDTERMINATOR='','', ROWTERMINATOR=''\n'')') go EXEC reload_sp
Even if you are logged with sysadmin rights, you will get this error message:
Msg 4834, Level 16, State 4, Line 2 You do not have permission to use the bulk load statement.
To open the sandbox, you must open two doors. If the database is owned by a user with sysadmin permission, one of the doors are already open. The other door is this statement:
ALTER DATABASE db SET TRUSTWORTHY ON
If the database is trustworthy, and you impersonate user1 with the statement
EXECUTE AS USER = 'user1' or the clause
EXECUTE AS 'user1' in a stored procedure, you will be able to exercise any rights that user1 may have in other databases or on server level.
To set a database as trustworthy you need sysadmin rights. And this is by no means a step you should take casually. There are some scenarios where this setting is safe play, but there are also many where it opens a glaring hole in your server security. I will discuss this in detail, but to keep the focus of the main topic – granting permissions to stored procedures – I will first show how to use EXECUTE AS to grant bulk-copy permissions.
As with certificates, using EXECUTE AS to give bulk-copy permissions takes a little more work. The steps are:
As there is not much new here, I will just make a few comments, before I give you a complete script with all steps and a test case.
Since ADMINISTER BULK OPERATIONS is a server-level permission, we need to create a full login in this case. It's a good idea to revoke the proxy login the right to connect to SQL, and I do this in the test script below.
As discussed in the previous section, we need to mark the database as trustworthy to break out from the sandbox.
Just like we did with certificates, we must put the BULK INSERT statement in dynamic SQL, because of a bug in SQL Server.
So here is the test script for using BULK INSERT with EXECUTE AS. (And as always, the introductory note on the examples applies):
USE master go -- Create a test file for bulkload. EXEC xp_cmdshell 'ECHO 978,123,234 > C:\temp\reloadtest.csv', no_output EXEC xp_cmdshell 'ECHO -98,13,85 >> C:\temp\reloadtest.csv', no_output go CREATE LOGIN testuser WITH PASSWORD = 'ExECaS=0=TeST' go -- Create the database to run the test in. CREATE DATABASE bulkcopytest go -- Mark the database as trustworthy. ALTER DATABASE bulkcopytest SET TRUSTWORTHY ON go -- Create a proxy login, which is to have the bulk-copy rights. CREATE LOGIN bulkproxy WITH PASSWORD = 'lkjSeF&hskldjh?löKDdf/jlk98sdfjälksdjg' go -- Grant rights for the proxy login and make it unable to login. GRANT ADMINISTER BULK OPERATIONS TO bulkproxy REVOKE CONNECT SQL FROM bulkproxy go -- Move to test database. USE bulkcopytest go -- Create the non-priv user and the proxy user. CREATE USER testuser CREATE USER bulkproxy go -- A test table. CREATE TABLE reloadable (a int NOT NULL, b int NOT NULL, c int NOT NULL) go -- Test procedure with BULK INSERT. CREATE PROCEDURE reload_sp WITH EXECUTE AS 'bulkproxy' AS TRUNCATE TABLE reloadable EXEC('BULK INSERT reloadable FROM ''C:\temp\reloadtest.csv'' WITH (FIELDTERMINATOR='','', ROWTERMINATOR=''\n'')') go -- Give test user right to execute them. GRANT EXECUTE ON reload_sp TO public go -- Grant the proxy user rights to truncate and insert to the test table. GRANT ALTER, INSERT ON reloadable TO bulkproxy go -- Insert some test data. If test succeeds, this data should disappear. INSERT reloadable (a, b, c) VALUES (12, 23, 34) go -- Switch to the test user. EXECUTE AS LOGIN = 'testuser' go -- Run the bulk load. EXEC reload_sp go -- Back to ourselves. REVERT go -- Verify that bulk load succeeded. SELECT a, b, c FROM reloadable go REVERT go -- Clean up. USE master DROP DATABASE bulkcopytest DROP LOGIN bulkproxy DROP LOGIN testuser EXEC xp_cmdshell 'DEL C:\temp\reloadtest.csv', no_output
Exactly how dangerous is TRUSTWORTHY? Permit me to approach this question in a somewhat roundabout way. For many years, this article just said that you should think twice before turning on TRUSTWORTHY, but did not go into details. Then one day, I got a mail from a reader who asked a question that got me thinking.
My correspondent had a problem. He wanted to grant access to BULK INSERT, and used my example for EXECUTE AS as a template, but he could not get it to work. There was a twist in his case, he wanted the database owner to be a plain server login, and not a member of sysadmin.
I sat down and played with my bulk-copy example and I was able to confirm his findings. I read through the topic Extending Database Impersonation by Using EXECUTE AS in Books Online and this was when I learnt that the sandbox has two doors that both must be open. Say there is a database A, and in this database we impersonate a user U – with the statement EXECUTE AS USER or the EXECUTE AS clause in a stored procedure. To be able to exercise the rights that the user U may have outside the database, the following conditions must be true.
|1.||The database A must be TRUSTWORTHY.|
|2a.||For access to another database B, the owner of A must have been granted the permission AUTHENTICATE in the database B.|
|2b.||For actions that require server-level permissions, the owner of A must have been granted the permission AUTHENTICATE SERVER.|
So did I answer to my correspondent that he should grant his database owner AUTHENTICATE SERVER? No. I had a nagging feeling that there was something hiding here, and after some thinking, I came to the realisation that this was just a different way to give the DB owner the possibility to do everything on the server – that is, the rights of sysadmin. How can it be? Consider this scenario:
Say that you are DBA for a server that hosts many databases, owned by various people in your organisation. One database owner, let's call him David B Owner, comes to you with this stored procedure to perform BULK INSERT. He now needs your help to get the server-level permissions for the procedure to work. We have already looked at how do this with certificates, and you have learnt that this way you can have full control what permissions you have granted to what code. If David changes his code, he has to come to you again so you can sign it anew.
But assume that David B Owner persuades you to instead take the route with EXECUTE AS. David has already written a procedure and tested it out on his personal server, and you are swamped with other things. And maybe you don't want David come to you again and again, every time he changes the procedure. After all, what damage can you do with ADMINISTER BULK OPERATIONS alone? Following the example in the previous section, you create the proxy login which you grant ADMINISTER BULK OPERATIONS, you make David's database trustworthy, and you grant David AUTHENTICATE SERVER. David merrily leaves your office. But what exactly did you do now? Did you in any way ensure that all David's database can do on server level is BULK INSERT?
Back at his desk, David runs this in his database:
CREATE USER [Domain\ServerDBA] -- That's you! go EXECUTE AS USER = 'Domain\ServerDBA'
That is, David creates a user for you in his database, and then he impersonates that user. Since he owns the database, he has full permissions to do anything in the database, including these two actions. As long as at least one of the two doors in the sandbox are closed, David can only play that he is you inside in his own database. But you were kind to open both doors to him, and now he has all powers on the server that you have. On top of all, auditing will give you the blame for what he is doing, unless auditing is based on
This is a classical example of privilege elevation, and the sandbox exists precisely to prevent this from happening by default.
In this last example, a non-privileged user was the database owner, but in many shops is customary to have sa or some dedicated SQL login as the database owner for all databases. (The problem with having individuals as database owners is that when people leave the company the DBA is not always informed when the user is dropped from the Active Directory, leaving the DBA with a database owned by an orphaned user.) But there are still people like Anna DeMin and David B Owner who are application admins or whatever, and they are member of db_owner in that database. In this scenario, what does it mean to set the database as trustworthy?
If the database is owned by sa (or some other user with sysadmin rights) the situation is just like above. Since sa is the owner, one of the doors of the sandbox is open from the start. If you make the database trustworthy, any person with db_owner rights can impersonate a server login with sysadmin rights just and do whatever he likes. What if the databases are owned by a generic login which has no other server permissions than owning all the databases? In this case, the door to server-level permission is closed, but the doors to all user databases are open. A malicious user with db_owner rights can do
EXECUTE AS USER = 'dbo'
and if the database is trustworthy, he can access all databases on the server owned by that login with full permission, which means that the user can read and write data he is not authorised to access.
In these examples I have assumed that the evil user is in the db_owner role. But db_owner is not required. More precisely, it's sufficient to have permission create users in the database and and have permission to impersonate users. Being member of db_securityadmin and db_accessadmin is sufficient. You should also not overlook the possibility that two users with supplementing permissions can work together.
You have now seen that TRUSTWORTHY is a switch that applied casually that can be utilised by malicious persons. But is this switch ever secure?
Certainly. When it comes to give plain users server-level permission through stored procedures, EXECUTE AS + TRUSTWORTHY is safe if all persons who have elevated permissions in the database also are members of sysadmin or have CONTROL SERVER. In this trivial case, there is no person who can use impersonation to elevate his permissions. This scenario is not unlikely on a server that is dedicated to a single application. However, keep in mind that one day you may be oblivious and grant a person you don't trust sysadmin rights to have db_owner permissions in that database. Maybe a support person for a vendor application. Maybe a junior DBA (who may prove to be less than junior when it comes to exploit security holes!) So while EXECUTE AS may seem simpler, I would say that for server-level access, you should always use certificates. Keep in mind that with certificates you have full control over what permissions you grant. Even if you don't want to review Anna's and David's bulk-insert procedures over and over again but instead give them the password to the certificate, the only permission they can ever abuse is ADMINISTER BULK OPERATIONS. Whereas with EXECUTE AS and a trustworthy database, there are no restrictions at all.
In the previous section I showed that it is dangerous to make a database trustworthy, if all databases are owned by the same generic user. But if all databases have individual owners, it's a different matter. Note here that individual owners do not have to be physical persons, but it could be a generic login for each database. If this is the situation, there are a few scenarios for cross-database access where EXECUTE AS + TRUSTWORTHY may be perfectly acceptable.
Consider an application that consists of several databases all with the same owner, and there is a need for stored procedures to access data in more than one database. In this article we have looked three alternatives to address this situation: 1) Database chaining and the database option DB_CHAINING. 2) Certificates. 3) EXCUTE AS + Trustworthy. If the requirement is that every application database should be able to access all the other databases, then database chaining may be the simplest solution.
But say that there is only one database where there are stored procedures with cross-database access, and you don't want to permit access from the other databases. Since DB_CHAINING must be enabled for all databases, this rules out this option. The advantage with EXECUTE AS + TRUSTWORTHY over database chaining is that you can select which databases you make trustworthy. Of course, if you decide to use EXECUTE AS for cross-database access, you need to make sure that you can handle the consequences of impersonation and make sure that you don't rely on SYSTEM_USER et al, but only use
Here is a second scenario: there are two databases, A and B, which are part of different, but related, applications, and the databases have different owners. There is a need to access data in database B from A. To do this with EXECUTE AS, database A must be TRUSTWORTHY, and furthermore the database owner of A must be granted the permission AUTHENTICATE in database B. For this to be permissible, all users with db_owner or similar rights in database A must be entitled to see all data in database B, since they now can do:
CREATE USER owner_of_database_B go EXECUTE AS USER = 'owner_of_database_B'
They can now do whatever they want in database B. To some extent this is a matter about trust. If the owner of database B trusts all users in database B not to mess up his database, he can grant AUTHENTICATE to the owner of database A. After all, having indirect permission through AUTHENTICATE is from a legal point of view not the same as being added to the db_owner role in the database. Still owner of B is taking a risk, and personally, I say if there is sensitive data in the database he should not accept to grant AUTHENTICATE to the owner of A, but insist on certificate signing, and review all code that accesses his database.
Obviously, the point about trust can be made about server permissions as well. If you trust Anna DeMin and David B Owner, you can grant them AUTHENTICATE SERVER. But in my opinion, there is too much at stake here to even consider this.
I like to stress again, that a presumption for it to be acceptable to make a database TRUSTWORTHY is that the database is owned by a user specific to that database, or group of databases. As soon there is a generic owner who owns unrelated databases, TRUSTWORTHY cannot be considered permissible. (Unless there never are any users who are only db_owner in a subset of the databases.)
We looked previously at how we could make it possible for users of an application to start a certain job with help of certificates. The solution is somewhat dubious, since it requires you to counter-sign three procedures in msdb. Could this be done better with EXECUTE AS without compromising security? I think so. Here are the steps for a possible solution.
EXECUTE AS 'jobstartuser'.
EXECUTE AS 'dbo'that calls the procedure in msdb.
The reader may be shocked here, since we have learnt that if you own a trustworthy database and have AUTHENTICATE permission in another database, then you can get the power of the owner of that database by creating a user for him in your own database and then impersonate him. And yet I'm suggesting this? And with msdb, a system database?
Yes. You see, there is a special case. The owner of msdb is, by default, sa. And if you try any of:
CREATE USER sa CREATE USER Nisse FROM LOGIN sa
You will be told:
Msg 15405, Level 16, State 1, Line 1 Cannot use the special principal 'sa'.
You may ask: what happens if I create a user for someone I know is member of sysadmin and impersonate that user? The answer is that in this case, you will access msdb as guest. As long as that person is not an explicit member of msdb, that is. And there is a weakness with this solution. There is maybe little reason to add members of sysadmin to msdb. But what if there are operators or junior DBAs who are not sysadmin, but who are entitled to administer jobs? They have to be users in msdb, so they can be added to the various SQL Server Agent roles. And with AUTHENTICATE permission in msdb, the owner of database A can impersonate these guys and do things he should not be permitted to.
There is potentially a second problem with this solution. Who says that it is supported to put user procedures in msdb? Maybe it is, but I have not been able to find an answer in either direction. When I asked in our internal MVP forum, the only reply I got was Why don't you create the procedure in master? At first I did not see the point, as it would only serve to make the solution to be more complicated. Sure, no risk that the database owner would be able to impersonate operators in msdb, but instead he would have to be granted AUTHENTICATE in master.
But after some more thinking I realised that using an intermediate database was the right thing, but it should not be master, but a dedicated database. So here is the modified list of steps:
EXECUTE AS 'jobstartuser'.
EXECUTE AS 'dbo'that calls the procedure in jobstarter.
When I devised this solution, I debated with myself whether I should really have this jobstartuser. If you instead use EXECUTE AS OWNER in the procedure that calls sp_start_job, there is no need to create this extra login. Since I have advocated that you should never grant more permissions than needed, I chose to follow this line. But in this particular case, I cannot really blame you if you prefer EXECUTE AS OWNER. And you could argue that this is safer, since if jobstartuser is mistakenly granted permissions in jobstarter it should not have, this could lead to a security hole.
Before I show you an example script, I like to point out an important difference to the solution with certificates. In that solution, there is no code in msdb, nor is there any intermediate database. Instead the procedure that calls sp_start_job is in the source database. This is possible with certificates, since the DBA can have full control over what can be done with the ceritificate, for instance by dropping the private key. But when we use EXECUTE AS, the call to sp_start_job must be outside of reach for the database owner.
Here is a complete script that demonstrates the solution (the introductory remark on the example scripts applies as always). If you want to run this, you need to create a job called Testjob. It does not have to do anything particularly meaningful.
USE master go -- Create a login for a database owner as well plain test user. CREATE LOGIN databaseowner WITH PASSWORD = 'JoBS=tA7RTte5t' CREATE LOGIN testuser WITH PASSWORD = 'eXEc=a$=TeST' CREATE LOGIN jobstartuser WITH PASSWORD = 'No login !!' DENY CONNECT SQL TO jobstartuser go -- Create test database and set owner. We set the database trustworthy. CREATE DATABASE jobstarttest ALTER AUTHORIZATION ON DATABASE::jobstarttest TO databaseowner ALTER DATABASE jobstarttest SET TRUSTWORTHY ON go -- Create an intermediate database. This database *must* be owned by sa. -- This database should never have any non-sysadmin privileged users. CREATE DATABASE jobstarter ALTER AUTHORIZATION ON DATABASE::jobstarter TO sa ALTER DATABASE jobstarter SET TRUSTWORTHY ON go -- Next stop is msdb. go USE msdb go -- Create a user for jobstartuser and give permissions. CREATE USER jobstartuser EXEC sp_addrolemember SQLAgentOperatorRole, jobstartuser go -- Set up things the intermediate database. USE jobstarter go -- Create a user for the jobstartuser. CREATE USER jobstartuser go -- We add the database owner as a user and grant him AUTHENTICATE. CREATE USER databaseowner GRANT AUTHENTICATE TO databaseowner go -- Create a procedure to start a certain job. CREATE PROCEDURE start_this_job WITH EXECUTE AS 'jobstartuser' AS EXEC msdb..sp_start_job 'Testjob' go -- Permit the databaseowner to run this procedure. GRANT EXECUTE ON start_this_job TO databaseowner go -- Move to test database. USE jobstarttest go -- Create a database user for the test login as well as the proxyuser. CREATE USER testuser go -- Create a procedure that calls our start procedure in msdb. CREATE PROCEDURE start_our_job WITH EXECUTE AS 'dbo' AS EXEC jobstarter..start_this_job go -- Give test user right to execute the procedure. GRANT EXECUTE ON start_our_job TO testuser go -- Switch to the test user. EXECUTE AS LOGIN = 'testuser' go -- Start the job, this succeeds. EXEC start_our_job go -- Back to ourselves. REVERT go -- Clean up. go USE msdb go DROP USER jobstartuser go USE master go DROP DATABASE jobstarttest DROP DATABASE jobstarter DROP LOGIN testuser DROP LOGIN databaseowner DROP LOGIN jobstartuser
You may find this solution a bit too elaborate, and I can certainly agree. A better solution may to be use a mix of impersonation and certificate signing. Put the procedure start_this_job in msdb and use EXECUTE AS to get access to sp_start_job. But instead of making the source database TRUSTWORTHY, you use certificate signing to give permission to run start_this_job. This also relieves you of the requirement that the database must not have an individual owner.
As a final note: if you want to see which user in the source database that actually started the job, counter-signing the system procedures is the only choice. The auditing in msdb is performed through SYSTEM_USER so any impersonation breaks that.
Before we leave EXECUTE AS, there is one more side effect I have yet to discuss. This is a little more on the advanced side, and something I learnt from SQL Server MVP Adam Machanic.
In a CLR module, you can access the WindowsIdentity object. The main purpose for this is in assemblies that have been marked as EXTERNAL_ACCESS or UNSAFE where you want to access resources outside SQL Server with the Windows permissions of the actual user. To do this, you need to impersonate that user, or else the access will be through the service account for SQL Server.
As long as there has not been any impersonation through EXECUTE AS, SqlContext.WindowsIdentity.Name will return the domain and the Windows user name, if the user logged in through Windows authentication. For an SQL login, WindowsIdentity is Null, so access to SqlContext.WindowsIdentity.Name yields a Null exception.
But if there is an EXECUTE AS clause somewhere on the call stack, you can no longer retrieve the user name for the Windows user. In most cases, WindowsIdentity is Null. But, if the database was set as trustworthy, and the EXECUTE AS is for a user with sysadmin privileges, then WindowsIdentity.Name will return the name of the service account for SQL Server.
In this section I will cover three other methods to secure SQL Server. Sometimes I see people ask on the newsgroups and forums How can I grant access to an application? That is, they don't want the users to be able to access the tables directly from SSMS or Excel, but only from the application. The regular approach to achieve this is to use stored procedures, and we have already looked what possibilities they offer. But not all applications use stored procedures. In this section, I will briefly look at three solutions you can employ regardless whether you use stored procedures or not.
Application roles were added in SQL 7. The idea is that you create a role to which you assign the necessary privileges to run the application. The users have no permissions at all beyond the database access. The application calls the system procedure sp_setapprole to activate the role. To do this, the application must pass a password that can be obfuscated when sent over the wire.
Application roles may seem what you are looking for, but the password is a weak point. If you have a two-tier application, you can never achieve a secure solution with application roles. The password has to be embedded in the application, or stored somewhere the user has read access. You can chop it into pieces and store the pieces in the four corners of the application, but at best that is security by obscurity. It's a different matter if you have a three-tier application. Then you can store the password on the middle tier somewhere the users do not have read access. You should still need to beware that anyone who can get access to the network wire to SQL Server may be able to eavesdrop and crack the password.
By default, when you activate an application role, you cannot back out of it. This has an effect on connection pooling; if you try to reuse a connection where an application role has been active, you will get an error. But you can get a cookie back from sp_setapprole, which you then can pass to sp_unsetapprole before you disconnect.(Please see sp_setapprole in Books Online for the exact syntax.)
This also makes it possible for having several application roles with custom permissions for various tasks, similar to what we have discussed for certificates and EXECUTE AS. That is, you would set the application role, perform the SQL that needs special permissions, and then unset the role. (Note that you cannot call sp_setapprole from within a stored procedure; it must be called from the top-level scope.) But due to the password issue, it is not a solution that I recommend.
Since application roles are database entities, you cannot use them for things that require server-level permissions, for instance bulk load.
When you use application roles, functions that return login names – SYSTEM_USER,
I have already touched at application proxies in several places, mainly in the sections on the EXECUTE AS statement and SET CONTEXT_INFO. Here I like to give just a few more remarks.
For an "application proxy" to be meaningful, the application must have at least three tiers. The middle tier authenticates the user and then connects to SQL Server. The same arrangement can be achieved with application roles, but with one difference: the application proxy can be a Windows login, so there is no password crossing the wire.
An interesting observation on SET CONTEXT_INFO
is that it
could serve as a full alternative to EXECUTE AS to impersonate the real user. All checks and auditing in the application that require knowledge about the real user would use
It is possible to set up a Remote Desktop connection so that a specific application is started, when the user connects. Furthermore, on Windows 2008, it is possible to set this up so that the user arrives directly to the login screen of the application, and the application appears as if it executes from his computer. That is, there is no desktop from the computer running Terminal Server. Please don't ask me how you do to set this up; I'm an SQL Server MVP, not a Windows MVP. But I've seen a demo of it.
If you have a two-tier application, you can use this to ensure that users can connect only through the application, and not through Access, Excel, Management Studio or whatever. You need to configure the network so that the SQL Server machine is not directly accessible from the users' computers, only from the computer running Terminal Server. One way to do this is to configure the firewall on the SQL Server machine to only accept connections from certain IP addresses.
Since this solution builds on things outside my realm, I cannot fully asses how secure it is. For instance, I don't know if there is any possibility for users to intercept the login process in Terminal Server. Nevertheless, it is an interesting option. Not the least if you have a two-tier application, you don't want to re-architect.
Security is always a challenge. One aspect is that security and convenience rarely goes hand in hand. Better security often means more hassle.
But security is also a challenge, because the holes in your security scheme may not always be apparent. To work with security means that you constantly have to think around corners. Can this permission be exploited by a malicious user? Could there be privilege elevation? You cannot only consider the current situation, but you must also try to see into the future. Maybe your current setup is secure because of some assumptions that are true now. But what if those assumptions are not true to tomorrow?
In this article I have presented a number of solutions and suggestions, which I believe to be secure. But I cannot rule out that I've made a shortcut too many somewhere. By all means, if you apply any of my solutions in an area where security is top priority, you should make your own evaluation of the solution to assess whether there is a hole somewhere.
In this article we have looked at three different solutions to grant permissions through stored procedures: ownership chaining, certificates and EXECUTE AS.
Of these, ownership signing only works in a limited scenario, but a very common one, and ownership signing is what you will use 99 % of the time or even more.
In the situations where ownership signing is not sufficient, you can always use certificate signing to grant other permissions. With certificates you can have very tight control over what permissions you grant. Not the least is this important if you are a server DBA who needs to grant server-level permission to users in application databases.
And then there is EXECUTE AS... As you have realised from this article, I am less than enthusiastic over EXECUTE AS. Everything you can do with EXECUTE AS you can do with certificates. (Save to cover up for an explicit DENY.) But it has to be admitted that EXECUTE AS is simpler. So I think that EXECUTE AS is OK to grant database permissions under these circumstances:
From this follows that if you are a plain developer and need a solution to grant permissions beyond what is possible with ownership chaining, you cannot start using EXECUTE AS on your own initiative. You first need to discuss with your DBA or the chief designer of the application, so that you don't wreak havoc of something.
There are also some situations where EXECUTE AS is meaningful for cross-database access, and we have looked at some examples. The presumption is that the database has an individual owner, so that the effect of making it trustworthy is limited.
But when it comes to server-level permissions, you should be extremely conservative with using EXECUTE AS, since this requires the database to be trustworthy and the database owner to be granted AUTHENTICATE SERVER. Any user in that database with sufficient permission will be able to elevate his permission to the sysadmin role. You should have very good reasons not to use certificates here.
If you have suggestions for improvements, corrections on contents, language or formatting, please mail me at firstname.lastname@example.org. If you have technical questions that any knowledgeable person could answer, I encourage you to post a question to the SQL Server Security forum on MSDN/Technet.
2011-12-31 – Added text about a new feature in SQL Server 2012, that makes it easier to copy a certificate from one database to another, including the new section CREATE CERTIFICATE FROM BINARY in SQL 2012.
2011-07-13 – A major overhaul of the article to reflect that five years had passed since the original publication. I have also added some quite important new material. Changes in summary:
2011-01-11 – Corrected the expression to decode
2006-03-28 – Rewrote the section on asymmetric keys on suggestions from Razvan Socol.
2006-02-19 – Original version.
Back to my home page.