4/15/2008
excerpt--Giving Permissions through Stroed Procedures
FROM : http://www.sommarskog.se/grantperm.html
Giving Permissions through Stored Procedures
Ownership Chaining, Certificates and the Problematic EXECUTE AS
An SQL text by Erland Sommarskog, SQL Server MVP.
Introduction
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 look at five different ways to implement this:
- Ownership Chaining.
- Signing Procedures with Certificates.
- Using impersonation with EXECUTE AS
- Application roles.
- "Application proxies".
Of these, the second and the third are new for SQL 2005, and the emphasis of this article are on those two methods. Oh, before I go on, I should say that this article is entirely focused on SQL 2005, and I am not discussing solutions for SQL 2000.
Table of Contents
IntroductionNotes on the Example Scripts
Some Philosophy on Security
Owner/Schema Separation
New Statements
Creating Objects in a Schema
Ownership Chaining
How it Works
Example of Ownership Chaining
Not All Permissions are Transferable through Ownership Chaining
Dynamic SQL
CLR Modules
Cross-Database Access
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
Who's Who?
Using Certificates with Bulk Copy
Full Example for Bulk-load
Cross-Database Access
When Certificates Don't Work
Using Asymmetric Keys
Which Procedures are Signed by Which Certificates?
What about the Password?
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
original_login()
SET CONTEXT_INFO
Using DDL Triggers to Stop All This
Cross-Database Access
Yet another Side Effect – WindowsIdentity
Using EXECUTE AS to Give Bulk-Copy Permissions
Should EXECUTE AS ever be Used?
Using Application Roles
"Application Proxies"
Acknowledgments and Feedback
Revision History
Overview
The classic method for granting permissions through stored procedures is ownership chaining. This is still the prime method for plain table access, but you cannot grant any permission 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 offers two new methods to give users access through stored procedures: you can sign procedures with certificates, or 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 first three methods can be applied to individual procedures, application roles and "application proxies" are solutions that you typically use on an application-wide scale. I am fairly brief on these methods, and "application proxies" is something I mainly mention in passing when discussing EXECUTE AS. (I put "application proxy" in quotes throughout the article, as this is a term that I've coined myself, and may not be established terminology.)
Notes on the Example Scripts
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 or the Surface Area Configuration tool, 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 could perform those actions manually.)
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 being created 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 names in the script!)
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 reason logins are created is because it's difficult to demonstrate permissions features when running as sysadmin.
To contain everything into one script, I make heavily use of the new 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 and run as a test user. If you prefer, you can stop the scripts at EXECUTE AS, log into a second query window to run the part up to REVERT.
Some of the scripts in the first part of the article, use statements that I don't cover until later in the article. So if you see something you don't recognise, stay tuned.
If you run the scripts on a server that runs under Windows 2003, you may get error messages that some passwords do not comply with the local password policy. This is most likely to happen with the passwords for the certificates. Just modify the passwords as needed.
Some Philosophy on Security
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 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 defence lines 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.
Owner/Schema Separation
Before we look at any of the methods to grant permissions, we need to look at a change which can be a bit breath-taking to seasoned users 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
server.database.owner.object
But in SQL 2005 this changes to
server.database.schema.object
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 sample database that comes with SQL 2005. (But which is not installed by default.)
SQL 2005 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. (sys, by the way, is the schema for the new catalog views and the dynamic management views that replaces the old system tables, another breath-taking subject which falls entirely beyond the scope of this article.)
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.
New Statements
There are several new statements related to owner/schema separation, and I will only cover the most pertinent for this article.
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. (Please see Books Online for details.)
There is a new way to create users in SQL 2005:
CREATE USER newuser [WITH DEFAULT_SCHEMA = someschema]
This command replaces sp_adduser which now is deprecated. Overall, in SQL 2005, there is a drive to replace system stored procedures with
CREATE SCHEMA newuser
go
CREATE USER newuser WITH DEFAULT_SCHEMA = newuser
go
ALTER AUTHORIZATION ON SCHEMA::newuser TO newuser
(The last commands makes newuser owner of the schema created in his name.) Odds are very good that you don't need that schema, so there is good reason to abandon sp_adduser entirely. CREATE USER also has some options not offered by sp_adduser, that we will return to later in this text.
CREATE ROLE newrole
CREATE ROLE 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 also a CREATE LOGIN replacing sp_addlogin. As with CREATE USER, CREATE LOGIN has some new options, that we will come back to later in this article.
There is also DROP USER instead of sp_dropuser etc.A little note here: if you have a database that is carried over from SQL 2000, you will need to use sp_dropuser to drop users created in SQL 2000, 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. (Or run ALTER USER usr WITH DEFAULT_SCHEMA = dbo for all these users, and then drop the schemas with their name.) If you try to drop users created with sp_adduser from the GUI in SQL Server Management Studio, you will get an error message, as the GUI uses DROP USER.
Creating Objects in a Schema
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, all objects must have the same owner, so it much simpler if all objects are owned by the schema owner from the start. (It is possible to transfer the ownership of an object to someone else, if you have a need for this.)
Ownership Chaining
How it Works
Ownership chaining is the classical way of giving users access to objects through stored procedures in SQL Server. And while SQL Server provides two new methods, ownership chaining is what you will use 95% 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 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. This is because sp1 and tbl1 have the same owner, and this is ownership chaining. Ownership chaining can also be achieved through 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 see soon, there are permissions that cannot be transferred through ownership chaining.
(Note: in previous versions of SQL Server, one reason for applications to employ different object owners, was if they wanted to use schemas. But since in SQL 2005, dbo can own all schemas, this should not be necessary.)
Example of Ownership Chaining
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
Not All Permissions are Transferable through Ownership Chaining
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 and UPDATE) 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 effective.
Another example of a command where ownership chaining does not work is BULK INSERT. In SQL 2000, the user must belong to the fixed server role bulkadmin and also have INSERT permission on the table.
All these are situations that in SQL 2005 can be resolved by signing procedures with a certificate, or using impersonation with EXECUTE AS, methods that we shall look into later in this article.
Dynamic SQL
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 to give users SELECT access on the tables, as long as this was compliant with corporate security policy.
In SQL 2005 this is not necessary. Again the new features, procedure signing and impersonation can be used to give users permission to execute dynamic SQL.
CLR Modules
Another novelty in SQL 2005 is that 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, the users will need direct permissions to these tables, or you must sign the procedures with a certificate or use impersonation.
Cross-Database Access
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 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 chaining, use the command ALTER DATABASE db SET DB_CHAINING ON. (In SQL 2005. In SQL 2000 you need to use sp_dboption.) 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.
- Add Jill as a user in his own database.
- Create a schema in his database owned by Jill.
- Create stored procedures in the Jill schema that accesses Jill's database. Since Jill owns the schema, she also owns the procedures, as noted above. (Jack could also create the procedures in the dbo schema, and then make Jill owner of those procedures.)
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 – the user needs to have EXECUTE permission to the procedure in the other database.
An alternate way to implement cross-database access is to sign procedures with certificates, and we will see an example of this later.
Signing Procedures with Certificates
We will now turn to the first of the two new methods in SQL 2005 to grant permissions through stored procedures, signing a procedure with a certificate.
Using Certificates within a Database
We will first look at using certificates for giving permissions within the database. 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 at all, 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:
- Create a certificate.
- Create a user associated with that certificate.
- Grant that user SELECT rights on testtbl.
- Sign the procedure with the certificate, each time you have changed the procedure.
What happens when the procedure is invoked is that the rights of the certificate user are added to the rights of the actual user, so rights of any of them can apply. Technically, we can describe this as the certificate user being 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 the invoked module is also signed by the certificate). But, if the procedure invokes a batch of dynamic SQL through
In code, the four steps are as in this example:
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.
Creating the Certificate
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. When the sole use for the certificate is to sign procedures to grant extra permissions, there may not always be a dire need for a strong password, something I will discuss a little more in the section What about the Password?
The WITH SUBJECT clause is part of the metadata for the certificate, and 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 RTM may produce this message:
Warning: The certificate you created is not yet valid; its start
date is in the future.
Since the default for the start date is the same second as you issue the command, the message is bogus, and there is a bug report for it. The message is only informational, but if you don't want to see it, specify a start date.
Creating the Certificate User
CREATE USER examplecertuser FROM CERTIFICATE examplecert
As noted above, CREATE USER replaces sp_adduser in SQL 2005, and here we create a user of a type that is new for SQL 2005: a certificate user, a user that is associated with a certificate. Such a user exists in the database only and is not associated with any login.
Granting Rights to the Certificate User
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, or even make it db_owner to relieve you from 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 have taken the necessary steps to protect your procedure against this attack. But that is today. Code changes through 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.
If you have many procedures that need certificates, what do you do then? One possibility is of course to use the same certificate for a group of procedures that has related needs. Another strategy would be to have one certificate for each stored procedure that uses dynamic SQL or needs other permissions that can not be granted through ownership chaining. This may sound unmanageable, but a simple way out is to include the statements for the steps 1-4 in the file that holds the code for the stored procedure. (This presumes that you actually put your stored procedures in files, and not merely use a database as a repository for them. But since source code should be under version control, the latter is poor practice.) Yes, this is a very casual handling of the password. I will return to that later.
Signing the Procedure
This syntax is new for SQL 2005.
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. If you need the permissions from two certificate users, you can sign the procedure with both certificates.
Admittedly, it's a hassle having to sign a procedure each time it's changed. But recall that certificates are a general cryptographic feature, not just something for stored procedures. Signing something with a certificate, means that you add a signature that is derived from a combination of the certificate and the current text. Anyone who has access to the public key of the certificate can then verify that the text has not been changed since the text was signed. Thus, if you change the procedure, the signature is no longer valid, and the procedure must be signed anew.
Putting it All Together
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
Who's Who?
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 user token, so its rights 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. If you remove that comment, and run the script again, you will see that when you call unsigned_sp from example_sp, you still get a permission error. You will also see in the output from sys.user_token, that examplecertuser is no longer there.
Using Certificates with Bulk Copy
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. 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. (This corresponds to the fixed server role bulkadmin in SQL 2000.)
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:
- Create a certificate in the master database.
- Create a login for that certificate.
- Grant that login rights to perform bulk operations.
- Export the certificate to file.
- Switch to the application database.
- Import the certificate from the file.
- Delete the file from disk.
- Create a user for the certificate.
- Grant that certificate user rights to truncate the target table, and to insert into it.
- Sign the stored procedure with the certificate, each time you have changed the procedure.
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 new lingo 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 rights to run bulk loads.
Finally we export the certificate to disk with the BACKUP CERTIFICATE command. 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.
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 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 file with the certificate from disk as a security precaution, since any database owner on the machine could load the certificate into his database. (Note that xp_cmdshell is disabled by default. You could always delete the file directly from Windows.)
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 of 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 2005 RTM, 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.)
Full Example for Bulk-load
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.)
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.
Cross-Database Access
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 = 'All you need is love'
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 = 'Tomorrow never knows',
DECRYPTION BY PASSWORD = 'All you need is love')
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 master into the test database.
CREATE CERTIFICATE crossdbcert FROM FILE = 'C:\temp\crossdbcert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk',
DECRYPTION BY PASSWORD = 'Tomorrow never knows',
ENCRYPTION BY PASSWORD = 'A day in life')
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 = 'A day in life'
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
When Certificates Don't Work
It is possible to deny a user access to an object with the DENY statement. In this case you cannot give the user access to the object through a stored procedure that you have signed with a signature, as DENY takes precedence over GRANT.
Using Asymmetric Keys
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 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 folllowing practical differences:
- An asymmetric key never expires, which from for signing purposes is a slight advantage.
- Neither do you have do specify a subject or an asymmetric key.
- You cannot export an asymmetric key from a database. If you want to sign procedures in two databases with the same key, you could create an asymmetric key outside SQL Server and import it into the databases. (This is possible.) I will need to add the disclaimer that I have not tested whether this actually works.
- The fact that an asymmetric key cannot be exported, can on the other hand been as a security advantage, as someone cannot take your key into another database without your knowing. (See discussion below in the section What about the Password?)
- The key for a certificate in SQL Server is always 1024 bits, where as for an asymmetric key you can choose between 512, 1024 and 2048 bits. It's possible that there is a performance gain by using a shorter key for signing your procedures. However, I have not tested this, nor have I had it confirmed, so it's pure speculation on my part.
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.
Which Procedures are Signed by Which Certificates?
To see which procedures that have been signed in a database, you can run this query:
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. (So if you are the DBA on a corporate server with many databases owned by different people, you might find it easier to give ADMINISTER BULK OPERATIONS to the users who need it, rather than introducing a level of indirection through certificates. It all depends on how much you trust your database owners.)
What about the Password?
Normally passwords should be strong and kept secret, but I have already hinted that in this particular case this may not really be necessary.
What damage can you do if you know the password for a certificate? To sign a procedure you need permission on the certificate and permission to alter the procedure. If you have those rights are you likely to be authorised to know the password as well. In any case, if you can alter a procedure, you can edit the procedure and add an EXECUTE AS clause that, as we shall see, has wider effects than signing with a certificate.
There is however one situation that you may have reason to be careful with your certificate passwords, and that is if your database resides on a server where other databases are managed by people that you don't know how much you can trust. Say that there is a malicious person who has the right to sign procedures in some database, and he gets hold of the password to one of your certificates. If then he is able to run BACKUP CERTIFICATE – maybe borrowing your keyboard while you are away from it – he could import the certificate into his database and sign procedures that accesses your database. (It helps here, if you have been restrictive with what permissions you grant each certificate user.)
It follows from this that you should avoid storing you certificates on disk. And if you have to, the password for your private key should be strong and secret.
I suggested earlier that you should have the creation of the certificate and the signing in the same file as the procedure. In fact you can do this without including any password in the file. To wit, you could create the password within the file. The script would drop the current certificate if it exists, create a new one with the generated password that you would keep in a temp table for the duration of the script. You would have to run CREATE CERTIFICATE and ADD SIGNATURE from dynamic SQL.The password would be lost as the script exits.
I should add that if you are on Windows 2003, the certificate passwords are subject to the password policy defined for the operating system, just like the passwords for SQL logins are.
Finally, I should mention that rather than having a password for the certificate you could protect it with the database master key. But in my opinion, this is a much poorer alternative, since this key may also protect keys that are used to encrypt sensitive data. The password for your master key should certainly be strong and protected.
Impersonation with EXECUTE AS
We will now turn to the second method in SQL 2005 to provide any permission through stored procedures, and this is 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.
The Statement EXECUTE AS
EXECUTE AS is actually two things. It is a clause 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. But 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 another login or user just as if you had logged in as that login/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, 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:
- A privileged user can use EXECUTE AS to test queries and procedures as another user, without having to open a new query window. This can be very handy, and all example scripts in this article use EXECUTE AS for this purpose.
- To implement "application proxies". In this case, the application authenticates the users outside the server. The application connects to the server with a proxy login that has IMPERSONATE rights on the real users and then issues EXECUTE AS to run as them. When you create a user in SQL 2005, you can specify the clause WITHOUT LOGIN to create a user exists in the database only. Thus, you can implement a solution where the real users do not need any sort of direct access to SQL server.
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.)
You may be familiar with the SETUSER statement from previous versions of SQL Server. EXECUTE AS is a more elaborate version of SETUSER which now is deprecated. (In fact SETUSER has been deprecated since SQL 7, despite that there has not been any alternative until now.) If you are using SETUSER, I recommend you to switch to EXECUTE AS in SQL 2005.
I should also mention that there is an impersonation shortcut for the
EXECUTE(@somesql) AS USER = 'someuser'
EXECUTE(@somesql) AS LOGIN = 'somelogin'
The purpose of this is the same as for the EXECUTE AS statement; for a high-privileged user to impersonate a low-privileged user.
Using EXECUTE AS to Grant Permissions within the Database
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 the example.
To repeat, there were the presumptions for our 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:
- Create a proxy user.
- Grant the proxy user the necessary permissions.
- Add the EXECUTE AS clause to the stored procedure.
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.
What is the effect of the EXECUTE AS clause? The same as of the EXECUTE AS 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 you call another stored procedure, or activate a trigger, you are not reverted back to your original self, but 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 previous context.
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 permissions
-- 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.)
The Side Effects of EXECUTE AS
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.
- In the WHERE clause of a view or stored procedure for row-level security.
- To fill in the values of auditing columns, through a DEFAULT constraint or a trigger or directly in a stored procedure.
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?
There are more side effects. One that is likely to be a concern to more than one DBA, is what you see in Profiler. You will see the login of the user impersonated by EXECUTE AS, or just a SID if the user was created WITHOUT LOGIN. For auditing, you can still rely in the column NTUserName, or in worst case, look up other events for the same SPID. On the other hand, if you filter on the login name, EXECUTE AS can cause information to be hidden for you.
There is yet another side effect that 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
What can you do to mitigate these consequences? We will look at four different ways: 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 horrors.
WITH EXECUTE AS CALLER | SELF | OWNER
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 always worked until SQL 2005, 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. (Accesses 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 may see all rows. This means that casual use of EXECUTE AS can have the serious consequence of users getting to see 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 newsgroups 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.
The EXECUTE AS CALLER statement
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. Note that 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 newsgroups is not going to get through that extra hoop.
original_login()
While SYSTEM_USER, USER,
So if you change your existing views for row-level security, and your defaults and code for auditing to use
If your row-level security and auditing schemes are based on the login name,
(Why there isn't any
If you are really paranoid and want to make sure that your procedures 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 procedures:
IF SYSTEM_USER <> original_login()
BEGIN
RAISERROR('This procedure does not support impersonated users', 16, 1)
RETURN 1
END
(Instead of raising an error, could you perform something like EXECUTE AS LOGIN = ? Not really. You would have to use EXECUTE AS with a user for this plot to work, so you would need to translate
SET CONTEXT_INFO
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(128), context_info()), SYSTEM_USER)
)
go
By using
I need to add 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 change his information. 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.
Using DDL Triggers to Stop All This
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 2005 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 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[1]', 'nvarchar(128)'),
@object_name = C.value(N'ObjectName[1]', '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.) Just bail out.
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?
Cross-Database Access
What happens if you try to access objects in other databases from a stored procedure that has an EXECUTE AS clause? 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 or a Windows login with sysadmin rights, 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. Once an EXECUTE AS clause is in effect, you are sandboxed into the current database, and you are denied any access outside that database.
If all you want to do is to give users access to a database that they do not have permission to, the best method is to sign the procedure with a certificate that exists in both databases, see the example above. In which case you don't need EXECUTE AS at all.
But if you want to break out of the sandbox, you can do this. There is a database switch to swing the box wide open:
ALTER DATABASE db SET TRUSTWORTHY ON
If the database is trustworthy, you can add EXECUTE AS 'user1' to a procedure that need to access data in a second database, as long as user1 maps to a login that that also has access to the second database (and that has permissions to the objects that the procedure accesses).
To set a database as trustworthy you need sysadmin rights.
Just like you should be careful with turning on DB_CHAINING for a database, you should also think twice before you mark a database as trustworthy, particularly on consolidated server where different databases have different owners and caretakers. For a server that is dedicated to a single application, it's less dramatic. But there may appear some unexpected effects when a database is trustworthy, and not all are well documented. One such case is seen in the next section.
What if you want to use impersonation cross-database and don't want turn on TRUSTWORTHY? For once, I'm taking the easy way out and refer you to the topic Extending Database Impersonation by Using EXECUTE AS in Books Online, which also covers the TRUSTWORTHY option more in detail. A brief summary, it says that you should sign the procedure with a certificate...
Note: the sandbox also applies if you run the statement EXECUTE AS USER = 'user'. It does not apply to EXECUTE AS LOGIN = 'login'.
Yet another Side Effect – WindowsIdentity
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, SqlContext.WindowsIdentity.Name will return domain and 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 sa or a user with sysadmin privileges, then WindowsIdentity.Name will return the name of the service account for SQL Server.
Using EXECUTE AS to Give Bulk-Copy Permissions
As with certificates, using EXECUTE AS to give bulk-copy permissions takes a little more work. The steps are:
- Create a proxy login, in the master database.
- Grant the proxy login ADMINISTER BULK OPERATIONS. Again in master.
- Mark the target database as trustworthy.
- Switch to the application database.
- Create a user for the proxy login.
- Grant the proxy user ALTER and INSERT on the target table.
- Add an EXECUTE AS clause to the procedure.
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 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, as is done in the test script below.)
We need to mark the database as trustworthy, since the sandbox when EXECUTE AS is in effect affects to server-level permissions as well. As noted above, using certificates is an alternative to setting the database as trustworthy. But if we are into certificates, we don't need EXECUTE AS anyway
As with certificates, the BULK INSERT statement needs to be in dynamic SQL, because of a bug in SQL 2005 RTM.
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 = 'lkjsefhskldjhlöksdfjlksdfjä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
Should EXECUTE AS ever be Used?
As you have understood, my enthusiasm for the way how EXECUTE AS works is limited. But I don't really want to say "don't use it!", but rather I want to make you aware of what impact those four small words WITH EXECUTE AS OWNER can have on your application. As I said in the beginning of this article, if you are a DBA, you need to be aware of what a developer may add to a stored procedure. And if you are a developer, you should know that the impact of EXECUTE AS may be far wider than just giving permissions to users for the duration of a stored procedure.
While using certificates always works (save when users have explicitly been denied access) it can't be denied that EXECUTE AS is far less hassle. If you have a stored procedure that does not invoke any other stored procedures, does not perform any audited actions, there is no row-level security and finally you don't care too much for Profiler, then it is difficult to find any objections. For a new application that uses
Still, if you are a developer and you consider using EXECUTE AS, please first discuss this with your DBA and make sure that he understands the impact. A special case if you work for an ISV. In this case, you have many DBAs out there who are going to manage your application. Count on that at least a few of them will like to look at the login name in Profiler. So if you are an ISV, I would recommend you to be very cautious with the use of EXECUTE AS.
I should also add that I have gotten the impression that impersonation often is used in the context of Service Broker. Exactly what this means, I cannot really say, as Service Broker is something that I have yet to digest.
But whatever you do, be very very restrictive with EXECUTE AS OWNER! Instead work with login-less users that have the exact set of permissions needed.
Using Application Roles
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, it must pass a password that can be obfuscated when sent over the wire.
Application roles looked promising when they first appeared, but there have been two problems with them:
- The password is a weak point. Anyone who can listen to the wire will get a string that can be cracked. The same thing goes for the application executable, although you can do a more or less good job of hiding the password in it. (For a three-tier application, I guess that the middle tier could store the password on its server in a place where users do not have access.)
- In SQL 7 and SQL 2000 there is no way to back out of the application role, once it has been set. For this reason, it has not been possible to use connection pooling with application roles. Most applications these days connect only to issue a query and then disconnect, relying on that the client API maintains a connection pool which makes reconnection cheap.
The good news is that in SQL 2005, the last issue has been resolved. You can now get a cookie back from sp_setapprole, and you can then pass this cookie to sp_unsetapprole before you disconnect. (Please see sp_setapprole in Books Online for the exact syntax.)
Not only does this make it possible to use connection pooling with application roles, but it also opens 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, call the stored procedure 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,
"Application Proxies"
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. The advantage is that the application proxy would not need any elevated privileges at all, but would only need permissions to run the stored procedures of the application. However, given the injection issue that I mentioned in the previous paragraph, the EXECUTE AS statement with its NO REVERT and WITH COOKIE clauses appears as safer.
Acknowledgments and Feedback
I like to thank SQL Server MVPs Dan Guzman, Martin Bell, Adam Machanic, Hugo Kornelis and Kent Tegels as well as Razvan Socol for submitting valuable suggestions for this article.If you have suggestions for improvements, corrections on contents, language or formatting, please mail me at esquel@sommarskog.se. If you have technical questions that any knowledgeable person could answer, I encourage you to post to any of the newsgroups microsoft.public.sqlserver.security or comp.databases.ms-sqlserver.
Revision History
2006-03-28 - Rewrote the section on asymmetric keys on suggestions from Razvan Socol.
2006-02-19 – Original version.
No comments yet