In this article you will learn you how you can package permissions for an action in SQL Server inside a stored procedure. That is, rather than granting the permissions the action requires directly to users and thereby permit them to do a lot more things they should not be allowed to, you grant them rights to run a stored procedure which performs this specific action but not more than that. A simple example in an application is that far-reaching updates to a customer record should only be permitted for managers. An example from the DBA world is that you have power users who need be able to see who are connected to their database, but they must not see other users on the system.
For this packaging task, we have three methods to our disposal:
The by far the most important and simplest to use of these is ownership chaining. In fact, it is so simple that you may not be aware of that you are using it. Simple as it may be, it is limited to a quite specific but very common scenario that accounts for 95 % or more of our stored procedures. It is only for the extra special ones that we need to employ the other two methods. Both serve the same purpose, but they do so in different ways. Of the two, certificate signing is preferable in most cases. It may seem overly complex at first, but we will learn how we can overcome that by automation. EXECUTE AS appears at first glance to be a lot easier to use, but this is a mirage: you need good understanding of your system to know whether you can use EXECUTE AS without causing undesired side effects. When it comes to server-level permissions, using EXECUTE AS requires that the database to be set as TRUSTWORTHY, and you will learn that this can introduce a major security vulnerability on your server.
This article consists of two parts: the main article, which you are reading right now, and an appendix. In the main article, you will learn to use these three techniques, for database-only permissions, for server-level permissions and for cross-database access. The appendix is an catalogue of examples that show how these techniques can be applied to specific problems.
The main article is quite long. One reason for this is that I illustrate about everything I discuss with code samples and the output from the samples. Another is that I have taken some space to highlight important security risks that you need to be aware of. You may have been referred to this article in response to a question that you need urgent help with. For that situation, I would like to give some reading advice.
Table of Contents
Notes on Scripts, Objects, Databases etc
Some General Considerations on Security
Limitations with Ownership Chaining
A Security Threat with Ownership Chaining
Using Certificate Signing in the Database
Creating a User from the Certificate
Granting Permissions to the Certificate User
The Scope of the Certificate Token
Using Certificates to Package Server-Level Permissions
Signing Stored Procedures in the master Database
Server-level Permissions in a User Database
Automating Signing for Server-Level Permissions
One More Level: Availability Groups
Interlude: Access to the Database Only through the Application
Using EXECUTE AS on Database Level to Package Permissions
The Ramifications of Impersonation
EXECUTE AS and Server-Level Permissions
Other Side Effects of Impersonation
Conclusion on Cross-Database Access
This article contains a lot of code snippets. With the exception of a few snippets that are only for demonstrational purposes, the snippets are collected in scripts with about one script per chapter. There are links to the scripts as they are needed, and you can copy and paste from your browser to SSMS, if you like. You can also download this zip-archive which contains all scripts so that you have them locally. This archive includes files both for the main article and the appendix.
There is about one script per chapter. The names of the scripts reflect which chapter they belong to. (Scripts with names starting with app_ relate to the appendix.) Beware that the script for one chapter in the main article generally assumes that you have run the scripts for previous chapters. That is, databases, users, tables, stored procedures are reused. Thus, if you skip a chapter, you should still run the script for that chapter, but you can run all in one go.
In the scripts, there are long lines of hyphens that delimit the snippets in the article. In a few scripts there are also sections delimited by equal signs. They illustrate that you should change context to a different user or server as explained in the chapter. Since the scripts are supposed to be used with the article, they are largely uncommented.
The download also includes some utility scripts that I don't include in whole in the text, but only discuss and drop a link to. These scripts are commented.
The code in this article creates quite a few things on your server: databases, logins, etc. Therefore, I recommend that you run the code on your private instance. By all means, do not run the code on a production server! I should also point out that I assume that when you run the scripts, you are logged in with sysadmin permission. After all, you are playing the server-level DBA.
To produce the output from the scripts, I have been running SSMS set to Results to Text to make it easier to present both result sets and error messages. When you run the scripts yourself, you may prefer to run with output to grid, but you will need to switch between the Results and Messages tabs to see all the output.
At any time you feel that you don't want these objects around anymore, you can use the script CleanupAll.sql, which drops everything created in this article.
All databases created in this article includes the string Play in the name, which can help you to identify whether a database on your server may come from this article. Moreover, check before you start playing whether you have databases with this string – in that case, you need to be careful.
...and as you read on, you may sense a theme related to some guys from Liverpool.
This article applies to SQL 2005 and later. SQL 2005 was the version where certificates and EXECUTE AS were introduced, and I will not discuss older versions. Later releases have brought us some smaller additions to these features. The code in this article was written and tested on SQL 2012 and SQL 2017. The scripts include features that do not run on SQL 2005/2008. I will call these out when they appear.
This is an article about security features, and therefore it is apt to start with some general considerations on security. There is no script for this chapter, and the snippets are not to be intended to be executed.
Security is one of those difficult things, because it often conflicts with other interests in the programming trade. Users are screaming for a solution and they want it now, and at this point they don't worry about security, they only want to get their work done. But if you provide them with a solution that has a security hole and that hole is later exploited by a rogue employee or an hacker from the outside, guess who will get the blame. Therefore, you must always develop with security in mind.
A common mistake is to think we have this firewall/encryption/whatever in place, so we are safe. In my point of view, security consists of multiple lines of defence, so if one line is broken, the intruders are stopped by the next line, or at least restricted in how much damage they can inflict. An important thing to keep in mind is that computer systems are not static, but typically they undergo a lot of changes during their lifetime, both in how they are configured and how the code is written. If the system has a single line of defence, it only takes one bad programmer that makes a casual change to open a wide hole. Or one casual DBA to make a configuration change that takes down the line entirely.
This is not the place to discuss all lines of defence you should have in place, but I will just give a quick list of some items:
You may object that in our application users need to be able to create tables, and.... Well, this article is exactly about that. How you can package privileged operations that go beyond the plain vanilla DML access in a stored procedure so that you don't have to grant users (or application logins) elevated permissions.
I want to highlight one specific security vulnerability and that is SQL injection, which is one of the most commonly exploited security holes. SQL injection is possible when an application builds an SQL statement concatenating SQL syntax with data from user input. Here is a simple example in C#:
cmd = "SELECT * FROM dbo.customers WHERE name LIKE '%" + TextBox1.Value + "'%"
A malicious user can exploit this by entering something quite different from a customer name, for instance:
==' UPDATE dbo.employess SET salary = 10 WHERE name = 'BigBoss' --
The purpose of the initial == is only to prevent a lot of data coming back. The key character is the single quote. This terminates the SELECT query, and the user can now enter any statement. The closing dashes comment out any remaining syntax in the original statement. Exactly what the user is able to do in the injected SQL depends on the next line of defence: that is, what permissions have been granted.
You may say to yourself: The user does not know what the SQL statement looks like, and it will take forever to figure out how to exploit a hole. Please remember that today hacking sites is a professional trade that pays well. These people have lot of time on their hands and good tooling to help them to trawl sites to find injection holes. This is anything but a hypothetical threat.
Before I go on, I should say that the correct way of writing the above is:
cmd = "SELECT * FROM dbo.customers WHERE name LIKE '%' + @searchstr + '%'"
cmd.Parameters.Add("@searchstr", SqlDbType.NVarChar, 50).Value = TextBox1.Value
That is, rather than building an SQL string from input, you use a parameterised statement which is a constant SQL string and which thus cannot be manipulated. Not only is your code now safe from SQL injection, but there are also a few pleasant side effects: 1) The plan cache in SQL Server is used more efficiently. 2) Users can now search for O'Brien without hiccups. 3) The code is easier to read and maintain.
This article is about stored procedures, and it is not uncommon to use dynamic SQL in stored procedures. In stored procedures, too, you should use parameterised SQL statements, and the above would read like this in T‑SQL:
EXEC sp_executesql
N'SELECT * FROM dbo.customers WHERE name LIKE ''%'' + @searchstr + ''%''',
N'@searchstr nvarchar(50)', @searchstring
However, it only takes one junior developer who haven't grasped the concept, and suddenly you have something that looks like this:
EXEC('SELECT * FROM dbo.customers WHERE name LIKE ''%''' + @searchstring + '''%''')
And the exploit above is again possible. This is a theme I will return to more than once in this article to underscore why you should not grant more permissions than needed for the task.
In some shops, particularly smaller ones, the same user(s) may be responsible for administer things on server level and database level. That is, there is no distinction between the server-level DBA and the database-level DBA.
However, this is far from always the case. In a large corporation, an instance could have databases for different and unrelated department-level applications. At a hosting service, the databases could belong to customers who are unrelated to each other, or for that matter fierce competitors. The staff who work as DBAs administer the server, but they are not much involved with the individual databases, but they are administered by application admins, the developers, or some other people. They are member of db_owner, or have other elevated permissions on database level, but they typically have no permissions at all outside of their database, nor should they have – except maybe being plain users of other applications.
I have written this article with the latter scenario in mind, not because this is better than the first, but simply because it presents more challenges. The terminology I have adopted is that I talk about the server-level DBA and the other group of people as (local) power users. I often address you as the reader as if you are the server-level DBA. A recurring theme when we come to package server-level permissions in stored procedures is that you must be careful so that you don't introduce security holes that permit these local power users to elevate their permission to server level and become sysadmin.
This section is more a preparation for the rest of the article, although what you will learn here is something you can use in general.
In this article we will run many commands as users with limited permissions to test whether permissions work or not. While you could run your DBA commands from one query window, and be logged in as a plain user from a different window, the scripts make use of two very practical statements in SQL Server: EXECUTE AS USER/LOGIN. You can say one of:
EXECUTE AS LOGIN = 'LadyMadonna' EXECUTE AS USER = 'LadyMadonna'
These statements make you become the user Lady Madonna for all practical matters. Her permissions will apply, and SYSTEM_USER and other functions will return LadyMadonna. Only the function original_login() will reveal that you were the one who logged in originally.
To return to your true self, use the REVERT statement. Note that it must be issued from the same database as you issued the original EXECUTE AS statement. Thus, if you switched to another database while you were Lady Madonna, you need to switch back before you revert. Also, be careful to always put REVERT in a batch of its own like this:
EXECUTE AS USER = 'LadyMadonna' -- Do something as Lady Madonna go REVERT
There is no syntactical requirement that REVERT must be in a separate batch. But if any of the statements you perform as Lady Madonna fails, the batch may be aborted, in which case a REVERT statement at the end of the batch will not be executed. This can cause some confusion until you understand that you still are Lady Madonna. When in doubt, you can always run REVERT a few times extra. REVERT never gives an error message, even if there is nothing to revert from.
The difference between the two commands is that EXECUTE AS LOGIN impersonates a server-level login whereas EXECUTE AS USER impersonates a user on database level only. If you have the choice, use EXECUTE AS LOGIN. When you use EXECUTE AS USER there can be some confusion, as you are sandboxed into the current database and cannot access other databases or perform actions that require server-level permissions – even if the user you impersonate has the permissions required. (This is something we will look more into in further chapters in the article.) With EXECUTE AS LOGIN there are no such restrictions. That said, as long as you only want to test permissions inside the database, the two works equally well.
There are two situations when you would use EXECUTE AS USER over EXECUTE AS LOGIN:
For simplicity, we will only work with SQL logins in this article. But you can also impersonate windows users with EXECUTE AS. Note that you can do this, even if they don't have a login on the server, as long as SQL Server can find them in the AD. They must have access to the database you want to execute in, though. (But again, this can be arranged without a server-level login.) Note also that when you impersonate a Windows user, you should not enclose the name in brackets. That is, use
EXECUTE AS USER = 'Domain\User'
and not any of these:
EXECUTE AS USER = [Domain\User] EXECUTE AS USER = '[Domain\User]'
The error message for the latter is not very clear.
Note also that you cannot impersonate a Windows group; you will need to pick a member in the group to impersonate.
In this chapter we will learn about ownership chaining, how it works and what the limitations there are. I will also discuss a potential security trap with ownership chaining.
The script for this chapter is 03_ownershipchain.sql. The scripts starts with a general setup that we will use all through this article:
SET XACT_ABORT, NOCOUNT ON
USE tempdb
go
IF db_id('Playground') IS NOT NULL
DROP DATABASE Playground
go
CREATE DATABASE Playground
ALTER AUTHORIZATION ON DATABASE::Playground TO sa
go
USE Playground
go
CREATE ROLE Playrole
GRANT EXECUTE ON SCHEMA::dbo TO Playrole
CREATE USER Michelle WITHOUT LOGIN
ALTER ROLE Playrole ADD MEMBER Michelle
We start off with setting XACT_ABORT so that most errors will abort a batch directly (this is something I discuss in detail in my series Error and Transaction Handling in SQL Server) and we turn on NOCOUNT to save us the noise of the rows affected messages. Next, we move to tempdb before dropping any existing database with the name Playground so that we easily can start over. (If you already have a database with that name – be careful!) We (re)create the Playground database and we make sa the owner of the database. While it is not uncommon to find servers where sa owns all databases, I like to point out that this is not best practice. I will return to why I think so and what I think is best practice, when we discuss the TRUSTWORTHY feature. But for now, sa owns the database.
We now move over to the newly created database and create a role, Playrole, which we grant permission to run all stored procedures in the dbo schema. Finally we add a user, Michelle, whom we make a member of Playrole. Since we will use her to test database permissions only, we create her WITHOUT LOGIN.
Note: The command ALTER ROLE ADD MEMBER was introduced in SQL 2012. If you are on SQL 2008/2005, you will need to use sp_addrolemember to add Michelle to Playrole.
We will now look at the first method to package permissions in a stored procedure, that is, ownership chaining. This is something that has been in the product from day one. The principle is this: If a user U runs stored procedure S which accesses the table T, and S and T are owned by the same user, there is no permission check performed on T at all. Or more generally: if the user U runs a module M which access the object O, and M and O have the same owner, no permissions are checked. A "module" here can be a stored procedure, a user-defined function, a trigger or a view, while the most common examples of "objects" are tables, views, stored procedures and user-defined functions. (There are others, but there is no need to bury us in details.)
This may sound a little abstract, but in very many databases all objects are owned by the user dbo, which also is the owner of the database (dbo is short for database owner), and in that case the rule can be simplified: if users have permission to run a stored procedure, they don't need permissions to tables or other objects accessed by the procedure.
Let's take a look at this in practice. First we need a table to play with:
CREATE TABLE Playtable (id int NOT NULL,
somedata nvarchar(40) NOT NULL,
whodidit sysname NOT NULL
CONSTRAINT Play_default_whodidit DEFAULT USER,
CONSTRAINT pk_Play PRIMARY KEY (id)
)
It's a very simple table with an id, a token data column and an auditing column whodidit. Next, we create a simple procedure to insert a row into Playtable and select the inserted data.
CREATE PROCEDURE add_playdata @id int, @somedata nvarchar(40) AS
INSERT Playtable(id, somedata)
VALUES(@id, @somedata)
SELECT id, somedata, whodidit FROM Playtable WHERE id = @id
Now we want to try this as Michelle. Let's first see what happens if Michelle tries to insert a row into the Playtable directly, giving someone else the blame:
EXECUTE AS USER = 'Michelle' INSERT Playtable(id, somedata, whodidit) VALUES (1, 'Des mots qui vont très bien ensamble', 'Maggie Mae') go REVERT
This fails with the error message:
Msg 229, Level 14, State 5, Line 33
The INSERT permission was denied on the object 'Playtable', database 'Playground', schema 'dbo'.
This is of course not unexpected, since no one, not Michelle, nor Playrole, nor Maggie Mae or anyone else has been granted any permissions whatsoever on Playtable. It is only members of elevated roles like db_owner who can access it directly. Michelle now tries running the stored procedure, bitterly accepting that she will have to take full responsibility herself:
EXECUTE AS USER = 'Michelle' EXEC add_playdata 1, 'Des mots qui vont très bien ensamble' go REVERT
We see this output:
id somedata whodidit
----- ------------------------------------ -----------
1 Des mots qui vont très bien ensamble Michelle
That is, Michelle was now able to insert a row in the table and also read the data, despite not having any permissions on the table. Through her membership in Playrole, she had permission to run add_playdata, and because dbo owns both the table and the procedure, no permissions were checked, and the INSERT and SELECT succeeded. Before we go on, note that the whodidit column has the value Michelle; that is, the built-in function USER returns the name of the impersonated user.
I doubt that the reader feels a sense of wonder at this point, since this is something many SQL developers make use of every day – although, they may not be fully aware of the exact mechanism. But let us prove that ownership chaining actually exists. We create a new database-local user NewOwner and make it the owner of Playtable and let Michelle make a second attempt to run add_playdata:
CREATE USER NewOwner WITHOUT LOGIN ALTER AUTHORIZATION ON Playtable TO NewOwner go EXECUTE AS USER = 'Michelle' EXEC add_playdata 2, 'Words that go well together' go REVERT
This produces this output:
Msg 229, Level 14, State 5, Procedure add_playdata, Line 2
The INSERT permission was denied on the object 'Playtable', database 'Playground', schema 'dbo'.
That is, now that the procedure and table owners are different, there is a permission check. See now what happens when NewOwner also becomes the owner of add_playdata:
ALTER AUTHORIZATION ON add_playdata TO NewOwner go EXECUTE AS USER = 'Michelle' EXEC add_playdata 2, 'Words that go well together' go REVERT
Michelle can now successfully insert the row, since the procedure and the table again have the same owner.
As having other owners than dbo is a bit exotic, we restore ownership of both objects to dbo before we continue:
ALTER AUTHORIZATION ON Playtable TO SCHEMA OWNER ALTER AUTHORIZATION ON add_playdata TO SCHEMA OWNER
You may wonder why I use SCHEMA OWNER here rather than dbo. There is a subtle difference which matters if the objects are later moved to a schema owned by a different user. If I would use dbo, dbo would remain owner of the objects after the transfer to the new schema, whereas SCHEMA OWNER means exactly that. Whoever owns the schema owns the object.
From what I have said so far, it may sound like ownership works universally for all features, but this is not the case. In this section we will look at situations where you may expect ownership chaining to work, when it does not.
Ownership chaining only applies to permissions that you would grant to plain users, that is SELECT, INSERT, UPDATE, DELETE and EXECUTE permissions. Or said in another way, it applies to DML statements like SELECT, INSERT, UPDATE, DELETE and MERGE and when you use EXECUTE to run stored procedures or user-defined functions.
But for any other operation beyond this, SQL Server will always perform a permission check, which means that the user must have been granted the permissions needed – or this has been arranged for through any of the two other methods we will look at later in this article. As one example, consider this procedure which is a possible trap:
CREATE PROCEDURE truncate_playdata AS TRUNCATE TABLE Playtable go EXECUTE AS USER = 'Michelle' EXEC truncate_playdata go REVERT
Logically, nothing seems wrong here, since this is a procedure to delete all data in the table, and DELETE permission is something that is covered by ownership chaining. Nevertheless, this fails with this error message:
Msg 4701, Level 16, State 1, Procedure truncate_playdata, Line 2
Cannot find the object "Playtable" because it does not exist or you do not have permissions.
This is because the command TRUNCATE TABLE requires ALTER permission on the table, and for such a strong permission, SQL Server never suppresses the permission check, and thus Michelle needs ALTER permission herself to be able to run truncate_playdata.
This is something that has surprised more than one developer. Consider this example with very bad dynamic SQL:
CREATE PROCEDURE dynamic_playdata @searchstr nvarchar(MAX) AS
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'SELECT * FROM dbo.Playtable
WHERE somedata LIKE ''%'' + ''' + @searchstr + ''' + ''%'''
EXEC(@sql)
go
EXECUTE AS USER = 'Michelle'
EXEC dynamic_playdata 'ensamble'
go
REVERT
The error message is:
Msg 229, Level 14, State 5, Line 72
The SELECT permission was denied on the object 'Playtable', database 'Playground', schema 'dbo'.
The SQL code in @sql is not part of the stored procedure dynamic_playdata, but rather it is a nameless stored procedure which does not have an owner (or the owner can be said to be the current user). Whatever, the ownership is not inherited from the calling procedure, and therefore there is a permission check.
At first, this may seem like an irritating limitation. Wouldn't it be great to able to package a string of a dynamic SQL in a stored procedure to give the user the possibility to run a dynamic search without permissions on the table? Well, we will learn how to do that in the next chapter. But there is a good reason why ownership chaining does not apply here, and the reason is exactly that too many developers write bad dynamic SQL which is open for SQL injection. Note that the sloppy programmer who wrote dynamic_playdata declared @searchstr to be nvarchar(MAX), despite that nvarchar(40) would have sufficed. Had ownership chaining applied here, an attacker could use the procedure to access any table in the database to his or her own liking, even if the attacker only has permissions to run stored procedures.
Here is another situation where things do not work as you might expect. We want to add a check that Playtable exists before attempting an operation on it, to save the user from an ugly error message in favour of something more friendly.
CREATE PROCEDURE check_playdata @id int AS
IF object_id('Playtable') IS NOT NULL
SELECT id, somedata, whodidit FROM Playtable WHERE id = @id
ELSE
PRINT 'Playtable has not been created yet. Try again later.'
Michelle tries this procedure:
EXECUTE AS USER = 'Michelle' EXEC check_playdata 1 go REVERT
But the jaw drops when she sees:
Playtable has not been created yet. Try again later.
The reason for this surprise is that ownership chaining does not apply to access to metadata, be that through built-in metadata functions like object_id, direct access to catalog views like sys.objects or system procedures like sp_help. In all these cases, the permission VIEW DEFINITION on the object in question must be present, one way or another.
Ownership chaining never applies to access on server level, but for any operation that requires permission on server level, SQL Server always performs a permission check.
When it comes to cross-database access, ownership chaining can apply. However, by default, ownership chaining across databases is turned off. We will look at this more in the chapter Cross-Database Access.
In my experience, the by far most common arrangement is that all objects in a database are owned by dbo, and I will go as far as label scenarios where you have multiple owners as "advanced" and not discuss them in this article – save for one situation that I will discuss in an instant.
The situations for schemas is slightly similar. That is, there are plenty of databases where all objects are in the dbo schema. Partly this is due to legacy, since up to SQL 2000 owners and schema were the same thing in SQL Server. But that was long ago and I in my perspective, using multiple schemas is nothing to be considered as advanced. Schemas can serve as namespaces and help you modularise your database. They can also serve as security boundaries, since you can grant permissions on schema level. This is something we have already made use of in the Playground database, where we granted Playrole EXECUTE permission on the dbo schema. We will see the benefit of this further on in the article.
There are some different opinions on what is best practice. Some people frown on:
SELECT id, somedata FROM Playtable
and think that you should always include the schema:
SELECT id, somedata FROM dbo.Playtable
If you look at the code you have seen in this article so far, you may or may not have noticed a pattern. If you look at code where I use dynamic SQL, be that in a stored procedure or from a client, I do say dbo.Playtable. The reason for this is that Playtable alone without schema is potentially ambiguous, as the user may have a different default schema than dbo. This can lead to cache littering, since users with different default schemas cannot share plans for a batch where at least one object is not schema-qualified. Thus, to avoid this, you should always schema-qualify objects in dynamic SQL.
But it is a different matter with static SQL in stored procedures in a database where all objects are in the dbo schema and everything is owned by dbo – which, as noted above, is a very common scenario. There is no ambiguity, as object references are always resolved from the point of view of the procedure owner, not the current user. In this case, I think that adding dbo. in front of all table names is just four-character noise that only serves to make the code more verbose. I have adhered to this practice in this article, exactly to make the examples more concise. I like to stress that if your application actually uses multiple schemas, I think that you should always use two-part notation, also for the objects you put in dbo.
Before we leave ownership chaining, I like to look at a situation where ownership chaining can open for privilege elevation in combination with some other features.
Say that you have some developers that need to develop stored procedures for reports. Because you have no proper test database with production-like data, you give them permission to work in the production database. Your data is not that sensitive, so there is no issue if they can see the data, but you don't want them to make updates. You create a Reports schema and grant the developers CREATE PROCEDURE on database level (which is the only level where you can grant this permission) and ALTER permission on the Reports schema. You also make them members of the db_datareader role. To test your idea, you run this:
CREATE SCHEMA Reports go CREATE ROLE DevRole CREATE USER MrKite WITHOUT LOGIN ALTER ROLE DevRole ADD MEMBER MrKite go GRANT CREATE PROCEDURE TO DevRole GRANT ALTER ON Schema::Reports TO DevRole ALTER ROLE db_datareader ADD MEMBER DevRole
To test that you have the setup right, you first run:
EXECUTE AS USER = 'MrKite' go CREATE PROCEDURE testrep AS PRINT 11 go REVERT
This fails with:
Msg 2760, Level 16, State 1, Procedure testrep, Line 1
The specified schema name "dbo" either does not exist or you do not have permission to use it.
Good! They cannot create procedures in dbo. Next you try:
EXECUTE AS USER = 'MrKite' go CREATE PROCEDURE Reports.test_report @id int AS SELECT id, somedata, whodidit FROM dbo.Playtable WHERE id = @id go EXEC Reports.test_report 1 go REVERT
(Because we are now in another schema, it is a good idea to use the dbo prefix to avoid confusion.) However, to your chagrin this fails with:
Msg 229, Level 14, State 5, Procedure test_report, Line 1
The EXECUTE permission was denied on the object 'test_report', database 'Playground', schema 'Reports'.
This happens because no matter who creates an object in a schema, it is the schema owner who becomes the owner of the object, not the user who created the object. This is somewhat confusing, but it has the advantage that when users create objects in the dbo schema, no extra steps are needed to make dbo be the owner the objects.
So in this case, what do you do? Your first thought may be to grant DevRole EXECUTE permission on the Reports schema. But stop! Keep in mind that Mr. Kite and the gang have permissions to create procedures not only to read data, but they also can create procedures to insert, update and delete data in all tables in the Playground database. If they are granted permission to run these procedures, they can thus start manipulating the data in the database. That is, they are able to elevate their permissions beyond what you intended.
No, instead the correct action here is to break the ownership chain and the easiest way to do this is to make DevRole owner of the Reports schema. This is also changes the owner of all the objects within the schema (save those where the owner has been set explicitly):
ALTER AUTHORIZATION ON SCHEMA::Reports TO DevRole go EXECUTE AS USER = 'MrKite' EXEC Reports.test_report 1 go REVERT
Mr Kite can now run the procedure he wrote. Once the developers are satisfied with their work, you can change the ownership of the individual procedures to dbo or move/copy them to the dbo schema.
While we were able to tighten the hole here, I will have to admit that I am not enthusiastic of granting users permissions that are somewhere between plain read/write/execute permissions and membership in db_owner. The permission system in SQL Server is fairly complex and not always simple to understand. You may lure yourself to believe that you have prevented users from being able to do certain actions, when in fact they only need to do some extra tweak to circumvent your restrictions. My preference for the given scenario is to restore a copy of the production database in a test environment, where the developers can be db_owner all day long. This also has the advantage that they can add test data if they feel that the current data does not cover situations they want to test.
We have now looked at ownership chaining and we can see that it works for the main bulk of procedures we write, because most of time all we want to do is to read or write data in some tables and execute other store procedures. But we have also seen that there are situations ownership chaining does not work. In this chapter, we will learn how we can overcome these restrictions by using certificate signing.
To use certificate signing to package extra database permissions in a stored procedure, there is a recipe consisting of four steps:
At this point, this may seem like quite a bit of mumbo-jumbo to you, and I will discuss these steps in detail, not only in terms of syntax, but also the deeper meaning behind them. At the same time, we will also arrange so that Michelle can run the procedure check_playdata successfully. However, because there are so many things to explain, it will take a while until I am able to give the full picture.
The script for this chapter is 04_certsigndb.sql. The first batch of this script reads:
SET NOCOUNT, XACT_ABORT ON USE Playground
First of all, you may wonder what is a certificate? You may have encountered the term in other contexts, but not in SQL Server. A certificate is an asymmetric key with some extra metadata (which is of little interest for this article) around it. An asymmetric key consists of a pair of keys, one public and one private. The public key is just that: public. You typically expose it freely without any means of protection. The private key on the other hand is your secret, and you need to protect it in some way, for instance with a password or encrypting it with another key.
If you look at the syntax for CREATE CERTIFICATE in Books Online, you may be bewildered by all the options, but for this purpose, you will always run the command as in this example:
CREATE CERTIFICATE my_first_cert ENCRYPTION BY PASSWORD = 'All You Need Is Love' WITH SUBJECT = '"This is my first certificate"'
That is, you have to give it name, you must specify a password to protect the private key, and, because the syntax mandates it, you must also specify a subject. In a "real" certificate, the subject is supposed to reflect the owner, and there is some syntax as defined by X.509 for how the subject should be formed. I have not bothered to research that syntax, but I learnt from Michael K. Campbell that as long as you enclose the text in double quotes, anyhing goes. Most of the time, you will get away with not using double quotes, but here is an example of a command that fails::
CREATE CERTIFICATE mysecondcert ENCRYPTION BY PASSWORD = 'Eight Days a Week' WITH SUBJECT = 'Music, Song and Dance'
Because of the comma, you get this error message:
Msg 15297, Level 16, State 46, Line 10
The certificate, asymmetric key, or private key data is invalid.
Enclose the subject in double quotes and the error goes away.
Syntax-wise, this is not very difficult:
ADD SIGNATURE TO check_playdata BY CERTIFICATE my_first_cert WITH PASSWORD = 'All You Need Is Love'
That is, the command is ADD SIGNATURE and you specify the name of the object to sign. You use the clause BY CERTIFICATE to specify the certificate and since signing requires access to the private key, you need to give the password for the key.
You may ask yourself what all this means. Signing something with a certificate is something which is applicable far beyond SQL Server. For instance, if you send a email, you can sign the mail with your private key. You make your public key available, and the person who receives your mail can use the public key to verify that the contents agrees with the signature. That is, the receiver can be sure that the mail is from you, and that the contents has not been tampered with in transit.
If you work for an ISV who ships a product to many customers, you could opt to sign all your stored procedures, triggers and functions during installation and drop the private key from certificate after installation. You can then use this to verify that customers have not made their own changes to the installation.
But what does this have to do with permissions in SQL Server? That is a question we will need to defer to answer, until we have looked at all steps in the recipe. Here I will briefly discuss some details around the command itself.
You can sign a procedure with more than one certificate.
To sign a procedure, you need to have ALTER permission on the procedure and CONTROL permission on the certificate. In line with the scepticism I expressed earlier of handing out partial elevated permissions, I'd say that in practice this is an activity for members in the db_owner role.
Books Online does not give full details on what you can sign, and I have not made extensive investigations myself. What matters for this article is that you can sign all executable modules, that is, stored procedures, triggers, scalar functions and multi-statement functions. But you cannot sign views and inline table functions, which are not modules you execute. (Rather they are macros that are expanded into the query prior to optimisation.) Curiously enough, you can sign tables.
The view sys.crypt_properties holds information about all signed modules in the database. For a sample query, see the next section.
The next step is to create a user from the certificate. This is done this way:
CREATE USER my_first_cert_user FROM CERTIFICATE my_first_cert
Again, you may ask yourself what this is good for. And admittedly, the certificate user is a bit of a kludge. The SQL Server team wanted a way to tie permissions to certificates, but it seems funny to grant permissions to a certificate, so they introduced this user a bridge between the certificate and the permissions.
You may feel uncomfortable with adding an extra user to your database, but this is quite a special user. If you run
SELECT * FROM sys.database_principals WHERE name = 'my_first_cert_user'
You will see that the type_desc column returns CERTIFICATE_MAPPED_USER. Furthermore, if you try:
EXECUTE AS USER = 'my_first_cert_user'
You get an error message:
Msg 15517, Level 16, State 1, Line 22
Cannot execute as the database principal because the principal "my_first_cert_user" does not exist, this type of principal cannot be impersonated, or you do not have permission.
Also, note that you can only create one user per certificate, so this fails:
CREATE USER my_second_cert_user FROM CERTIFICATE my_first_cert
The error message is somewhat confusing, though:
Msg 15063, Level 16, State 1, Line 24
The login already has an account under a different user name.
Here is a query that lists which procedures (and other modules) that have been signed and by which certificate. It also returns any user created from the certificate:
SELECT quotename(s.name) + '.' + quotename(o.name) AS Module,
c.name AS Cert, c.subject, dp.name AS [Username], cp.*
FROM sys.crypt_properties cp
JOIN sys.certificates c ON cp.thumbprint = c.thumbprint
LEFT JOIN sys.database_principals dp ON c.sid = dp.sid
JOIN sys.objects o ON cp.major_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
Here is the result set from this query, split up over several lines for readability:
Module Cert subject
----------------------- -------------- -----------------------------
[dbo].[check_playdata] my_first_cert This is my first certificate
Username class class_desc major_id
-------------------- ----- ------------------ -----------
my_first_cert_user 1 OBJECT_OR_COLUMN 341576255
thumbprint crypt_type crypt_type_desc
-------------------------------------------- ---------- -------------------------
0x46AD9DD1B43D4F78A4533ADB1DFD7D2E2F9B47C4 SPVC SIGNATURE BY CERTIFICATE
crypt_property
-----------------------------------------------------------------------
0x94FCCA5A362D1AE8CFCFECFBE0A97FBEF46A734D1D13CA0D8B9A9C8C75C9515B2....
To permit a legible format of the article, I have truncated the last column, crypt_property, which is the signature itself. As you can see this is a binary value and not human-readable. The thumbprint is like a GUID for a certificate. That is, it uniquely identifies a certificate, not only in SQL Server, but across the planet. You may also note that the double quotes have been removed from the subject. It appears that SQL Server removes the double quotes when there are no characters that have a special meaning in the syntax for subjects.
This last step of the recipe is less revolutionary in terms of commands as this is a matter regular permission assignment. for which you use the GRANT statement. It is also possible to add the certificate user to a role with ALTER ROLE ADD MEMBER to package the powers of the role with the stored procedure.
The delicate part is to determine the correct permissions to grant. While you could solve all problems by making the cert user member of db_owner, you should make it a habit of never granting more permissions than you think that are needed. For the procedure check_playdata, the extra permission needed is the humble VIEW DEFINITION on Playtable. Thus:
GRANT VIEW DEFINITION ON Playtable TO my_first_cert_user
Now having executed all four steps in the recipe, Michelle makes a new attempt with check_playdata:
EXECUTE AS USER = 'Michelle' EXEC check_playdata 1 go REVERT
This time, Michelle sees the data:
id somedata whodidit
---- ------------------------------------- ----------
1 Des mots qui vont très bien ensamble Michelle
We have now seen that we could use certificate signing to add the VIEW DEFINITION permission to the procedure check_playdata to get it work as desired. Still, there is one piece missing for us to be able understand what happened. The last piece of the puzzle is found in the view sys.user_token. We will not query this view directly, but we will package it into our own view, called tokeninfo:
CREATE VIEW tokeninfo AS
SELECT name AS token_name, type, usage,
original_login() AS original_login,
SYSTEM_USER AS [SYSTEM_USER], USER AS DBUSER
FROM sys.user_token
That is, beside the three columns of interest from sys.user_token, we also look at the output from three system functions: original_login() that tells us who originally logged in, SYSTEM_USER which returns the current user on server level and USER which returns the current user on database level. If you run a SELECT from this view while being logged in as sysadmin, you will see something like this:
token_name type usage original_login SYSTEM_USER DBUSER
------------ --------- -------------- ------------------ ------------------ -------
dbo SQL USER GRANT OR DENY LIVERPOOL\GMartin LIVERPOOL\GMartin dbo
(Instead of LIVERPOOL\GMartin, you will obviously see your own login name. For the output in this article, I am pretending that it is produced when a certain GMartin is logged with integrated security on the domain or workstation LIVERPOOL.)
You may think that this does not tell you something you don't already know, but let's add it to the procedure check_playdata and then run the procedure again as Michelle:
ALTER PROCEDURE check_playdata @id int AS
SELECT * FROM tokeninfo
IF object_id('Playtable') IS NOT NULL
SELECT id, somedata, whodidit FROM Playtable WHERE id = @id
ELSE
PRINT 'Playtable has not been created yet. Try again later.'
go
EXECUTE AS USER = 'Michelle'
EXEC check_playdata 1
go
REVERT
We see this:
token_name type usage original_login SYSTEM_USER DBUSER
------------ -------- ------------- ------------------ --------------- --------
Michelle SQL USER GRANT OR DENY LIVERPOOL\GMartin S-1-9-3-366... Michelle
public ROLE GRANT OR DENY LIVERPOOL\GMartin S-1-9-3-366... Michelle
Playrole ROLE GRANT OR DENY LIVERPOOL\GMartin S-1-9-3-366... Michelle
Playtable has not been created yet. Try again later.
This time we get three rows from the tokeninfo view, and we can now understand what a token is. There are three of them, Michelle, the current user, and then there are public and Playrole, the two roles that Michelle is a member of. GRANT OR DENY in the usage column tells us that the token can contribute with a GRANT or DENY of permissions. The output from the three system functions is not that important at this time, but as they will be later on, I like to comment on them. original_login retains GMartin, while the DBUSER column reports Michelle, as we are impersonating Michelle. SYSTEM_USER reports only a SID, since Michelle is a user WITHOUT LOGIN.
Below the result set, we can tell from the PRINT message that the powers of the certificate have vanished. Why is it so? Recall that the point with signing something with a certificate is to be able to verify that the contents has not changed. We changed the procedure, and thus the signature is no longer valid. In fact, if you run the query against sys.crypt_properties, you will find it now returns an empty result set. That is, every time you change a signed procedure, SQL Server removes the signature.
So let's sign the procedure anew and run as Michelle:
ADD SIGNATURE TO check_playdata BY CERTIFICATE my_first_cert WITH PASSWORD = 'All You Need Is Love' go EXECUTE AS USER = 'Michelle' EXEC check_playdata 1 go REVERT
This time we get this output (for brevity, I'm not including the three columns to the right):
token_name type usage
-------------------- --------------------------- --------------
Michelle SQL USER GRANT OR DENY
public ROLE GRANT OR DENY
Playrole ROLE GRANT OR DENY
my_first_cert_user USER MAPPED TO CERTIFICATE GRANT OR DENY
id somedata whodidit
----------- ---------------------------------------- ----------
1 Des mots qui vont très bien ensamble Michelle
We see now that a fourth token has been added, the token of my_first_cert_user, and we can now explain what happens. If all of these three are true:
Then the token of the certificate user is added to sys.user_token, and it contributes with permissions just like any of the other tokens does. If the certificate user is member of one or more roles, the tokens for these roles are also added. (There are no examples of this in the main article, but you can find this in the first two chapters in the appendix.) In this case, my_first_cert_user contributes with VIEW DEFINITION on Playtable, which is why Michelle can see the data in the table.
We have now acquired understanding of the mechanisms that make certificate signing work, but there are still some more details we need to learn and we will look at them in the next section. I can sense that the reader has some questions about the fact we need to re-sign the procedure every time and how we are supposed to manage all these certificates. Don't worry. I will return to these questions.
Admittedly, the fact that we could get object_id to work for Michelle without granting her any permission directly on Playtable will have to count as a fairly small victory. Let's see if we can get this to work with something else, for instance with dynamic SQL. We previously looked at this bad procedure:
CREATE PROCEDURE dynamic_playdata @searchstr nvarchar(MAX) AS
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'SELECT * FROM dbo.Playtable
WHERE somedata LIKE ''%'' + ''' + @searchstr + ''' + ''%'''
EXEC(@sql)
When we apply the recipe on this procedure we need to determine the permissions needed. Quite obviously, this is a SELECT permission, and since a single table is accessed, all we need is to grant SELECT on this table. Thus we, get:
CREATE CERTIFICATE dynamic_playdata$cert ENCRYPTION BY PASSWORD = 'While My Guitar Gently Weeps' WITH SUBJECT = '"GRANT SELECT ON dbo.Playtable"' ADD SIGNATURE TO dynamic_playdata BY CERTIFICATE dynamic_playdata$cert WITH PASSWORD = 'While My Guitar Gently Weeps' CREATE USER dynamic_playdata$certuser FROM CERTIFICATE dynamic_playdata$cert GRANT SELECT ON dbo.Playtable TO dynamic_playdata$certuser
You may note that this time I have given the certificate and the user a name which is derived from the stored procedure. There is an obvious point in this: you can immediately see what purpose the certificate and the user serve. (In case if you think that the $ character has a special meaning, it has not. It is just another identifier character like A or _, only less commonly used.) I use the subject to state the permissions granted.
The net effect is that the query to display signed procedures directly tells us which stored procedure that are signed and what permissions that are packaged into them. Let's run that query again:
SELECT quotename(s.name) + '.' + quotename(o.name) AS Module,
c.name AS Cert, c.subject, dp.name AS [Username], cp.*
FROM sys.crypt_properties cp
JOIN sys.certificates c ON cp.thumbprint = c.thumbprint
LEFT JOIN sys.database_principals dp ON c.sid = dp.sid
JOIN sys.objects o ON cp.major_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
We see this in the first three columns (I leave out the rest in the interest of brevity):
Module Cert subject
--------------------------------- ----------------------- ------------------------------
[dbo].[dynamic_playdata] dynamic_playdata$cert GRANT SELECT ON dbo.Playtable
[dbo].[check_playdata] my_first_cert This is my first certificate
It is now time for Michelle to run this. Recall that last time, Michelle got a permission error.
EXECUTE AS USER = 'Michelle' EXEC dynamic_playdata 'ensamble' go REVERT
This time the procedure returns a result set. Thus, we have scored another victory for this technique: we were able package the SELECT permission needed to run the dynamic SQL with the stored procedure. Note here the importance of not granting more permissions than needed: the procedure is still vulnerable to SQL injection, but all a malicious user would be able to do is to wrestle out more data from Playtable than he is supposed to see. Depending on what data the table holds, this can be bad enough, but at least it is far better than what would have been the case if ownership chaining had worked – full access to all tables in the database.
One observation we can make from this feat is that the token of the certificate user is carried on into the dynamic SQL, which, as I said previously, is a nameless stored procedure on its own. This may not seem particularly noteworthy at this point, but let's explore this a little more. Here is a not particularly meaningful stored procedure that displays the indexes on Playtable and Michelle tries to run it:
CREATE PROCEDURE show_playindexes AS EXEC sp_helpindex 'Playtable' go EXECUTE AS USER = 'Michelle' EXEC show_playindexes go REVERT
We don't really expect ownership chaining to work here, and indeed there is an error message:
Msg 15009, Level 16, State 1, Procedure sp_helpindex, Line 38
The object 'Playtable' does not exist in database 'Playground' or is invalid for this operation.
To see the indexes on a table, you need the permission VIEW DEFINITION on the table. As we already have a certificate for this purpose, we are lazy and reuse it whereupon Michelle makes a second attempt:
ADD SIGNATURE TO show_playindexes BY CERTIFICATE my_first_cert WITH PASSWORD = 'All You Need Is Love' go EXECUTE AS USER = 'Michelle' EXEC show_playindexes go REVERT
This time Michelle can successfully run the procedure. Thus, we can conclude that the token of the certificate user is carried on into the system procedure.
But what happens if we invoke user-defined objects? We create a stored procedure and a trigger that both return information from the tokeninfo view together with the name of the module:
CREATE PROCEDURE inner_sp AS SELECT 'inner_sp' AS wherearewe, * FROM tokeninfo go CREATE TRIGGER play_tri ON Playtable AFTER INSERT AS SELECT 'In trigger' AS wherearewe, * FROM tokeninfo
Next, we create an outer procedure that invokes these two and also displays the information in tokeninfo.
CREATE PROCEDURE outer_sp @id int, @somedata nvarchar(40) AS
SELECT 'outer_sp' AS wherearewe, token_name, type, usage
FROM tokeninfo
INSERT Playtable(id, somedata)
VALUES(@id, @somedata)
EXEC inner_sp
There is no need to sign outer_sp (or any of the other two) to add extra permissions. However, we want to know how far the scope of the certificate extends, and therefore we sign outer_sp with my_first_cert and then try it as Michelle:
ADD SIGNATURE TO outer_sp BY CERTIFICATE my_first_cert WITH PASSWORD = 'All You Need Is Love' go EXECUTE AS USER = 'Michelle' EXEC outer_sp 4, 'Testing the scope' go REVERT
This is the output (only including the columns from sys.user_token for brevity):
wherearewe token_name type usage
---------- -------------------- --------------------------- -------------
outer_sp Michelle SQL USER GRANT OR DENY
outer_sp public ROLE GRANT OR DENY
outer_sp Playrole ROLE GRANT OR DENY
outer_sp my_first_cert_user USER MAPPED TO CERTIFICATE GRANT OR DENY
wherearewe token_name type usage
---------- -------------------- --------------------------- -------------
In trigger Michelle SQL USER GRANT OR DENY
In trigger public ROLE GRANT OR DENY
In trigger Playrole ROLE GRANT OR DENY
wherearewe token_name type usage
---------- -------------------- --------------------------- -------------
inner_sp Michelle SQL USER GRANT OR DENY
inner_sp public ROLE GRANT OR DENY
inner_sp Playrole ROLE GRANT OR DENY
That is, we can see that the token of the certificate user is there when we enter outer_sp, but when we enter the trigger or the inner procedure, it has been removed!
At first this may seem inconsistent. The certificate is carried on into some scopes, but not into others. However, there is a point in this. While not very common, it could be the case that an inner procedure or a trigger is designed to explicitly check whether the user has a certain permission. If the token from the certificate user would be carried on from the caller, the inner procedure would be lured. (While this may seem far-fetched, I make use of this in the chapter Loading CLR Objects in the appendix.) If you want the powers of the certificate in the inner procedure as well, you need to sign that too. On the other hand, you cannot sign a batch of dynamic SQL, nor can you sign a system procedure, so in this case it is very practical that the certificate token is retained.
Before we go on, we drop the trigger:
DROP TRIGGER play_tri
There is one more thing we need to look at to get the full picture. We explicitly DENY Michelle access on the Playtable:
DENY SELECT, UPDATE, DELETE, INSERT ON Playtable TO Michelle
Michelle the tries to run both add_playdata to add a row and dynamic_playdata to find the new row:
EXECUTE AS USER = 'Michelle' go EXEC add_playdata 5, 'Crawled off to sleep in the bath' EXEC dynamic_playdata 'bath' go REVERT
This results in this output:
id somedata whodidit
---- ---------------------------------- ---------
5 Crawled off to sleep in the bath Michelle
Msg 229, Level 14, State 5, Line 131
The SELECT permission was denied on the object 'Playtable', database 'Playground', schema 'dbo'.
We can tell that add_playdata ran successfully, despite the DENY. As ownership chaining is in play, no permissions are checked, neither granted nor denied. However, dynamic_playdata now fails despite that it is signed by the certificate. Remember that the way certificate signing works is that the token of the certificate user is added to the other tokens and in this way contributes with permission on equal footing with the other tokens. Previously, the only permission in the mix was GRANT SELECT given to the certificate user. But now the situation has changed, as Michelle's token includes DENY SELECT on Playtable, and DENY always takes precedence over GRANT. Thus, you can never use certificate signing to overcome permission problems that are due to DENY (or membership in a role like db_denydatawriter).
Overall, DENY is something you should use avoid as much as possible, exactly because of this behaviour. And there should rarely be any need for DENY if you keep in mind that by default users have no permissions at all in the database.
At this point we revoke the DENY for Michelle, since we only added it for this example:
REVOKE SELECT, UPDATE, DELETE, INSERT ON Playtable FROM Michelle
Before we move on to the next section, let us summarise what we have learnt:
At this point I can sense readers who are thinking: Yeah, while this is nice and cosy, it has no practical usage, because it would be a nightmare to manage all these certificates, not talking about all the passwords. And having to re-sign the procedure after each change, what a pain! This is the section where we will address these concerns.
So far we have used two certificates, and we have used them in two different ways. One certificate, dynamic_playdata$cert, has a name which is derived from the procedure it signs, whereas we have used my_first_cert to sign three different procedures. This sort of indicates two main strategies: one certificate for every procedure that needs special powers, or a few general certificates that you use when needed.
In my opinion this is a simple choice. The latter method will indeed lead to a lot of work to keep track of certificates, passwords and what permissions they go along with. I only took the freedom to reuse my_first_cert a few times, because we had not yet learnt to automate the creation of certificates and signing of procedures. Tailoring a certificate for every procedure where you want to package extra permissions is a much better model, exactly because it is a lot easier to automate. In this model, the password is something you never have to see, but you can throw away. Why, you will see in an instant.
Here is the basic idea, expressed in T‑SQL, although this is nothing that compiles and runs – I use variables in a lot of places where it is not permitted. (As this code does not run, it is not included in the script for this chapter).
DECLARE @certname sysname = @spname + '$cert',
@username sysname = @spname + '$certuser',
@password char(37) = convert(char(36), newid()) + 'a'
DROP SIGNATURE IF EXISTS FROM @spname BY CERTIFICATE @certname
DROP USER IF EXISTS @username
DROP CERTIFICATE IF EXISTS @certname
CREATE CERTIFICATE @certname
ENCRYPTION BY PASSWORD = @password
WITH SUBJECT = '"GRANT SELECT ON Playtable"'
ADD SIGNATURE TO @spname BY CERTIFICATE @certname
WITH PASSWORD = @password
CREATE USER @username FROM CERTIFICATE @certname
GRANT SELECT ON Playtable TO @username
That is, we first generate names for the certificate and user from the name of the procedure. Next we drop any existing signature from the procedure, and then we drop the user and the certificate. Then we create a new certificate. It has the same name, but it is a new certificate, with different keys and a different thumbprint than the previous one. For the password we use a random GUID with a lowercase character added to be sure that we pass the complexity requirements in Windows. (Three out of the four categories uppercase, lowercase, digits, and punctuation). In the subject we put the permissions granted, so that we can easily see this when we run the query we have used earlier. Then we sign the procedure, create the user and grant the permissions. Since we create a new certificate every time, we only need the password twice: once to create the certificate and once to sign. Then we can forget it.
As noted, the above does not compile, but we need to use dynamic SQL to create the commands. I have packaged this in a stored procedure, GrantPermsToSP. You find the full code for this procedure in the file GrantPermsToSP.sql. As the procedure as such is more of an exercise in writing good dynamic SQL, I have not included the full code in the article, but here I will only present the interface and some examples. The procedure as posted runs on SQL 2012 and higher. You can easily also get it running on SQL 2008, if you comment out the COLLATE clause on line 164 in file. For SQL 2005, you would need to rearrange the procedure since it uses a table-valued parameter.
Since this is a management procedure and not part of any application, I have a separate schema for it:
CREATE SCHEMA Management
You may remember that I earlier I talked of how schemas can be security boundaries. Recall that we granted EXECUTE rights to Playrole on the dbo schema. Thus, by using this model, we put this procedure out of reach of the plain users.
To be able to specify multiple permissions, the procedure takes a table-valued parameter, and for this we need a table type:
CREATE TYPE Management.Permission_list AS
TABLE(perm nvarchar(400) PRIMARY KEY)
Here the signature of the procedure:
CREATE PROCEDURE Management.GrantPermsToSP
@spname nvarchar(520),
@permissions Management.Permission_list READONLY,
@debug bit = 1 AS
One procedure we looked at earlier was truncate_playdata which permits the user to empty the table, but since it uses TRUNCATE TABLE, Michelle was not able to run this procedure. This requires ALTER permission on the table. Let's now use GrantPermsToSP to arrange for that:
DECLARE @perms Management.Permission_list
INSERT @perms (perm) VALUES('ALTER ON Playtable')
EXEC Management.GrantPermsToSP 'truncate_playdata', @perms, @debug = 1
Since we run with the debug flag set, we see an output like this (the password will be different each time):
CREATE CERTIFICATE [truncate_playdata$cert]
ENCRYPTION BY PASSWORD = '46D92ECF-B2E8-4F7D-BD34-DF5A60B6DDE9a'
WITH SUBJECT = '"GRANT ALTER ON Playtable"'
ADD SIGNATURE TO [dbo].[truncate_playdata]
BY CERTIFICATE [truncate_playdata$cert]
WITH PASSWORD = '46D92ECF-B2E8-4F7D-BD34-DF5A60B6DDE9a'
CREATE USER [truncate_playdata$certuser]
FROM CERTIFICATE [truncate_playdata$cert]
GRANT ALTER ON Playtable TO [truncate_playdata$certuser]
That is, this is what I suggested above, except that are no DROP statements, as the procedure was not signed previously. If you run the same thing a second time, the DROP statements appear and the password is different. (In passing: this is useful, if you find that the permissions you tried in your first attempt were not the best ones.)
DROP SIGNATURE FROM [dbo].[truncate_playdata] BY CERTIFICATE [truncate_playdata$cert]
DROP USER [truncate_playdata$certuser]
DROP CERTIFICATE [truncate_playdata$cert]
CREATE CERTIFICATE [truncate_playdata$cert]
ENCRYPTION BY PASSWORD = '476AED96-FB50-4660-8771-E63EE88F98E5a'
WITH SUBJECT = '"GRANT ALTER ON Playtable"'
ADD SIGNATURE TO [dbo].[truncate_playdata]
BY CERTIFICATE [truncate_playdata$cert]
WITH PASSWORD = '476AED96-FB50-4660-8771-E63EE88F98E5a'
CREATE USER [truncate_playdata$certuser]
FROM CERTIFICATE [truncate_playdata$cert]
GRANT ALTER ON Playtable TO [truncate_playdata$certuser]
To prove the concept, Michelle gives it a whirl:
EXECUTE AS USER = 'Michelle' EXEC truncate_playdata go REVERT SELECT * FROM Playtable
This completes without error, and we can see that the table is now empty.
Here are some more details on how the procedure works. Say that you find that a procedure was signed in error, and no permissions should be granted to it. In this case, you simply call the procedure without passing the @permissions parameter. (Recall that a table-valued parameter always has an implicit default value of an empty table.)
EXEC Management.GrantPermsToSP 'truncate_playdata', @debug = 1
This is the output:
DROP SIGNATURE FROM [dbo].[truncate_playdata] BY CERTIFICATE [truncate_playdata$cert] DROP USER [truncate_playdata$certuser] DROP CERTIFICATE [truncate_playdata$cert] Procedure not signed - no permissions given
Signature, user and certificate are dropped, and there is a message to inform you that the procedure is not signed this time.
If the procedure is signed by other certificates of which the names are not derived from the procedure, these are not touched. You may recall that show_playindexes is already signed by my_first_cert. Here we add some permissions that serve no other purposes than to test the signing procedure:
DECLARE @perms Management.Permission_list
INSERT @perms (perm)
VALUES('INSERT, SELECT, DELETE, UPDATE ON Playtable'),
('CREATE PROCEDURE'),
('IMPERSONATE ON USER::Michelle')
EXEC Management.GrantPermsToSP 'dbo.show_playindexes', @perms, @debug = 1
The output is (with one line broken for legibility):
CREATE CERTIFICATE [show_playindexes$cert]
ENCRYPTION BY PASSWORD = '770260F7-CE1B-466E-943C-1F8BEE7485E2a'
WITH SUBJECT = '"GRANT CREATE PROCEDURE - IMPERSONATE ON USER::Michelle -
INSERT SELECT DELETE UPDATE ON Playtable"'
ADD SIGNATURE TO [dbo].[show_playindexes]
BY CERTIFICATE [show_playindexes$cert]
WITH PASSWORD = '770260F7-CE1B-466E-943C-1F8BEE7485E2a'
CREATE USER [show_playindexes$certuser]
FROM CERTIFICATE [show_playindexes$cert]
GRANT CREATE PROCEDURE TO [show_playindexes$certuser]
GRANT IMPERSONATE ON USER::Michelle TO [show_playindexes$certuser]
GRANT INSERT, SELECT, DELETE, UPDATE ON Playtable TO [show_playindexes$certuser]
As you use can see, my_first_cert does not appear in the output and is thus unaffected by the operation. You can also see how multiple permissions are handled in the subject. They are separated by dashes. (The line break is not there in the subject, but only to fit the article format.)
The name of the certificate and the user is constructed from the procedure name alone when the procedure is in the dbo schema, but if the procedure is in a different other schema, the schema name is included as well. For instance, if you have a procedure my_sp in the schema myschema, the certificate would be named myschema.my_sp$cert. (Note that the dot here is part of the name; certificates and users are not bound to schemas themselves.)
How you would actually use GrantPermsToSP depends a little on your circumstances. If you are in an environment where your group develops an application which is under version control, it seems natural to me that the call to GrantPermsToSP would be in the deployment script for the stored procedures that need extra permissions. Some tools may be a little squared and don't agree with this, and in such case you may have to put these calls in a some post-deployment script. (Readers who use SSDT may hope for advice, but since I've only played with SSDT and never used it for real, I will have to pass on that one.) The key property from a security perspective is that in this type of environment, developers are generally trusted to hand out permissions that should be in the application, even if they do not have permission on the production database themselves.
Other environments may be laxer when it comes to version control, but are more strict on security. For instance, there may be users who have permission to write stored procedures in one schema (which is not owned by dbo). They have read and write access to the dbo schema, but they have no permissions to change tables (which are in dbo), add users etc. In this case, they will not be able to run GrantPermsToSP, and nor do you want them to. You want to review their code, before you agree to add elevated permissions to their procedures. In this type of scenario, the fact that the signature disappears after every change is extremely important. When you have a signed a procedure, the developers cannot go back alter the procedure to do something you would not agree to. Every time a developer makes a change to a privileged procedure, you can request to review the code anew before you sign.
Note: Readers who are cognizant about SQL injection and read the code to GrantPermsToSP will note that the @permissions parameter is wide-open to SQL injection. However, I don't see this as a problem here. SQL injection is only a risk if it permits a user to do something the user is not able to do his own. A typical example is a web application which connects with a high-privilege account and the actual user does not even have access of its own to SQL Server. Here SQL injection is a powerful attack vector. But GrantPermsToSP is intended to be executed by users with admin permissions, and GrantPermsToSP does not add any extra permissions the user does not have already. (Well, if you sign GrantPermsToSP itself to package permissions it will, but that seems like a bad idea to me.)
This section contains some supplementary information that is not essential for understanding of the technique. Feel free to skip it, if you like.
If you create a database master key and have it open when you create your certificate, you don't need to specify a password when you create the certificate, as the database master key protects the private key of the certificate. For the same reason, you don't need any password when you sign the procedure.
While this makes things a little simpler, I have been reluctant to use this. Given that the password can be handled entirely inside GrantPermsToSP, the password is not a big deal in the first place. I will have to admit that I'm foggy on when the database master key is open and when it is not, but if you copy the database to another server it may not be open, unless the two instances have the same service master key. (The service master key protects the database master key.) Thus, there is a risk is that you find yourself confused when something that you are used to work, all of a sudden does not.
Instead of certificates, you can use asymmetric keys. This is simpler so far that you don't have to specify a subject, and if you also have a database master key, you only need to say
CREATE ASYMMETRIC KEY key1 WITH ALGORITHM = RSA_2048
While you don't have to specify subject or password, you must specify an algorithm.
In this article, I have opted to work with certificates only, despite the small hassle with the subject. The reason is that certificates can (relatively) easily be exported to other databases, while this is not all simple with asymmetric keys. This is something we need to do when signing procedures for server-level access, which we will look into in the next chapter.
When you sign a procedure, you can insert the keyword COUNTER:
ADD COUNTER SIGNATURE TO ...
When you countersign a procedure, the signature has no effect if the procedure is called directly. However, if the procedure is called by an outer procedure which is signed with the same certificate, the token of the certificate user is not removed when the countersigned procedure is entered, but lingers on.
This is sort of a feature that looks for a problem to solve, but imagine this: you have a search procedure that uses dynamic SQL. However, the search procedure itself is too general and exposes too much data. Instead users should call an outer procedure which adds extra filters depending on what the user has permission to see. In this case you can sign the outer procedure with a certificate of which the user has been granted SELECT permission on the tables in question, whereas you only countersign the inner procedure.
In the appendix, the chapter Letting Users Start Specific Jobs includes a solution that uses countersignatures.
There is a form of ALTER SIGNATURE that permits you to add a signature that you have computed previously:
ADD SIGNATURE TO some_sp BY CERTIFICATE some_cert WITH SIGNATURE = 0x.....
where the binary value that follows WITH SIGNATURE is the signature itself. In this case, only the public key of the certificate needs to be present in the database. There are scenarios where this can be useful to sign procedures in order to package permissions in stored procedures, but they will have to count as advanced. I will eventually present an example, but that will not be until the final chapter on cross-database access.
Straying beyond the main topic for this article, there is another situation where this is useful that is worth a brief discussion. Say that you develop an application which you ship to customers and you want to be able to detect whether the customers make changes to the code or the tables you ship to them. In your database at home, you sign all your procedures (and everything else you can sign and want to detect tampering of). For this purpose, you would use the same certificate for all objects. When you build the installation kit, you only include the public key of the certificate (you will learn later how you can extract a certificate from the database), and you get the signatures from sys.crypt_properties. If the customer changes an object, the signature disappears. They can drop your certificate and create a new one with the same name and sign the procedure. But when you make an audit, you would find that their public key does not match your private key.
Note that if you also use certificate signing to package permissions, you could still use GrantPermsToSP in your installation kit. The certificate you use to detect tampering should not be mixed with certificates you use to package permissions.
You cannot sign DDL triggers, be that on database or server level. There is simply a disconnect in the syntax – there is no module class that matches DDL triggers. This is not a major obstacle. You can pass the contents from eventdata() to a stored procedure which you have signed. For a server-level trigger there is another alternative that we will look at in the section Server-level Objects later. Nevertheless, if you feel that this is a shortcoming that requires rectification, there is a feedback item from Solomon Rutzky you can vote for.
We will now look at how we can package server-level permissions in stored procedures with help of certificate signing. We will first learn how to do this for procedures stored in the master database; this is not too different from how things work on database level. We then move on to look at what is needed for stored procedures in user databases, something that requires more steps and considerations. As with using certificates on database level, I will offer a way to automate the process.
The script for this chapter is 05_certsignserver.sql. I would like to remind you that you should be careful not to work on any important server, since we will create logins and other objects on server level in this chapter.
Before we look at the actual technique, let me first introduce a scenario that we will work with. Assume that you are a DBA on a server with many databases that generally should not know about each other. Each database have their own set of power users who typically have no server-level permissions.
The power users have a need to see information in DMVs related to their database. For instance, they may need to see which users that are connected to their database. The query for this purpose is a simple one:
SELECT * FROM sys.dm_exec_sessions WHERE database_id = db_id()
The crux is that to see other sessions than your own in sys.dm_exec_sessions, you need the server-level permission VIEW SERVER STATE. But if you would grant the power users that permission, they would be able to see sessions in all databases and lot more things they are not entitled to. In this chapter, we will learn how we can package this permission in a stored procedure, so that the power user can see what they need to see, but no more.
Note: In SQL 2022, two new "child" permissions to VIEW SERVER STATE, and of these VIEW SERVER PERFORMANCE STATE is sufficient for the access to sys.dm_exec_sessions. However, as some readers may be running the sample scripts on earlier versions of SQL Server, I have opted to keep VIEW SERVER STATE in the text and in the deno scripts for the time being.
Before we can start writing stored procedures, we need a setup. Here is a script that creates the server role PowerUsers and a login for a certain Sgt Pepper, the power user of the Playground database. We add Sgt Pepper to PowerUsers, and then we move over to Playground where we make him member of the db_owner role. (Note: server roles were introduced in SQL 2012 and are not available in SQL 2008.) While we are at it, we also create a view logintokeninfo in the master and Playground databases for diagnostic purposes. It is akin to the tokeninfo view we looked at earlier, but logintokeninfo displays tokens on server level taken from sys.login_token.
SET XACT_ABORT, NOCOUNT ON
USE master
go
CREATE SERVER ROLE PowerUsers
CREATE LOGIN SgtPepper WITH PASSWORD = 'Lonely Hearts Club Band'
ALTER SERVER ROLE PowerUsers ADD MEMBER SgtPepper
go
CREATE VIEW logintokeninfo AS
SELECT name AS token_name, type, usage,
original_login() AS original_login,
SYSTEM_USER AS [SYSTEM_USER], USER AS DBUSER
FROM sys.login_token
go
USE Playground
go
CREATE USER SgtPepper
ALTER ROLE db_owner ADD MEMBER SgtPepper
go
CREATE VIEW logintokeninfo AS
SELECT name AS token_name, type, usage,
original_login() AS original_login,
SYSTEM_USER AS [SYSTEM_USER], USER AS DBUSER
FROM sys.login_token
We will first look at solving the problem for the power users with help of a stored procedure in the master database which makes use of an undocumented feature in SQL Server:
USE master
go
CREATE PROCEDURE sp_ShowSessions AS
SELECT * FROM logintokeninfo
IF (is_srvrolemember('PowerUsers') = 1 AND is_member('db_owner') = 1) OR
has_perms_by_name(NULL, NULL, 'CONTROL SERVER') = 1
SELECT * FROM sys.dm_exec_sessions WHERE database_id = db_id()
ELSE
RAISERROR('You don''t have permission to run this procedure!', 16, 1)
Because the procedure resides in master and the name starts with sp_, the procedure can be accessed from any database but it will execute in the context of the database it was invoked from. Thus, this is what db_id() will return, and the procedure will only display users connected to the current database. As I said, this is undocumented and thus unsupported, and it could break in any release. Particularly, Microsoft could ship their own sp_ShowSessions with the next version of SQL Server, which would trump what we have put in master. Use at your own risk.
The procedure starts off with returning the information from logintokeninfo, which is purely for diagnostic purposes. It then goes on to check whether the user is entitled to see the information. The user must be a member of the server role PowerUsers and also be db_owner in the current database. This latter check is needed in case the user would be a plain user in some other database where he has no right to see connected users. The procedure also permits a user who has the permission CONTROL SERVER to run the query, so that sysadmin can run the procedure. (Why check for CONTROL SERVER and not VIEW SERVER STATE? Let's talk about that later.) If none of these conditions are fulfilled, we raise an error.
You may find it funny that we do the permission check inside the procedure. Why not simply only grant access to PowerUsers to run this procedure? The problem is that PowerUsers is a server principal, but EXECUTE permission is something you grant to database principals. And what database principals are there in master? Yes, you could mirror PowerUsers with a database role in master, but you would also have to add all the power people as users in master, and who wants users in master? Not me. So, there is really only one option: public.
GRANT EXECUTE ON sp_ShowSessions TO public
And since everyone can invoke the procedure, the procedure must perform its own permission check.
Let us now test the procedure, both as ourselves and as Sgt Pepper. Note that since we are making server-level access, we must use EXECUTE AS LOGIN to impersonate Sgt Pepper, as EXECUTE AS USER works inside the database only.
USE Playground go EXEC sp_ShowSessions EXECUTE AS LOGIN = 'SgtPepper' EXEC sp_ShowSessions go REVERT
When I run this, I get an output akin to the below. For space reasons, I have deleted the SYSTEM_USER column from the first result set, and I only show the first three columns from sys.dm_exec_sessions:
token_name type usage original_login DBUSER
--------------------- ---------------- --------------- ------------------ ------
LIVERPOOL\GMartin WINDOWS LOGIN GRANT OR DENY LIVERPOOL\GMartin dbo
public SERVER ROLE GRANT OR DENY LIVERPOOL\GMartin dbo
sysadmin SERVER ROLE GRANT OR DENY LIVERPOOL\GMartin dbo
...
session_id login_time host_name
---------- ----------------------- -----------
51 2017-07-27 21:42:16.740 LIVERPOOL
52 2017-07-23 21:13:38.073 LIVERPOOL
53 2017-07-24 21:56:57.000 LIVERPOOL
token_name type usage original_login SYSTEM_USER DBUSER
----------- ------------ -------------- ------------------ ------------ ------
SgtPepper SQL LOGIN GRANT OR DENY LIVERPOOL\GMartin SgtPepper guest
public SERVER ROLE GRANT OR DENY LIVERPOOL\GMartin SgtPepper guest
PowerUsers SERVER ROLE GRANT OR DENY LIVERPOOL\GMartin SgtPepper guest
session_id login_time host_name
---------- ----------------------- -----------
51 2017-07-27 21:42:16.740 LIVERPOOL
I have abbreviated the first result set – when logged as sysadmin through Windows authentication GMartin have no less than 17 login tokens. On the particular occasion, GMartin had three query windows open to the Playground database. When running the procedure as Sgt Pepper, you can see three tokens: SgtPepper for the login itself and then there are the two server roles public and PowerUsers. The column original_login reveals that GMartin was the person who actually had logged in, while SYSTEM_USER returns SgtPepper since we are impersonating him. Somewhat unexpectedly, DBUSER reports guest and not SgtPepper. Apparently the USER function is resolved against the master database where the stored procedure resides. (As I said, we are using an undocumented and unsupported feature.) Because Sgt Pepper has no server-level permission, the query against sys.dm_excec_sessions returns a single row for the current session.
The recipe to package server-level permissions in a stored procedure in master is very similar to what we used for database permissions:
That is, rather than creating a database user, we need to create a server login to be able to tie the certificate to the permission, since we are working with server permissions. And while it may sound scary to create extra server logins, have no fear. Just like the certificate user, the certificate login is nothing that actually can log in or execute.
Here is a script to perform the four steps of the recipe. I have added a SELECT from sys.server_principals, so that you can verify that this is not a regular login (check the column type_desc). Observe that we need to move back to master:
USE master
go
CREATE CERTIFICATE spShowSessions$cert
ENCRYPTION BY PASSWORD = 'Magical Mystery Tour'
WITH SUBJECT = '"GRANT VIEW SERVER STATE"'
go
ADD SIGNATURE TO sp_ShowSessions BY CERTIFICATE spShowSessions$cert
WITH PASSWORD = 'Magical Mystery Tour'
go
CREATE LOGIN spShowSessions$certlogin FROM CERTIFICATE spShowSessions$cert
SELECT * FROM sys.server_principals WHERE name = 'spShowSessions$certlogin'
go
GRANT VIEW SERVER STATE TO spShowSessions$certlogin
Sgt Pepper now makes a second attempt to run sp_ShowSessions:
USE Playground go EXECUTE AS LOGIN = 'SgtPepper' EXEC sp_ShowSessions go REVERT
I got this output (for space reasons, I'm only including the first three columns of each result set):
token_name type usage
----------------------- --------------------------- --------------
SgtPepper SQL LOGIN GRANT OR DENY
public SERVER ROLE GRANT OR DENY
PowerUsers SERVER ROLE GRANT OR DENY
spShowSessions$certlogin LOGIN MAPPED TO CERTIFICATE GRANT OR DENY
spShowSessions$cert CERTIFICATE GRANT OR DENY
session_id login_time host_name
---------- ----------------------- -----------
51 2017-07-27 21:42:16.740 LIVERPOOL
52 2017-07-23 21:13:38.073 LIVERPOOL
53 2017-07-24 21:56:57.000 LIVERPOOL
In the lower result set, Sgt Pepper now sees the same three sessions as we did when we ran the procedure as ourselves. If we turn to the upper result set, we see that there are now five login tokens. The certificate login is there, and this is the token that contributes with the permission VIEW SERVER STATE. And this explains why we check for the permission CONTROL SERVER in the procedure. Would we check for VIEW SERVER STATE, has_perms_by_name would always return 1, since this permission is present through the certificate.
For reasons unknown to me, the certificate itself appears among the tokens, which is different from what we saw on database level. I don't think there is any practical implication of this.
We had to open a case with Microsoft about something, and they found out about our sp_ShowSessions and they told us in no uncertain terms that it is undocumented and unsupported, so we decided to play by the rules and instead deploy a procedure ShowSessions in every database:
USE Playground
go
CREATE PROCEDURE Management.ShowSessions AS
SELECT * FROM logintokeninfo
IF (is_srvrolemember('PowerUsers') = 1 AND is_member('db_owner') = 1) OR
has_perms_by_name(NULL, NULL, 'CONTROL SERVER') = 1
SELECT * FROM sys.dm_exec_sessions WHERE database_id = db_id()
ELSE
RAISERROR('You don''t have permission to run this procedure!', 16, 1)
We kept the check on that the user is member of PowerUsers and db_owner, because we still want to control who can run the procedure. We deploy it to the Management schema, so that plain users in Playrole will not have EXECUTE permission on the procedure.
If you want to package server-level permissions in a stored procedure (or some other module) in a user database, there are two more steps in the recipe, because the certificate must be both in master (as this is where the certificate login is defined and the permissions are granted) and in the user database (so that we can sign the procedure). These are the six steps:
Let's perform the first two steps. The only thing that is new here is that I include the name of the database in the certifcate name so that once the certificate has been copied to server, we can tell which database it relates to.
USE Playground go CREATE CERTIFICATE PlaygroundShowSessions$cert ENCRYPTION BY PASSWORD = 'And Your Bird Can Sing' WITH SUBJECT = '"GRANT VIEW SERVER STATE"' ADD SIGNATURE TO Management.ShowSessions BY CERTIFICATE PlaygroundShowSessions$cert WITH PASSWORD = 'And Your Bird Can Sing'
The next step is a new step. Once the procedure has been signed, there is no need to keep the private key, since only the public key is needed to validate the signature. Would it be that we want to re-sign the procedure, we can simply drop the signature and the certificate and create a new certificate. I did not introduce this step when I discussed database-level permissions, because in that scenario it is not equally compelling as it is here. Sgt Pepper might have been looking over our shoulders as we typed the password. Thus, would we leave the private key in the database, he could exploit it and sign procedures entirely according to his own liking. This is nothing we can permit. Whence we run this command:
ALTER CERTIFICATE PlaygroundShowSessions$cert REMOVE PRIVATE KEY
Next step is to copy the certificate to master. This is a two-step operation, as there is no direct COPY CERTIFICATE command. You need to retrieve the public key with the function certencoded, switch to the master database, and then import it into master with the command CREATE CERTIFICATE using the clause FROM BINARY as illustrated in this snippet:
USE Playground
go
DECLARE @public_key varbinary(MAX) =
certencoded(cert_id('PlaygroundShowSessions$cert'))
USE master
CREATE CERTIFICATE PlaygroundShowSessions$cert
FROM BINARY = @public_key
certencoded takes the id of the certificate as input parameter, and we can retrieve this id with the function cert_id.
There is however a slight problem with the above as testified if you try to run it:
Msg 102, Level 15, State 1, Line 106
Incorrect syntax near '@public_key'.
You see, CREATE CERTIFICATE FROM BINARY does not accept a variable for the key – it must be given as a binary literal. (Why? The only good reason I can think of is that they were short on time, and never came around to implement variable support.) Thus, the above must be modified to use dynamic SQL:
USE Playground
go
DECLARE @public_key varbinary(MAX) =
certencoded(cert_id('PlaygroundShowSessions$cert')),
@sql nvarchar(MAX)
SELECT @sql =
'CREATE CERTIFICATE PlaygroundShowSessions$cert
FROM BINARY = ' + convert(varchar(MAX), @public_key, 1)
USE master
PRINT @sql
EXEC(@sql)
When you run the above, you see an output like this:
CREATE CERTIFICATE PlaygroundShowSessions$cert
FROM BINARY = 0x308202CC308201B4A00302010202...
Finally we run the last two steps, that is creating the login and granting the permissions:
CREATE LOGIN PlaygroundShowSessions$certlogin
FROM CERTIFICATE PlaygroundShowSessions$cert
GRANT VIEW SERVER STATE TO PlaygroundShowSessions$certlogin
Now it is time for Sgt Pepper to give it a go:
USE Playground go EXECUTE AS LOGIN = 'SgtPepper' EXEC Management.ShowSessions go REVERT
The output is the same as in the end of the previous section. That is, there are five login tokens, and the Sergeant sees all sessions connected to Playground.
CREATE CERTIFICATE FROM BINARY was introduced in SQL 2012. If you still have to work on SQL 2008 or SQL 2005, there is a second method to copy the certificate by using the commands BACKUP CERTIFICATE and CREATE CERTIFICATE FROM FILE. If you want to try this method, drop the login and the certificate in master so you can start over:
USE master go DROP LOGIN PlaygroundShowSessions$certlogin DROP CERTIFICATE PlaygroundShowSessions$cert
Verify that Sgt Pepper is no longer able to see all sessions:
USE Playground go EXECUTE AS LOGIN = 'SgtPepper' EXEC Management.ShowSessions go REVERT
(You may note that the certificate is still present in the output from logintokeninfo, since the procedure is still signed, but since there is no login and no permissions granted anymore, it doesn't help.)
Here are the commands to copy the certificate with the alternate method:
USE Playground go BACKUP CERTIFICATE PlaygroundShowSessions$cert TO FILE = 'C:\temp\certexport.cer' go USE master go CREATE CERTIFICATE PlaygroundShowSessions$cert FROM FILE = 'C:\temp\certexport.cer' EXEC xp_cmdshell 'DEL C:\temp\certexport.cer'
As you can see, I attempt to delete the file with the certificate through xp_cmdshell. This requires that that xp_cmdshell is enabled. This is dubious, as best practice is to keep xp_cmdshell disabled. However, you should somehow delete the file to avoid litter on the disk. Also, if you rerun BACKUP CERTIFICATE with the same file name and the file exists, the command will fail.
Again create the login and grant permissions:
CREATE LOGIN PlaygroundShowSessions$certlogin
FROM CERTIFICATE PlaygroundShowSessions$cert
GRANT VIEW SERVER STATE TO PlaygroundShowSessions$certlogin
And verify that Sgt Pepper can run ShowSessions:
USE Playground go EXECUTE AS LOGIN = 'SgtPepper' EXEC Management.ShowSessions go REVERT
We have now seen the mechanism, and we have seen that there are quite a few steps. Even more when we worked with database permissions, we yearn for a way to automate the process. For this reason, I have composed a script which you find in the file GrantPermsToSP_server.sql. Why is this a script and not a stored procedure? My thinking is that if you are a DBA, you may need to run this script on ServerA on Monday, ServerB on Wednesday and so on. So it seems better to have a script that you have on your disk which you can improve over time, rather than having copies of a stored procedure on umpteen servers.
Note: This script requires SQL 2012 and higher. I also have a script for SQL 2005 and SQL 2008 that I discuss at the end of this section.
As you might guess, this script is even longer than GrantPermsToSP, and here I only show the opening parameter part and discuss the output. The main body of the script is a good exercise of dynamic SQL, but that is not the topic for this article. The script itself is well commented.
The script starts up this way (leaving out one control parameter that I will return to in the next section):
USE master
go
-- Set up parameters: the procedure to sign and the database it belongs to.
DECLARE @database nvarchar(260) = 'Playground',
@procname nvarchar(520) = 'Management.ShowSessions'
-- The permissions to grant through the certificate. Leave table empty
-- to only remove current permissions.
DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
INSERT @perms VALUES
('VIEW SERVER STATE')
-- Run with debug or not?
DECLARE @debug bit = 1
--============================ END OF SETUP ==========================
When you are to sign a procedure, you only need to edit the part before the line with the equal signs. You would only meddle in the part below this line, if there is something you want to improve in the process. The input is the same as for GrantPermsToSP, with the database as an additional input parameter. You specify the procedure name, and you can leave out dbo for procedures in this schema. You fill a table variable with the permissions you want to grant. You can also enter server roles and the script will figure that out and add the certificate login as a member in the role you specify. If you leave @perms empty, the script will only remove existing signatures, logins and certificates. Finally, there is a @debug flag which defaults to 1.
Before we try to run the script, here is a summary of what the script does. These are the steps:
The subject for the certificate is formed in the same way as in GrantPermsToSP, that is, it starts with GRANT and a summary of the permissions (or roles). Likewise, the password is a GUID. The name of the certificate and the login are formed in a different way, though. The name of both start with SIGN followed by a space and then the three-part name for the procedure, with all components in brackets. The name of the login and the certificate are the same; I have not bothered with a separate name for the login. Thus, the above results in a certificate as well as a login by the name SIGN [Playground].[Management].[ShowSessions].
So why are the naming schemes different?
If you want a different naming scheme, feel free to change.
In the debug output, the statements are prepended with a comment which states in which database the command is executed.
Before you go on and test the script, alter the procedure to remove the existing signature and verify that Sgt Pepper is now only able to see his own session:
USE Playground
go
ALTER PROCEDURE Management.ShowSessions AS
SELECT * FROM logintokeninfo
IF (is_srvrolemember('PowerUsers') = 1 AND is_member('db_owner') = 1) OR
has_perms_by_name(NULL, NULL, 'CONTROL SERVER') = 1
SELECT * FROM sys.dm_exec_sessions WHERE database_id = db_id()
ELSE
RAISERROR('You don''t have permission to run this procedure!', 16, 1)
go
EXECUTE AS LOGIN = 'SgtPepper'
EXEC Management.ShowSessions
go
REVERT
Now you can test running the full script GrantPermsToSP_server.sql. When you have done this, try running ShowSessions as Sgt Pepper once more to verify that the certificate login is added to the tokens and that Sgt Pepper can see all processes connected to Playground.
Here is the output I get when I run the script (with the blob for the certificate abbreviated and some lines broken to fit within the page width):
(1 row affected)
-- In database [Playground]
EXECUTE AS USER = 'dbo'
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)
REVERT
-- In master
DROP LOGIN "SIGN [Playground].[Management].[ShowSessions]"
-- In master
DROP CERTIFICATE "SIGN [Playground].[Management].[ShowSessions]"
-- In database [Playground]
EXECUTE AS USER = 'dbo'
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 [Management].[ShowSessions]
BY CERTIFICATE "SIGN [Playground].[Management].[ShowSessions]"
REVERT
-- In database [Playground]
EXECUTE AS USER = 'dbo'
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
REVERT
-- In database [Playground]
EXECUTE AS USER = 'dbo'
IF EXISTS (SELECT * FROM sys.certificates WHERE name = @certname)
DROP CERTIFICATE "SIGN [Playground].[Management].[ShowSessions]"
REVERT
-- In database [Playground]
EXECUTE AS USER = 'dbo'
CREATE CERTIFICATE "SIGN [Playground].[Management].[ShowSessions]"
ENCRYPTION BY PASSWORD = 'C9B1CE88-3C07-421C-8278-B0B44933A1A4Aa0'
WITH SUBJECT = '"GRANT VIEW SERVER STATE"'
REVERT
-- In database [Playground]
EXECUTE AS USER = 'dbo'
ADD SIGNATURE TO [Management].[ShowSessions]
BY CERTIFICATE "SIGN [Playground].[Management].[ShowSessions]"
WITH PASSWORD = 'C9B1CE88-3C07-421C-8278-B0B44933A1A4Aa0'
REVERT
-- In database [Playground]
EXECUTE AS USER = 'dbo'
ALTER CERTIFICATE "SIGN [Playground].[Management].[ShowSessions]" REMOVE PRIVATE KEY
REVERT
-- In database [Playground]
EXECUTE AS USER = 'dbo'
SELECT @pvt = pvt_key_encryption_type FROM sys.certificates WHERE name = @certname
REVERT
-- In database [Playground]
EXECUTE AS USER = 'dbo'
SELECT @public_key = convert(varchar(MAX),
certencoded(cert_id(quotename(@certname))), 1)
REVERT
-- In master
CREATE CERTIFICATE "SIGN [Playground].[Management].[ShowSessions]"
FROM BINARY = 0x308202CC308201B4A0030201...
-- In master
CREATE LOGIN "SIGN [Playground].[Management].[ShowSessions]"
FROM CERTIFICATE "SIGN [Playground].[Management].[ShowSessions]"
-- In master
GRANT VIEW SERVER STATE TO "SIGN [Playground].[Management].[ShowSessions]"
Some notes:
There is a risk that when you run this script that you encounter this error:
Msg 15517, Level 16, State 1, Line 44
Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.
This typically happens when you have restored the database from another server, and owner of the database is not present on the new server, so that sys.databases.owner_sid on server level does not match sys.database_principals.sid for the user dbo inside the database. The remedy is to correct the database owner with ALTER AUTHORIZATION to the desired owner. (If the owner is an SQL login of which the name exists on both servers, the problem is that the SID is typically different on the two servers, unless you created the logins with an explicit SID using CREATE LOGIN WITH SID.)
The code above only executes on SQL 2012 and later. If you are unfortunate to be on SQL 2008 or SQL 2005, you can use GrantPermsToSP_server_2008.sql which uses BACKUP CERTIFICATE and CREATE CERTIFICATE FROM FILE instead. The script writes the certificate file to the folder where the master database is located. If xp_cmdshell is enabled, the script deletes the file, else it prints a warning. The filename is a GUID, so there will not be any collisions if you don't delete the file, only littering. (If you look into the generated commands, you will find that I use the options START_DATE and EXPIRY_DATE that I don't discuss in this article. This is to avoid an irritating warning on SQL 2005.)
If the database is part of an availability group (AG), what we have done so far is not enough. The certificate must be copied to all servers in the AG, and on each server a login must be created for the certificate and that login needs to be granted the permissions required. If we don't do this, the procedure will stop working after a failover. Before you draw a deep sigh: don't worry, GrantPermsToSP_server.sql can do this for you. As I mentioned, the script has one more control variable that I left out:
-- How to handle Availability groups. That is, copy cert, login and perms -- across all nodes in the AG? Set explicitly or rely on default. DECLARE @copy_across_AG bit = NULL
In the intial paragraph, I tacitly assumed that you have a "classic" non-contained AG. SQL 2022 introduced contained availability groups, where the contained AG has its own master database which travels with the AG in case of a failover, and thus, the need for copying across the AG is evaded.
For this reason, the default for @copy_across_AG depends on the situation:
If you set @copy_across_AG to 1 (or it defaults to 1), the script loops over the servers in the AG and sets up a temporary linked server called TEMP$SERVER for each server and drops the linked server once that server has been processed. On each linked server, the script performs these actions:
Here is the output I got when running the script in an AG with three nodes. When I ran it, AGNODE1 was the current primary. I only show the output for the other two nodes (as the execution on AGNODE1 is the same as above). As previously, blobs are abbreviated, and some lines broken into several:
-- On server AGNODE2
IF EXISTS (SELECT *
FROM sys.server_principals
WHERE name = @certname
AND type = 'C')
DROP LOGIN "SIGN [Playground].[Management].[ShowSessions]"
-- On server AGNODE2
IF EXISTS (SELECT * FROM sys.certificates WHERE name = @certname)
DROP CERTIFICATE "SIGN [Playground].[Management].[ShowSessions]"
-- On server AGNODE2
CREATE CERTIFICATE "SIGN [Playground].[Management].[ShowSessions]"
FROM BINARY = 0x308201C730820130A0030...
-- On server AGNODE2
CREATE LOGIN "SIGN [Playground].[Management].[ShowSessions]"
FROM CERTIFICATE "SIGN [Playground].[Management].[ShowSessions]"
-- On server AGNODE2
GRANT VIEW SERVER STATE TO "SIGN [Playground].[Management].[ShowSessions]"
-- On server AGNODE3
IF EXISTS (SELECT *
FROM sys.server_principals
WHERE name = @certname
AND type = 'C')
DROP LOGIN "SIGN [Playground].[Management].[ShowSessions]"
-- On server AGNODE3
IF EXISTS (SELECT * FROM sys.certificates WHERE name = @certname)
DROP CERTIFICATE "SIGN [Playground].[Management].[ShowSessions]"
-- On server AGNODE3
CREATE CERTIFICATE "SIGN [Playground].[Management].[ShowSessions]"
FROM BINARY = 0x308201C730820130A0030...
-- On server AGNODE3
CREATE LOGIN "SIGN [Playground].[Management].[ShowSessions]"
FROM CERTIFICATE "SIGN [Playground].[Management].[ShowSessions]"
-- On server AGNODE3
GRANT VIEW SERVER STATE TO "SIGN [Playground].[Management].[ShowSessions]"
Keep in mind that as always with linked servers, there can be hassle. Specifically, if you run GrantPermsToSP_server.sql from SSMS on your personal machine, the SQL Server instance you connect to may not be trusted for Kerberos delegation and you will face the dreaded double-hop error. If this happens to you, or you know that it will happen to you, you can set @copy_across_AG to 0 and deal with the copying manually. (It would probably be a better approach to handle the AG from a PowerShell script that connects to each server individually. But that is left as an exercise to the reader.)
Before we close this chapter and move on, let's assess some potential security risks with what have learnt this far. After all, this is an article on security.
Note: While this section includes a few SQL snippets, they are not included in the script for this chapter, as this section is more focused on discussion than showing actual code. You can still try the snippets, but you will need to clean up after yourself when you are done.
Let's first identify two main scenarios with regards to duties, are they separated or not?
In the unseparated scenario, there are far less threats than in the separated scenario. The only thing you need to consider is whether a malicious user could exploit your code in a way you did not intend. To a large extent this means: Make sure that you don't have any SQL injection holes. Always use parameters where the syntax allows this, use quotename to delimit object names etc. See my article The Curse and Blessings on Dynamic SQL for more tips on how to write good dynamic SQL. Depending on what permission you are packaging, there can be other security holes if you for instance do not validate input parameters properly. I am not going to try to give any examples, all I say is that you need to be observant, and also a little creative – try to figure our yourself how you would break the code.
In the separated scenario, there are three concerns: 1) Could Sgt Pepper make manipulations to make the signed procedure perform something you do not agree to? 2) Could he somehow gain access to the private key of the certificate? 3) Could he lure you to run code you should not run?
From what we learnt in the previous chapter, there are some good news. We have learnt that if the contents of the procedure changes, the signature is lost, and it must be signed anew. This is essential here, because this permits you as the server-level DBA to request a review the code every time the local power user changes the code. And, thus, as long as you do your review job properly, there is no way that the local power user can abuse the packaged permission by simply changing the code of the procedure.
In the previous chapter we made another observation: When a signed procedure invokes a system procedure or dynamic SQL, the token of the certificate user/login is still present, and thus the permissions granted to this user/login still applies. However, when the signed procedure invokes a user-written module such as another stored procedure or a trigger, the token of the certificate user/login is removed, and so are the permissions. This works to our advantage here. Say that Sgt Pepper presents us with a stored procedure that calls other procedures or perform operations that could fire DML or DDL triggers. We don't have to worry about what's inside these procedures and triggers, because we know that the permissions we package into the procedure in front of us will not apply to any procedures or triggers it invokes.
However, you cannot have your guard down entirely. To start with, you should always be cautious with dynamic SQL. In this situation you not only need to consider what a malicious plain-vanilla user may do. The procedure may have been written by a malicious power user who have carefully introduced an SQL-injection hole which he plans to exploit, once you have signed the procedure and granted the permissions. Thus, all use of dynamic SQL requires a careful review.
Depending on the permission being granted there can be other traps. A good example is the very one we have worked with in this chapter, VIEW SERVER STATE. In fact, there is a security hole right in the procedure ShowSessions:
CREATE PROCEDURE Management.ShowSessions AS
SELECT * FROM logintokeninfo
IF (is_srvrolemember('PowerUsers') = 1 AND is_member('db_owner') = 1) OR
has_perms_by_name(NULL, NULL, 'CONTROL SERVER') = 1
SELECT * FROM sys.dm_exec_sessions WHERE database_id = db_id()
ELSE
RAISERROR('You don''t have permission to run this procedure!', 16, 1)
We only added the line in red for diagnostic purposes, so we could study how the mechanism works. But say that Sgt Pepper says he wants to have that line permanently, because he ever so often want to inspect the tokens. Can we agree to this? No! In any procedure into which you package VIEW SERVER STATE or any other permission that implies this permission, for instance CONTROL SERVER or sysadmin, we cannot permit any read access against user-defined objects in the database. Sgt Pepper owns the database and thus the definition of logintokeninfo, and he can change the definition as he sees fit, for instance to view with an unfiltered query against sys.dm_exec_sessions or any other DMV he wants to look into, despite having no business there. The key here is that logintokeninfo is not a module on its own, so the certificate still applies when it is accessed. At the same time, the definition of logintokeninfo is not part of what is being signed, so changing logintokeninfo does not invalidate the signature.
This does not only apply to tables and views, but also user-defined functions. It does not matter if Sgt Pepper tells you that a function is a multi-statement function where the certificate does not apply, because he can drop that function and replace it with a inlined table function. And an inline function is not a module of its own; it is only a parameterised view by another name. Scalar functions are no better, since starting with SQL 2019 they can also be inlined, and anything that is inlined will execute with the certificate user/login present among the tokens and thus the elevated permission.
So far the issues for VIEW SERVER STATE (and again everything that implies this permission, including CONTROL SERVER and sysadmin), but other permissions may have similar traps. Be careful, and look suspiciously at any piece of code that does not seem to be crucial for the action that needs the elevated permission. The more powerful the permission, the more attentive you need to be.
Let's now look at the next risk. Could the local power user somehow gain access to the private key and the password that protects it? This is just as much a question to you as it is to me as the author of this article. Do I believe that the recipe that I have proposed is perfectly secure?
I need to start with making a confession: For many years, this article suggested a different recipe where the certificate was first created in master and then copied to the user database and the script GrantPermsToSP_server.sql worked in the same way. This required both the public and the private keys to be copied. The private key was dropped at the end, so far so good. But in 2019 I identified a problem: the debug output from the script included the command to create the certificate with the private key including the password, so if that output fell into the wrong hands, a malicious user could create the certificate in any database he likes.
After giving it some thought, I arrived at the current design where the recipe starts in the user database. This means that the private key can be dropped before we copy the certificate to master. This was not only an improvement of security, but it also served to make the recipe simpler. (Because we are saved the complexities of copying the private key.) Still, since the private key lives in the user database for a short while, is there a way that Sgt Pepper can gain access to it?
There is quite a simple trick that Sgt Pepper can play to prevent the private key from being dropped:
CREATE TRIGGER ddltri ON DATABASE FOR ALTER_CERTIFICATE AS ROLLBACK TRANSACTION BEGIN TRANSACTION
This DDL trigger will silently roll back the statement ALTER CERTIFICATE REMOVE PRIVATE KEY. You can protect yourself against this trap by checking the column sys.certificates.pvt_key_encryption_type_desc which should read NO_PRIVATE_KEY. The script GrantPermsToSP_server.sql includes this check and raises an error that terminates the script if the private key is still there. The procedure will remain signed, but since nothing is ever created in master, there are no extra permissions present.
Even if Sgt Pepper would be successful in preventing the private key from being dropped (for instance because you are not using GrantPermsToSP_server.sql), he still needs to gain access to the password for the private key. That could happen if you are careless with the debug output from your script which will have the password in the CREATE CERTIFICATE and ADD SIGNATURE commands. Could he use a DDL trigger to capture the password? Thankfully, it does not seem like that. Sgt Pepper can retrieve the command that fired the trigger through the eventdata() function, but the password will be masked.
To conclude: the risks in this particular area seem to be small – but not entirely non-existing.
There is however a much more serious problem with DDL triggers. This is not an issue that is specific to certificate signing, but it is a general danger when you as a server-level DBA execute code in a user database where there are local power users you should not trust. Sgt Pepper could create a DDL trigger like this:
EXECUTE AS USER = 'SgtPepper'
go
CREATE OR ALTER TRIGGER ddltri ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS
IF is_srvrolemember('sysadmin') = 1
EXEC('USE master ALTER SERVER ROLE sysadmin ADD MEMBER SgtPepper')
go
REVERT
That is, if someone who is sysadmin runs an DDL operation in the database (which could be an index rebuild in a maintenance job!), the DDL trigger will add Sgt Pepper to sysadmin. It is in order to neutralise this threat that GrantPermsToSP_server.sql wraps all commands in the target database in EXECUTE AS USER = 'dbo'. The reader may think this is not secure, since the database could be owned by sa, but we will learn later in the section Into the Sandbox in chapter 8 why it actually is. Since Playground is owned by sa, you can try the trigger above and see if you are able to promote Sgt Pepper to sysadmin when you run GrantPermsToSP_server.sql. Use this query to check:
SELECT is_srvrolemember('sysadmin', 'SgtPepper')
Just make sure that you drop the trigger when are done playing. If you play with the trigger definition, make sure that you include the statements to impersonate SgtPepper above, since if you change the trigger while you are sysadmin, that will fire the trigger and you will add SgtPepper to sysadmin that way...
Note: I have a separate article, Don't Let Your Permissions be Hijacked!, where I explore this kind of attack in more detail. It starts with DDL triggers, and then continues to discuss how an attacker could use DML triggers or stored procedure or even application code to make you run code you should not execute.
I would like believe that the script that I have presented here is reasonably secure. However, would it be that bad that you find a way that Sgt Pepper or some other malicious power user could break GrantPermsToSP_server.sql and gain access to the private key and its password, or by some other means elevate permissions, please drop me a line at esquel@sommarskog.se.
I like to point out that if you use the recipe on your own, you will need to make similar precautions to avoid malicious DDL triggers being fired when you create and use the certificate.
"Reasonably secure" may not ring well with you, and you may want something you know for sure that is secure. And obviously, if we could find a solution where we never run something ourselves in the user database and where the private key never exists there, that would be something we absolutely can trust to be secure. It is indeed nothing which is impossible to achieve. We have quickly looked at the command that is the key for this: ADD SIGNATURE WITH SIGNATURE where provide a pre-computed signature. Unfortunately, this solution is more complicated to use, which is why I have not explored it in this chapter. However, I will show an example of this technique when I discuss certificate signing for cross-database access in the final chapter.
We have now learnt about using certificates to package database and server permissions in a stored procedure. Before we start looking at the other method, the EXECUTE AS clause, we will take a look at a problem that as such goes beyond the topic for this article. However, the learnings we will make in this chapter are important for the chapters that follow.
Ever so often I see questions in the SQL Server forums from people who want to prevent their users from accessing the database outside the application with SSMS, Excel etc. This may or may not be applications that use stored procedures. The hope is that there is a way to grant permissions to the application, but there is no secure way an application can identify itself to SQL Server. A rogue user can always build his own application that identifies itself as the real application. Instead you need to look for other solutions.
To achieve this in a secure way, there must be three tiers, one way or another. That is, you can never achieve this with a thick Windows client that sits on the users' desktop, because anything the application can do, the users can do on their own. There must be a middle tier between the user machines and SQL Server. This can be a full-blown application server, but if you have a web application, the web server serves as your middle tier in this context.
We will look at three methods:
The script for this chapter is 06_interlude.sql.
You create an application role with the statement CREATE APPLICATION ROLE and you need to define a password for the application role. Then you grant the role the permissions needed to run the application. The application activates the application role with sp_setapprole which requires the password for the role. When setting the role, the application retrieves a cookie from SQL Server, and it uses this cookie when reverting from the application role with sp_unsetapprole before disconnecting.
Reverting from the application role is not necessary for an application that sticks to a static connection, but most applications follow the pattern of connecting, running an SQL command or two before disconnecting within the same method. They rely on that the API maintains an connection pool from which connections are reused. However, SQL Server does not agree to the reuse of a connection on which an application role has been set, since the new logical connection has a different security context. Whence the need to unset the application role. The cookie is a security measure to prevent the user from being able to unset the application role, would there be an SQL injection hole in the application.
A full-fledged demo of application roles would require some client code, but we will let it suffice with a T‑SQL script. That is still sufficient to learn how application roles work:
SET XACT_ABORT, NOCOUNT ON
USE Playground
go
CREATE APPLICATION ROLE AppRole WITH PASSWORD = 'Tomorrow Never Knows'
GRANT EXECUTE, SELECT TO AppRole
go
EXECUTE AS LOGIN = 'SgtPepper'
CREATE TABLE #cookie(cookie varbinary(8000) NOT NULL)
DECLARE @cookie varbinary(8000)
EXEC sp_setapprole 'AppRole', N'Tomorrow Never Knows',
@fCreateCookie = 'true', @cookie = @cookie OUTPUT
INSERT #cookie (cookie) VALUES (@cookie)
go
SELECT * FROM tokeninfo
SELECT * FROM logintokeninfo
EXEC Management.ShowSessions
INSERT Playtable(id, somedata) VALUES (-1, 'It was twenty years ago today')
go
DECLARE @cookie varbinary(8000)
SELECT @cookie = cookie FROM #cookie
EXEC sp_unsetapprole @cookie
DROP TABLE #cookie
go
REVERT
We first create the application role, which we grant EXECUTE and SELECT permission on database level (in contrast to Playrole who only have EXECUTE permission on the dbo schema). We then become Sgt Pepper, remember that he is a db_owner user in Playground. We set the application role and save the cookie into a temp table, so that we don't lose it. (Would you lose the cookie, you will have no choice but to disconnect and reconnect, else you cannot get out of the application role.) In the next batch we inspect the contents of the tokeninfo and logintokeninfo views and attempt to run ShowSessions and insert a row into Playtable. Having done this, we retrieve the cookie and unset the application role and finally we revert from the impersonation of Sgt Pepper.
This is the output I get when running the above, with some repeating columns removed for brevity:
token_name type usage original_login SYSTEM_USER DBUSER
---------- ---------------- ------------- ----------------- ----------- -------
AppRole APPLICATION ROLE GRANT OR DENY LIVERPOOL\GMartin SgtPepper AppRole
public ROLE GRANT OR DENY LIVERPOOL\GMartin SgtPepper AppRole
token_name type usage
---------- --------------- -------------
public SERVER ROLE DENY ONLY
token_name type usage
--------------------------------------------- --------------------------- ---------
public SERVER ROLE DENY ONLY
SIGN [Playground].[Management].[ShowSessions] LOGIN MAPPED TO CERTIFICATE DENY ONLY
SIGN [Playground].[Management].[ShowSessions] CERTIFICATE DENY ONLY
Msg 50000, Level 16, State 1, Procedure ShowSessions, Line 8 [Batch Start Line 13]
You don't have permission to run this procedure!
Msg 229, Level 14, State 5, Line 17
The INSERT permission was denied on the object 'Playtable', database 'Playground', schema 'dbo'.
Let's first look at the first result set, the contents from tokeninfo. There are two tokens: AppRole and public. But there is no trace of Sgt Pepper or db_owner which he is a member of. Furthermore, the function USER returns the name of the application role (the same is true for other functions that returns the current database user). That is, on database level, the application role has simply replaced Sgt Pepper as the current user. On the other hand, SYSTEM_USER still returns SgtPepper. The corollary of this is that if you have auditing or row-level auditing in your application based on USER or similar function, you will need to rework this, if you want to use application roles. On the other hand, if you are using a server-level function like SYSTEM_USER, suser_name() etc, you are good.
The next result set is from logintokeninfo and gives us the server-level tokens, and we can see that there is only one: public. SgtPepper is not present among the tokens, and nor is PowerUsers, the server role that Sgt Pepper is a member of. Furthermore, observe the column usage. So far, we have only seen GRANT OR DENY in this column, but now it reads DENY ONLY. This means that this token is not good for granted permissions, but only denied permissions. That is, if your application needs to do things on server level or in another database, you cannot use application roles. But there are plentiful of applications who are happy to work in a single database.
The third result set is again from logintokeninfo, but this time from inside of ShowSessions. The certificate and certificate login are now among the tokens, but they also have DENY ONLY. We see that ShowSessions fails. As it happens, the particular failure is due to that ShowSessions checks whether the user is a member of PowerUsers and that is not the case when the application role is in force. But would you rewrite the procedure to remove the check and re-sign it, the SELECT would still only return a row for the current session.
The final error message is from the attempt to insert a row into Playtable, which fails since AppRole does not have any INSERT permission. The fact that Sgt Pepper is db_owner is entirely irrelevant, since AppRole is the current user context.
Assuming that your application is not suffering from any of the limitations discussed above, should you use application roles? Maybe, but I cannot say that I am enthusiastic over them. I like to point out a few things:
A common approach is that the application somehow authenticates the actual user, and logs on to SQL Server with a dedicated login. It goes without saying that the login must be performed from a middle tier, to keep the credentials of the application out of reach from the users.
There are quite a few ways, the actual user can be conveyed to SQL Server:
We will look at all of these.
That is, the application never tells the database who is the actual user. This can be acceptable for some less important small-scale applications, but I would say that this space is growing smaller and smaller. Most systems require some form of auditing, either because of the business itself or because of outside regulations. And obviously, row-level security cannot be implemented in the database tier, if the database does not know the user.
In this setup, the application login needs to be granted all permissions needed, which preferably should not extend beyond db_datareader and db_datawriter to reduce the damage of SQL injection holes. Alas, it is not entirely uncommon to see sa used as the application login – this is an extremely bad idea!
In this case, the application login needs only a single permission: IMPERSONATE on database level, since the first thing the application executes after connecting is:
EXECUTE AS USER = 'RealUser' WITH COOKIE INTO @cookie
The users may be SQL users who have been added to the database WITHOUT LOGIN or they can be Windows users, who only have been granted access to the database but who have no logins on server level. In either case, they cannot access SQL Server without the application.
We have already used the EXECUTE AS USER command quite a bit, but the clause WITH COOKIE is new. It serves the same purpose as the cookie with application roles. That is, once you have specified the clause WITH COOKIE, you must use that cookie when reverting to the original security context:
REVERT WITH COOKIE = @cookie
This prevents the user from exploiting any SQL injection hole to issue a REVERT command to perform actions as the application login.
As with application roles, applications that use connection pooling need to revert before disconnecting, or else the connection cannot be reused. Applications that keeps their connection open can specify the clause WITH NO REVERT with EXECUTE AS to prevent REVERT altogether. Alas, the issue with the TokenAndPermUserStore growing also exists with EXCECUTE AS WITH COOKIE and REVERT WITH COOKIE.
We have already used EXECUTE AS USER to test actions inside the database. Let's now look at the effects on server level. Run this:
EXECUTE AS USER = 'SgtPepper' EXEC Management.ShowSessions go REVERT
(Because the use of cookie or not does not affect the result, we can permit us to skip that part.) This is the output:
token_name type usage original_login SYSTEM_USER DBUSER
---------- ------------ ----------- ------------------ ----------- ---------
SgtPepper SQL LOGIN DENY ONLY LIVERPOOL\GMartin SgtPepper SgtPepper
public SERVER ROLE DENY ONLY LIVERPOOL\GMartin SgtPepper SgtPepper
PowerUsers SERVER ROLE DENY ONLY LIVERPOOL\GMartin SgtPepper SgtPepper
token_name type usage
--------------------------------------------- --------------------------- ---------
SgtPepper SQL LOGIN DENY ONLY
public SERVER ROLE DENY ONLY
PowerUsers SERVER ROLE DENY ONLY
SIGN [Playground].[Management].[ShowSessions] LOGIN MAPPED TO CERTIFICATE DENY ONLY
SIGN [Playground].[Management].[ShowSessions] CERTIFICATE DENY ONLY
Msg 50000, Level 16, State 1, Procedure ShowSessions, Line 8 [Batch Start Line 27]
You don't have permission to run this procedure!
Unlike when we used an application role, the tokens of SgtPepper and PowerUsers are not removed from the login tokens, but it does not help as the usage is DENY ONLY. That is, just like when we use an application role, we are not able to access things outside the database. Thus, this solution is only useful for applications that works entirely inside the database. (In case you wonder about the rationale for this behaviour, we will come back to this when we discuss TRUSTWORTHY later on.)
At first, it may seem that this solution requires a lot of administration for all these users WITHOUT LOGIN, but if the application server authenticates the users, it seems reasonable to delegate the duty of creating the database users to the application login, which would call a stored procedure which has been granted the permissions ALTER ANY USER with help of certificate signing.
One possible advantage with EXECUTE AS USER over application roles and session context is that you can control what the individual users can do in the database by granting them different permissions. Then again, I can't see myself building an application that relies on the permissions in SQL Server. How pretty is it if the user tries to save and gets a permission error because he/she only has SELECT permission? But it could be that you want to use the database permissions to protect yourself against glitches in the permission system in the application.
In contrast to sp_setapprole, EXECUTE AS USER can appear in nested scopes (except when you use NO REVERT or WITH COOKIE which must be executed on top level). Theoretically, this permits you to embed EXECUTE AS USER and REVERT in the command batches to avoid extra network roundtrips. However, the batch may abort because of an error before REVERT is executed, and in this case you cannot revert in your client-side exception handler since the cookie was only in the SQL batch (unless you saved it into a temp table). One possible option is that the exception handler disconnects and immediately reconnects to swallow the error which the reconnection causes. While this may be doable, it gets a bit complex and you may prefer to run EXECUTE AS and REVERT in separate batches after all.
Note: There is an older (and deprecated) command SETUSER which has a similar effect. I have not investigated whether are any significant differences. In any case, you should stay away from SETUSER.
This is very similar to the above, but the application uses EXECUTE AS LOGIN for impersonation instead and thus needs IMPERSONATE permission on server level. With this arrangement, there are no restrictions for actions that require server-level permissions. But instead you need different measures to prevent the users from logging into SQL Server from outside the application. There are a couple of possible solutions:
It's not a bad idea to combine these, to avoid that a single accidental change suddenly permits users to connect.
In this solution, the application uses the session context to set information about the current user. The session context is a user-defined area that programmers can use to set session-global values. Up to SQL 2014 it was known as "context info", and all there was to play with was 128 bytes. In SQL 2016 the concept was vastly improved, and we can now use up to 256 KB for session context. When using session context, all access to tables, stored procedures etc are by the application login; that is, there is no impersonation. As with application roles, the application login should at most be granted membership in db_datareader and db_datawriter. If the application login needs to perform privileged actions beyond this, put these actions in stored procedures you have signed with a certificate.
There are two ways to use the session context. Let's first look at sp_set_session_context that was introduced in SQL 2016. This procedure accepts key-value pairs that you can set. Optionally, you can define a key as read-only, which is very good when you use it to define a user name to be used for auditing, as this prevents a malicious user to use an SQL-injection hole to change his name. Here is one example (but don't run this yet):
EXEC sp_set_session_context 'Username', 'PolythenePam', @read_only = 1
To access data from the session context, you use the session_context function. When you use it for auditing, row-level security etc, you need to account for access outside the application by the DBA and other licensed persons where session_context may return NULL. So you need to do as in this example:
CREATE TABLE audited
(somedata int NOT NULL,
moduser sysname NOT NULL
CONSTRAINT def_moduser DEFAULT
coalesce(convert(nvarchar(128), session_context(N'Username')),
original_login())
)
session_context returns sql_variant, which is why you need the conversion. Since you have to repeat this expression in many places, you may prefer to put it in a user-defined function, although this can incur some overhead for mass-updates. Here is a demo you can try:
INSERT audited(somedata) VALUES (9) EXEC sp_set_session_context 'Username', 'PolythenePam', @read_only = 1 INSERT audited(somedata) VALUES (64) SELECT somedata, moduser FROM audited
This is the output for GMartin:
somedata moduser
----------- ------------------
9 LIVERPOOL\GMartin
64 PolythenePam
In contrast to the other methods, there are no conflicts with connection pooling – the session context is automatically cleared when a connection is reused. Another nice thing is that you don't need any user administration whatsoever on the server, and the users can be entirely locked out.
All and all, this makes session context quite a palatable solution. It does require that you use the session_context function (or the UDF you have packaged it in) consistently in your database. Then again, the same applies for whatever auditing scheme you use – there are many functions to use, but you should the same throughout your system.
A possible drawback for the DBA who is monitoring the system is that the session context cannot be collected in Trace or Extended Events, so it is not possible to find the actual user, would that be needed.
In SQL 2014 and earlier, sp_set_session_context and session_context are not available, but instead you can use the command SET CONTEXT_INFO to set the user name. This command accepts a value of the type varbinary(128). Here is an example:
DECLARE @contextinfo varbinary(128) SELECT @contextinfo = convert(varbinary(128), N'RockyRacoon') SET CONTEXT_INFO @contextinfo
To use it, you use the function context_info as in this example:
CREATE TABLE also_audited
(somedata int NOT NULL,
moduser sysname NOT NULL
CONSTRAINT also_def_moduser DEFAULT
coalesce(convert(nvarchar(64),
substring(context_info(), 1,
charindex(0x0000, context_info()) - 1)),
original_login())
)
This certainly even more calls for being packaged in a UDF!
Here is a script for testing, which also clears context info, in case you already ran the command above.
SET CONTEXT_INFO 0x INSERT also_audited(somedata) VALUES (9) DECLARE @contextinfo varbinary(128) SELECT @contextinfo = convert(varbinary(128), N'RockyRacoon') SET CONTEXT_INFO @contextinfo INSERT also_audited(somedata) VALUES (64) SELECT somedata, moduser FROM also_audited
A drawback with SET CONTEXT_INFO is that it does not provide a way to make the value read-only, so an SQL injection hole could permit a malicious user to hide his identity and play someone else. Which, if there is row-level security involved, can be a quite serious breach!
(On the other hand, the monitoring DBA may prefer context info over the new session context, since the value of context_info can be collected in Extended Events.)
It is worth noting that SET CONTEXT_INFO is different from all other SET commands: normally when you issue a SET command inside a stored procedure, the effect of the SET command is reverted when the procedure exits, but this is not true for SET CONTEXT_INFO. Just like session context, the context info is cleared when a connection is reused in the connection pool.
So when you find that you have painted yourself into a corner and realised your fat client cannot be transformed to a three-tier application within reasonable cost, and you still want to keep users away from SQL Server outside the application, there is still one way out. You can put the application on Terminal Server or a solution like Citrix. That is, to run the application, the users log on to another server, the login script starts the application, and if the application stops, they are logged out.
To prevent the users from accessing SQL Server, you segment the network, so they cannot access SQL Server from their desktops and laptops.
It is not a bad idea to combine this with application roles and grant no permissions to the users. This sets up an extra security layer, in the case there is an error with the network configuration that opens access to SQL Server to the users.
The technical details how to implement this are entirely beyond of the scope for this article.
We will now go back to the main theme of this article, that is, how to package permissions in a stored procedure when our needs go beyond what is possible with ownership chaining. This time, we will now look at the EXECUTE AS clause. If you found certificate signing to be a bit complicated, you will be delighted to find that this method is easier to use. In fact, a little too easy, as you will learn.
The script for this chapter is 07_executeasdb.sql.
Earlier we worked with check_playdata, which checks whether Playtable exists before running a query against it. Here is a version with a different name (so we can keep the signed version) that includes the diagnostic SELECT from tokeninfo.
SET XACT_ABORT, NOCOUNT ON
USE Playground
go
CREATE PROCEDURE check_playdata2 @id int AS
SELECT * FROM tokeninfo
IF object_id('Playtable') IS NOT NULL
SELECT id, somedata, whodidit FROM Playtable WHERE id = @id
ELSE
PRINT 'Playtable has not been created yet. Try again later.'
As a recap, we run this as Michelle:
EXECUTE AS USER = 'Michelle' EXEC add_playdata 11, 'Strawberry Fields Forever' EXEC add_playdata 12, 'Penny Lane' EXEC check_playdata2 11 go REVERT
We see something like this:
id somedata whodidit
----- -------------------------- -----------
11 Strawberry Fields Forever Michelle
id somedata whodidit
----- -------------------------- -----------
12 Penny Lane Michelle
token_name type usage original_login SYSTEM_USER DBUSER
---------- -------- ------------- ----------------- ---------------- --------
Michelle SQL USER GRANT OR DENY LIVERPOOL\GMartin S-1-9-3-24966... Michelle
public ROLE GRANT OR DENY LIVERPOOL\GMartin S-1-9-3-24966... Michelle
Playrole ROLE GRANT OR DENY LIVERPOOL\GMartin S-1-9-3-24966... Michelle
Playtable has not been created yet. Try again later.
Ownership chaining applies in add_playdata which is just doing INSERT and SELECT on Playtable. But since ownership chaining does not apply to metadata access, and none of the tokens present carry any permission on Playtable, Michelle is incorrectly told that the table is unavailable when she runs check_playdata2. Let's address this by using the EXECUTE AS clause. Here is the recipe:
When it comes to the first step, we have already used CREATE USER WITHOUT LOGIN. When we create a user from a certificate, this is a special user that cannot be impersonated or used for anything else than bridging certificate and permission. With EXECUTE AS there is nothing such special, but we use a regular database user, albeit one without a login.
Here are all three steps in the recipe at once, with the EXECUTE AS clause highlighted:
CREATE USER check_playdata2$user WITHOUT LOGIN
GRANT VIEW DEFINITION ON Playtable TO check_playdata2$user
go
ALTER PROCEDURE check_playdata2 @id int
WITH EXECUTE AS 'check_playdata2$user' AS
SELECT * FROM tokeninfo
IF object_id('Playtable') IS NOT NULL
SELECT id, somedata, whodidit FROM Playtable WHERE id = @id
ELSE
PRINT 'Playtable has not been created yet. Try again later.'
Michelle gives it a go:
EXECUTE AS USER = 'Michelle' EXEC check_playdata2 11 go REVERT
The output this time is quite different (I have split up the first result set on several lines to fit the page):
token_name type usage
------------------------------ ---------- ---------------
check_playdata2$user SQL USER GRANT OR DENY
public ROLE GRANT OR DENY
original_login SYSTEM_USER DBUSER
------------------ ----------------- -----------------------------
LIVERPOOL\GMartin S-1-9-3-148309... check_playdata2$user
LIVERPOOL\GMartin S-1-9-3-148309... check_playdata2$user
id somedata whodidit
----- --------------------------- ----------
11 Strawberry Fields Forever Michelle
Michelle can now see the data in Playtable, but there is something else in this picture that is more interesting. Look at the information in tokeninfo: there is no trace of Michelle, but she has been replaced by check_playdata2$user. Because both Michelle and check_playdata2$user have been created WITHOUT LOGIN, the output from SYSTEM_USER does not stand out, but if you compare the output with the above, you can see that the SID is not the same. That is, Michelle has been entirely eradicated. The only other information present is GMartin who originally logged into SQL Server.
Thus, this is a quite a different mechanism from certificate signing. It's not a matter of adding a security token, but it is impersonation, exactly what we have already used the EXECUTE AS statement for. This has some quite some ramifications which we will analyse later in this chapter.
I hear you: Yeah, this was simpler than certificate signing. But there is still some hassle with this proxy user and figuring out the right permissions, isn't there something simpler?
Indeed there is. For the lazy and casual, there is a very quick solution as demonstrated by a version of dynamic_playdata:
CREATE PROCEDURE dynamic_playdata2 @searchstr nvarchar(40)
WITH EXECUTE AS OWNER AS
DECLARE @sql nvarchar(MAX)
SELECT * FROM tokeninfo
SELECT @sql = 'SELECT * FROM dbo.Playtable
WHERE somedata LIKE ''%'' + ''' + @searchstr + ''' + ''%'''
EXEC(@sql)
go
EXECUTE AS USER = 'Michelle'
EXEC dynamic_playdata2 'berry'
go
REVERT
The output when Michelle runs this:
token_name type usage original_login SYSTEM_USER DBUSER
------------- ---------- -------------- ------------------ ------------ --------
dbo SQL USER GRANT OR DENY LIVERPOOL\GMartin sa dbo
public ROLE GRANT OR DENY LIVERPOOL\GMartin sa dbo
db_owner ROLE GRANT OR DENY LIVERPOOL\GMartin sa dbo
id somedata whodidit
----------- ---------------------------------------- ----------
11 Strawberry Fields Forever Michelle
Look! Four extra words was all it took. AS OWNER here means the owner of the procedure, which in this case (as often) is dbo, and you can tell from the output from tokeninfo that indeed it was dbo that was impersonated.
While simple, it breaks the principle of not granting more permissions than needed. And Michelle will be quick to show you why. Look what she is up to:
EXECUTE AS USER = 'Michelle'
go
CREATE PROCEDURE #xploit AS ALTER ROLE db_owner ADD MEMBER Michelle
go
EXEC dynamic_playdata2 'zz''EXEC #xploit--'
DROP PROCEDURE #xploit
SELECT is_member('db_owner') AS is_dbowner
go
REVERT
She first creates a temporary stored procedure (all users have the permission to do this) of which the body makes her member of db_owner. Then she runs dynamic_playdata2 which is a poorly written procedure with a hole open for SQL injection. She first enters zz to prevent the procedure from returning too much data. She then enters a single quote to close the search string. Now she adds a call to her procedure and finally she adds comment characters to kill the rest of the original command. This is the actual command that is executed:
SELECT * FROM dbo.Playtable
WHERE somedata LIKE '%' + 'zz' EXEC #xploit--' + '%'
And as you can see of the result from is_member, the exploit worked. Michelle is now member of db_owner.
You may ask what the point is with the temporary stored procedure. Why didn't she inject the ALTER ROLE command directly into the SQL string? The answer is simply that the parameter length of 40 is a just a little short for this to fit. But Michelle worked around this with help of the temporary stored procedure. It is true, though, that this requires that she has direct access to run queries in SSMS or similar. If she had been using an application with any of the solutions in the previous chapter, this exploit would not have been possible. Then again, you may recall that in the original dynamic_playdata, the sloppy programmer hade made the parameter nvarchar(MAX), and in that case there is no need for temporary stored procedures, but everything fits.
Of course, you could argue that as long as the dynamic SQL is correctly constructed and is parameterised, there are no injection holes, and there should be no problems with using EXECUTE AS OWNER. But keep in mind that it only takes one bad programmer to take down that line of defence. So there is all reason to have a second one by granting as few permission as needed, be that through a proxy user or a certificate signature.
Before we move on, take Michelle out of db_owner:
ALTER ROLE db_owner DROP MEMBER Michelle
We have seen that the effects of impersonation extend into dynamic SQL, as was the case with the token of the certificate user. How does it work in other contexts? We recreate the trigger we had earlier and create a copy of outer_sp. For the sake of the demo, we also create a user for outer_sp2, although this procedure does not need any extra permissions.
CREATE TRIGGER play_tri ON Playtable AFTER INSERT AS
SELECT 'In trigger' AS wherearewe, *
FROM tokeninfo
go
CREATE USER outer_sp2$user WITHOUT LOGIN
go
CREATE PROCEDURE outer_sp2 @id int, @somedata nvarchar(40)
WITH EXECUTE AS 'outer_sp2$user' AS
SELECT 'outer_sp' AS wherearewe, *
FROM tokeninfo
INSERT Playtable(id, somedata)
VALUES(@id, @somedata)
EXEC inner_sp
We run outer_sp2 as Michelle:
EXECUTE AS USER = 'Michelle' EXEC outer_sp2 13, 'Yesterday' go REVERT
The output is something like this (with the result sets split up over two lines):
wherearewe token_name type usage
---------- --------------- -------- --------------
outer_sp outer_sp2$user SQL USER GRANT OR DENY
outer_sp public ROLE GRANT OR DENY
original_login SYSTEM_USER DBUSER
----------------- ----------------- --------------
LIVERPOOL\GMartin S-1-9-3-131059... outer_sp2$user
LIVERPOOL\GMartin S-1-9-3-131059... outer_sp2$user
wherearewe token_name type usage
---------- --------------- -------- --------------
In trigger outer_sp2$user SQL USER GRANT OR DENY
In trigger public ROLE GRANT OR DENY
original_login SYSTEM_USER DBUSER
----------------- ----------------- --------------
LIVERPOOL\GMartin S-1-9-3-131059... outer_sp2$user
LIVERPOOL\GMartin S-1-9-3-131059... outer_sp2$user
wherearewe token_name type usage
---------- --------------- -------- --------------
inner_sp outer_sp2$user SQL USER GRANT OR DENY
inner_sp public ROLE GRANT OR DENY
original_login SYSTEM_USER DBUSER
----------------- ----------------- --------------
LIVERPOOL\GMartin S-1-9-3-131059... outer_sp2$user
LIVERPOOL\GMartin S-1-9-3-131059... outer_sp2$user
You may recall that when we did the same experiment with certificates, the token of the certificate user was removed when we entered another user-written module. But something like that does not happen here. The effect of the impersonation is in effect until outer_sp2 exits. (Or there is some inner module that has its own EXECUTE AS clause.)
Let's also look in Playtable:
SELECT * FROM Playtable
This is what we find:
id somedata whodidit
----------- --------------------------- --------------
11 Strawberry Fields Forever Michelle
12 Penny Lane Michelle
13 Yesterday outer_sp2$user
That is, the row inserted into Playtable in outer_sp is said to have been inserted by outer_sp2$user and not by Michelle. But outer_sp2$user is supposed to only be a proxy for packaging permissions. The real user is Michelle.
Once impersonation is in play, all those functions that return the current server login or current database user: SYSTEM_USER, suser_name(), suser_sname(), suser_id(), suser_sid(), CURRENT_USER, USER, SESSION_USER, user_id() and user_name() returns information about the impersonated user. And it does not matter whether impersonation was initiated with the statement EXECUTE AS or by the clause EXECUTE AS in a stored procedure or other module, they are just two faces of the same thing.
These functions are frequently used in SQL Server applications. One use for them is to answer questions in the application like "is this user entitled to see or modify this data"? The typical example is row-level security (RLS). Say that you put an EXECUTE AS clause with a proxy user in a stored procedure to package permissions, and this procedure includes access against a table or view with row-level security, be that a home-brew or something that uses the new built-in RLS feature added in SQL 2016. All of a sudden no rows come back. Or even worse: you use EXECUTE AS OWNER, and the rules are set up to let dbo see everything.
The most common usage for these system functions might be auditing. They could appear in defaults as in our Playtable, or they could be used in triggers that writes to log tables. And as we saw above, this auditing will be incorrect when we use the EXECUTE AS clause.
Auditing may also be performed through SQL Trace (which is what Profiler uses) based on the columns LoginName or NTUserName or through Extended Events sessions that use nt_username, server_principal_name or username. All of which change values when impersonation is in effect. You need to ask yourself: when it is of interest to audit the proxy user in the EXECUTE AS clause? Probably never.
If you use the SQL Audit feature, the audit file will always include the name of the user who actually logged as well as the name of the impersonated user. Beware, though, that when you set up a database audit with CREATE DATABASE AUDIT SPECIFICATION and filter by a database principal, the filter will work on the impersonated principal and not the original login, which means that you may miss actions that should have been audited – or that you get too much in your audit log, because you filtered by dbo and then used EXECUTE AS OWNER all over the place.
This does not mean that you cannot use EXECUTE AS if you want row-level security or auditing, but you need to plan ahead and design your system accordingly. That is, you cannot use any of the functions listed above, but you must use one of original_login() or session context/context info.
original_login() returns the user who actually logged into SQL Server and as we have seen in the output from the tokeninfo view, it is unaffected by impersonation. In Trace, the corresponding column to collect is SessionLoginName and with Extended Events it is session_server_principal_name.
In the previous chapter we looked at how an application login could use any of the EXECUTE AS statements to impersonate the actual user. In this architecture, original_login() does not work, because it returns the name of the application login. Nor is there any function to get the names in the middle of the impersonation stack. Thus, if you have this setup, and you want to use EXECUTE AS in your stored procedures, your only option to pass the name of the actual user to auditing and RLS filters is session context / context info which we also looked at in the previous chapter.
By now you may have to come to the realisation that what initially seemed to be a simple solution, in fact is not really that simple. While using certificates at first seemed like complicated mumbo-jumbo, it only affects the actual procedure you sign, and you can easily automate the process in a stored procedure or in a script. EXECUTE AS on the other hand requires that you consider the entire architecture of your application before you can start using it. If you are already using original_login() or session context, you can go ahead, but else you have work to do.
If you know that your system uses SYSTEM_USER all over the place, you may be a little scared. What if your developers learn about EXECUTE AS OWNER from somewhere without understanding the consequences? Maybe they have already done so? Here is a query that lists all procedures in the database with an EXECUTE AS clause:
SELECT s.name + '.' + o.name AS Module,
CASE sm.execute_as_principal_id WHEN -2 THEN 'OWNER'
ELSE user_name(sm.execute_as_principal_id)
END AS [EXECUTE AS]
FROM sys.sql_modules sm
JOIN sys.objects o ON sm.object_id = o.object_id
JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE sm.execute_as_principal_id IS NOT NULL
This is the output in the Playground database:
Module EXECUTE AS
------------------------- -----------------------
dbo.dynamic_playdata2 OWNER
dbo.check_playdata2 check_playdata2$user
dbo.outer_sp2 outer_sp2$user
If you don't want this ever to happen, and you are the evil sort of person, you may want to add a DDL trigger to your database to stop this from happening. You can find such a trigger and a test procedure in the script StopExecAs.sql. If you play with it in Playground, be sure to drop the trigger once you have completed your testing.
Before I close this section, I should add that even if your database relies on SYSTEM_USER or any similar function, it may still be alright to use EXECUTE AS for a specific procedure, because you have concluded that this particular procedure does not affect any area where this matters. For instance, say that in your application you want to permit super users to add users to the database. If you create a procedure with EXECUTE AS that do nothing more than CREATE USER, that should be alright. There could be an outer procedure which handles the auditing required for the operation. Again, this is something that requires understanding of what you are doing, and personally, I much prefer certificate signing over EXECUTE AS.
In this section I will cover various titbits around the EXECUTE AS clause that I have not mentioned so far.
Another variant of the EXECUTE AS clause is to say WITH EXECUTE AS SELF. This is short for saying WITH EXECUTE AS 'yourusername'. I will have to admit that I have not really seen the use case for this.
You can also say:
CREATE PROCEDURE myproc WITH EXECUTE AS CALLER AS
This is merely stating the default. That is, this is no different from saying
CREATE PROCEDURE myproc AS
You can also use EXECUTE AS CALLER as a statement inside a stored procedure. This permits you to revert to the caller's context so you can retrieve information about the caller. For instance, you could do:
EXECUTE AS CALLER SELECT @actual_user = SYSTEM_USER REVERT
Thus, this is something that can help you to overcome the effects of impersonation, but only in the procedure that has the EXECUTE AS clause. If a trigger invoked by the procedure would get the idea to try EXECUTE AS CALLER, it would only get the user name of the proxy user, that is, the direct caller of the trigger.
You could also use EXECUTE AS CALLER for the main bulk of the procedure, and revert back to the impersonated user for the action that requires special powers. For instance:
CREATE PROCEDURE check_playdata3 @id int
WITH EXECUTE AS 'check_playdata2$user' AS
DECLARE @object_id int = object_id('Playtable')
EXECUTE AS CALLER
IF @object_id IS NOT NULL
SELECT id, somedata, whodidit FROM Playtable WHERE id = @id
ELSE
PRINT 'Playtable has not been created yet. Try again later.'
REVERT
go
EXECUTE AS USER = 'Michelle'
EXEC check_playdata3 12
go
REVERT
This runs fine and returns the correct information in the whodidit column, but at the price of the code being more cluttered.
The above may inspire you to think: why not simply use EXECUTE AS USER in the procedure around the statement where extra powers are needed? That would be more logical and emphasise the special part. For instance like this:
CREATE PROCEDURE check_playdata4 @id int AS
EXECUTE AS USER = 'check_playdata2$user'
DECLARE @object_id int = object_id('Playtable')
REVERT
IF @object_id IS NOT NULL
SELECT id, somedata, whodidit FROM Playtable WHERE id = @id
ELSE
PRINT 'Playtable has not been created yet. Try again later.'
Michelle tries this procedure:
EXECUTE AS USER = 'Michelle' EXEC check_playdata4 12 go REVERT
But there is no success:
Msg 15517, Level 16, State 1, Procedure check_playdata3, Line 2
Cannot execute as the database principal because the principal "check_playdata2$user" does not exist, this type of principal cannot be impersonated, or you do not have permission.
When EXECUTE AS appears as a statement in a procedure, this is the same as with all other "advanced" statements: the user must have direct permission to impersonate the user. It is only when it appears as a clause that this requirement is voided.
You may recall that when we issued an explicit DENY for Michelle, the token from the certificate user did not help to overcome that. This is different with EXECUTE AS, since this is impersonation and the context of the original user is lost. So even if you prefer certificate signing in general, you may turn to EXECUTE AS if you end up in a situation where a DENY is blocking the way. (But as I noted previously, you should be very restrictive with using DENY and normally it should not be needed.)
If you are using natively compiled procedures in SQL 2014, the EXECUTE AS clause is mandatory, and you cannot use EXECUTE AS CALLER. The effects of this are, however, quite limited, since in SQL 2014 none of the functions to return the information about the current user are available in natively compiled stored procedures.
This restriction does not apply for SQL 2016 or later, but here you can leave out EXECUTE AS or use EXECUTE AS CALLER. And functions like original_login(), SYSTEM_USER and USER are available.
We have now looked at using EXECUTE AS to package permissions on database level. You may have noticed that I am not entirely enthusiastic over EXECUTE AS and that I favour certificate signing. But you are excited over the simplicity that EXECUTE AS offers, so you want to use it on server level as well. You say to yourself: how difficult can it be? Just create a proxy login that I disable so that it cannot be used, grant that login the permissions needed and add EXECUTE AS. Well, maybe. We'll see.
The script for this chapter is 08_execasserver.sql.
Let's try the idea with the procedure ShowSessions. As you may recall the problem was that we wanted power users to be able to see all sessions connected to their database, but they should not see other users. This is how the procedure looked like last time we looked at it:
CREATE PROCEDURE Management.ShowSessions AS
SELECT * FROM logintokeninfo
IF (is_srvrolemember('PowerUsers') = 1 AND is_member('db_owner') = 1) OR
has_perms_by_name(NULL, NULL, 'CONTROL SERVER') = 1
SELECT * FROM sys.dm_exec_sessions WHERE database_id = db_id()
ELSE
RAISERROR('You don''t have permission to run this procedure!', 16, 1)
We will make a ShowSessions2 where we use EXECUTE AS rather than certificate signing. First we need that proxy login, so let's create it:
SET XACT_ABORT, NOCOUNT ON
USE master
go
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN [PROXY Playground.Management.ShowSessions2] ' +
'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
go
ALTER LOGIN [PROXY Playground.Management.ShowSessions2] DISABLE
DENY CONNECT SQL TO [PROXY Playground.Management.ShowSessions2]
GRANT VIEW SERVER STATE TO [PROXY Playground.Management.ShowSessions2]
We use the same naming pattern as we used for certificates for server-level signing. That is, we have a prefix which directly informs us about the purpose, and then there is the full three-part name of the procedure. We create a random password for this login that we never display, and we disable the login and we also explicitly DENY the login the permission CONNECT SQL – this is the permission you need to be able to log on to SQL Server. Since this login is just a proxy, it should never log in on its own.
So now we can copy the code above and add the EXECUTE AS clause? What could go wrong?
USE Playground
go
CREATE PROCEDURE Management.ShowSessions2
WITH EXECUTE AS 'PROXY Playground.Management.ShowSessions2' AS
SELECT * FROM logintokeninfo
IF (is_srvrolemember('PowerUsers') = 1 AND is_member('db_owner') = 1) OR
has_perms_by_name(NULL, NULL, 'CONTROL SERVER') = 1
SELECT * FROM sys.dm_exec_sessions WHERE database_id = db_id()
ELSE
RAISERROR('You don''t have permission to run this procedure!', 16, 1)
go
EXECUTE AS LOGIN = 'SgtPepper'
EXEC Management.ShowSessions2
go
REVERT
This is the error message:
Msg 916, Level 14, State 1, Procedure ShowSessions2, Line 0
The server principal "PROXY Playground.Management.ShowSessions2" is not able to access the database "Playground" under the current security context.
Oops, we must also add the proxy login as a user in the Playground database.
CREATE USER [PROXY Playground.Management.ShowSessions2]
Which fails with:
Msg 15023, Level 16, State 1, Line 31
User, group, or role 'PROXY Playground.Management.ShowSessions2' already exists in the current database.
This may seem surprising, but if you look closely above, you realise that the error message about the proxy login not being able to access the database came when Sgt Pepper tried to execute the procedure, not when the procedure was created. (Since else there would have been a message about the procedure missing.) When we created the procedure, SQL Server was helpful and created the user in the database for us, as this is needed for the proxy user to be recorded in sys.sql_modules as execute_as_principal. However, the user was not granted CONNECT permission and whence the error. Grant it, and try it again as Sgt Pepper.
GRANT CONNECT TO [PROXY Playground.Management.ShowSessions2] EXECUTE AS LOGIN = 'SgtPepper' EXEC Management.ShowSessions2 go REVERT
But there is a new error message:
Msg 50000, Level 16, State 1, Procedure ShowSessions2, Line 9
You don't have permission to run this procedure!
If you have been paying attention, you already understand why we are getting this error. In the procedure we are impersonating the proxy login, but the proxy is not a member of PowerUsers, nor does it have the CONTROL SERVER permission. In fact, the entire idea with this check is that the DBA only wants the trusted PowerUsers to run this procedure, even if it resides in a user database.
This is a situation when we need to use EXECUTE AS CALLER to get back to get information about the actual user. Here is a modified version of the procedure:
ALTER PROCEDURE Management.ShowSessions2
WITH EXECUTE AS 'PROXY Playground.Management.ShowSessions2' AS
SELECT * FROM logintokeninfo
DECLARE @has_perm bit = 0
EXECUTE AS CALLER
IF (is_srvrolemember('PowerUsers') = 1 AND is_member('db_owner') = 1) OR
has_perms_by_name(NULL, NULL, 'CONTROL SERVER') = 1
SELECT @has_perm = 1
REVERT
IF @has_perm = 1
SELECT * FROM sys.dm_exec_sessions WHERE database_id = db_id()
ELSE
RAISERROR('You don''t have permission to run this procedure!', 16, 1)
We wanted to use EXECUTE AS, because it seemed simpler than certificates, but we had to start with first making the procedure more complex. But that's only the beginning. Before when you make the next attempt, make sure that you have multiple connections open to the Playground database:
EXECUTE AS LOGIN = 'SgtPepper' EXEC Management.ShowSessions2 go REVERT
No error message this time, but Sgt Pepper only sees one session. What if we try this ourselves? We are after all sysadmin, who could stop us?
EXEC Management.ShowSessions2
But the output is the same (for brevity I have abbreviated the name of the proxy login and reduced the result set from sys.dm_exec_sessions:
token_name type usage original_login SYSTEM_USER DBUSER
------------ ----------- ----------- ------------------ ------------ --------
PROXY ... SQL LOGIN DENY ONLY LIVERPOOL\GMartin PROXY ... PROXY ...
public SERVER ROLE DENY ONLY LIVERPOOL\GMartin PROXY ... PROXY ...
session_id login_time host_name
---------- ----------------------- --------------
54 2017-08-13 19:57:20.337 LIVERPOOL
The answer to the puzzle is in the usage column: it says DENY ONLY. That is, any granted permissions on server level do not apply, only explicit DENY. In the interlude chapter we looked at using an application login which impersonates the actual user with EXECUTE AS USER. In this case, we observed that the impersonation was only good in the current database, but not outside of it. And this is the same thing. As you may understand from the exercise with adding the proxy login as a user to the Playground database, the EXECUTE AS clause means that it is a database user that is being impersonated and not a server login.
When you impersonate a database user, you are sandboxed into the current database, you cannot access things outside of it, be that things on server level or in another database, even if the user you impersonate maps to a login with powers in the place you want to go. This is something that can take people aback. They add EXECUTE AS to a stored procedure, and find that they cannot do something on server level as they have intended. I'm sysadmin, so I should be able to anything on the server, they exclaim.
The full rationale for this sandboxing will be evident from the next section, but already here I will point out how this sandboxing can work to your advantage. In the section Security Considerations, I discussed how a malicious user could create a procedure or a trigger which includes something like this:
IF is_srvrolemember('sysadmin') = 1
EXEC('USE master ALTER SERVER ROLE sysadmin ADD MEMBER SgtPepper')
And then they lure you as the server-level DBA to run that procedure or trigger and you will make them sysadmin without knowing. A very good place for this attack could be a DDL trigger and wait until the index-maintenance job runs.
To counteract this, in my script GrantPermsToSP_server.sql, I run all commands in the target database bracketed by EXECUTE AS USER = 'dbo' and REVERT. When I presented the script, it may not have been apparent why this works, but we know now: by impersonating a database user, we are sandboxed and lose all our rights on server level, and any attempt to exploit our server powers will fail.
Keep in mind that depending on what elevated users there are in the database, impersonating dbo may still incur a risk, as a developer with limited rights to create procedures and tables still can lure you run code that puts him into db_owner which could be bad enough.
The sandbox is not hermitically sealed. You can perform actions outside of it, but two doors need to be opened. One door leading out of the sandbox, and one door leading into the place where you want to go.
As you may recall, the Playground database is owned by sa, so the second door is already open. All you need to do is this:
ALTER DATABASE Playground SET TRUSTWORTHY ON
Note: To change the TRUSTWORTHY setting of a database, you must have the permission CONTROL SERVER. Beware that if you restore a copy of the database on a different server or on the same server with a different name, the restored database will not be marked as trustworthy.
Sgt Pepper tries again:
EXECUTE AS LOGIN = 'SgtPepper' EXEC Management.ShowSessions2 go REVERT
Success! All connections to Playground are displayed (I'm leaving out some columns for brevity):
token_name type usage
------------------------------------------ ------------ --------------
PROXY Playground.Management.ShowSessions2 SQL LOGIN GRANT OR DENY
public SERVER ROLE GRANT OR DENY
sa SQL LOGIN AUTHENTICATOR
session_id login_time host_name
---------- ----------------------- -----------
51 2017-08-12 17:49:39.307 LIVERPOOL
53 2017-08-13 16:26:45.833 LIVERPOOL
54 2017-08-13 19:57:20.337 LIVERPOOL
If we look at the contents from the logintokeninfo view, we can see that the usage column now reads GRANT OR DENY. Furthermore, there is a third row with sa as the token name and with the usage AUTHENTICATOR. This type of token is added when a user is impersonated on database level and the database is set as TRUSTWORTHY. The purpose of the AUTHENTICATOR token is to authenticate the impersonated user on server level, and for this to happen, the token must carry the permission AUTHENTICATE SERVER. (Or if the access is to another database, AUTHENTICATE permission in that database.)
That may sound a little abstract, and it is not really important to understand the exact mechanism. A simplified description could be: if the database owner has the appropriate permissions, it vouches for the other tokens on server level, and that is why the login tokens now have GRANT OR DENY and thus their permissions are active on server level.
Sgt Pepper was able to run ShowSessions with EXECUTE AS + TRUSTWORTHY. Does this mean that everything is OK then? We have achieved a solution which is much simpler than certificate signing? Simpler maybe – but definitely not OK.
First of all, think in terms of separation of duties. We wanted Sgt Pepper to be able to see all connections to the Playground database, but he must not see connections to other databases, as that could disclose login names he should not see. We achieved this with certificates, because Sgt Pepper cannot change the procedure with less than the signature disappearing. So after every change, he has to come to you as the DBA to have the procedure signed again, and you would only agree to do this as long as you can ascertain that the Sergeant stays within his database. With the solution we have now, Sgt Pepper has gotten carte blanche to change the procedure as he sees fit. For instance, he can remove the filter on database_id to see all connections in the server. Exactly what we wanted to avoid.
But that is not all. For what follows, I will not use EXECUTE AS LOGIN to play Sgt Pepper, but to make it perfectly clear of what is going on and to remove any doubts, you should login directly as Sgt Pepper (the password is Lonely Hearts Club Band). Do this by right-clicking in the query window in Management Studio and select Change Connection to switch forth and back between Sgt Pepper and yourself. This is also called out in the accompanying script file. Connected as Sgt Pepper run:
USE Playground
go
SELECT is_srvrolemember('sysadmin') AS Sysadmin, * FROM logintokeninfo
The output is:
Sysadmin token_name type usage original_login SYSTEM_USER DBUSER
-------- ---------- ----------- ------------- -------------- ----------- ---------
0 SgtPepper SQL LOGIN GRANT OR DENY SgtPepper SgtPepper SgtPepper
0 public SERVER ROLE GRANT OR DENY SgtPepper SgtPepper SgtPepper
0 PowerUsers SERVER ROLE GRANT OR DENY SgtPepper SgtPepper SgtPepper
There is nothing remarkable about this. Sgt Pepper is logged in as himself, and he is a member of public and PowerUsers. He is not member of sysadmin. But look what happens now:
EXECUTE AS USER = 'dbo'
SELECT is_srvrolemember('sysadmin') AS Sysadmin, * FROM logintokeninfo
--DBCC TRACEON(4199)
REVERT
Because Sgt Pepper is db_owner, he has the permission to do anything inside the database. For instance, if he feels like he can impersonate dbo. Look at the output:
Sysadmin token_name type usage original_login SYSTEM_USER DBUSER
-------- ---------- ----------- ------------- -------------- ----------- ------
1 sa SQL LOGIN GRANT OR DENY SgtPepper sa dbo
1 public SERVER ROLE GRANT OR DENY SgtPepper sa dbo
1 sysadmin SERVER ROLE GRANT OR DENY SgtPepper sa dbo
1 sa SQL LOGIN AUTHENTICATOR SgtPepper sa dbo
Sgt Pepper is now sysadmin and is taking over the server! If you think this is fake, you can try running the DBCC command which requires membership in sysadmin and it runs without error. (This particular trace flag enables optimizer fixes, and it is actually often a good idea to have it on).
This is a classical case of privilege elevation, and it happened because sa owns the database, and we just too casually set the database TRUSTWORTHY. As I said, initially, it is not good practice to have sa as the database owner. Yet, this is by no means uncommon. One reason for this is that many organisations have learnt the hard way that having a real person as the database owner can lead to trouble. One day that person gets the sack and is thrown out of the AD with a wink of the eye, causing problems on the server instances where he owns databases. (In a large organisation, the DBA may not even be told that this person has been shown the door.) So to avoid this, many DBAs prefer to have an impersonal database owner that cannot disappear, and since sa fulfils that criteria and is always around, it seems like good pick. However, in my opinion it is not. I would like to submit that each database should be owned by a unique SQL login which has no permissions whatsoever and which exists solely for the purpose of owning that database. To make management easier, the name of the SQL login should be tied to the database. Change the connection back to yourself and fix this for Playground:
USE master
go
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN Playground$owner ' +
'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN Playground$owner DISABLE
DENY CONNECT SQL TO Playground$owner
ALTER AUTHORIZATION ON DATABASE::Playground TO Playground$owner
As with the proxy login we created earlier, we work hard to make it difficult to log in with this login. As for the naming convention, feel free to pick your own.
Note: A side effect of this is that when you restore the database to a different server, you must remember to change the database owner to the login Playground$owner on that server, since SQL logins typically have different SID on different servers. Else there will be a mismatch between the owner SID in sys.databases on server level, and the SID for dbo in sys.database_principals, which is likely to cause confusion at some point. A way to avoid this is to use the SID option with CREATE LOGIN to get the same SID on all servers for logins like Playground$owner.
Now change the connection to become Sgt Pepper and try the stunt again:
USE Playground
EXECUTE AS USER = 'dbo'
SELECT is_srvrolemember('sysadmin') AS Sysadmin, * FROM logintokeninfo
go
REVERT
The output (split up over two lines for legibility):
Sysadmin token_name type usage
-------- ---------------- ------------ -------------
0 Playground$owner SQL LOGIN DENY ONLY
0 public SERVER ROLE DENY ONLY
0 Playground$owner SQL LOGIN AUTHENTICATOR
original_login SYSTEM_USER DBUSER
-------------- ---------------- ------
SgtPepper Playground$owner dbo
SgtPepper Playground$owner dbo
SgtPepper Playground$owner dbo
This time Sgt Pepper was not able to elevate to sysadmin, because dbo is now the humble Playground$owner. Alas, when Sgt Pepper runs ShowSessions2:
EXEC Management.ShowSessions2
He only sees his own connection. And this is not surprising, given the output above. The usage column reads DENY ONLY. We see Playground$owner listed as AUTHENTICATOR, but it has not been entrusted to authenticate its database users. To this end, it needs to have the permission AUTHENTICATE SERVER. Change the connection to yourself again and run:
USE master GRANT AUTHENTICATE SERVER TO Playground$owner
Then change the connection back to Sgt Pepper and try ShowSessions2 again:
USE Playground EXEC Management.ShowSessions2
Success! Sgt Pepper can again see all databases. He tries the dbo stunt once more:
USE Playground
EXECUTE AS USER = 'dbo'
SELECT is_srvrolemember('sysadmin') AS Sysadmin, * FROM logintokeninfo
go
REVERT
The output this time:
Sysadmin token_name type usage
----------- ---------------- ----------- -------------
0 Playground$owner SQL LOGIN GRANT OR DENY
0 public SERVER ROLE GRANT OR DENY
0 Playground$owner SQL LOGIN AUTHENTICATOR
Good! He is not sysadmin. So, is the privilege-elevation hole tightened? Oh, no. Sgt Pepper has more tricks up his sleeve. In the below, replace LIVERPOOL\GMartin with your credentials:
CREATE USER [LIVERPOOL\GMartin]
EXECUTE AS USER = 'LIVERPOOL\GMartin'
SELECT is_srvrolemember('sysadmin') AS Sysadmin, * FROM logintokeninfo
go
REVERT
Sgt Pepper knows that GMartin is sysadmin, and he creates a user for him in his database, something he has all permissions to do and then he impersonates GMartin. This is the output (broken up on multiple lines for legibility):
Sysadmin token_name type usage
-------- ----------------- ------------- --------------
1 LIVERPOOL\GMartin WINDOWS LOGIN GRANT OR DENY
1 public SERVER ROLE GRANT OR DENY
1 sysadmin SERVER ROLE GRANT OR DENY
1 Playground$owner SQL LOGIN AUTHENTICATOR
original_login SYSTEM_USER DBUSER
-------------- ----------------- ------
SgtPepper LIVERPOOL\GMartin dbo
SgtPepper LIVERPOOL\GMartin dbo
SgtPepper LIVERPOOL\GMartin dbo
SgtPepper LIVERPOOL\GMartin dbo
Sgt Pepper was once more able to elevate to sysadmin. And unless auditing on the server is based on original_login(), risk is considerable that GMartin will get the blame for whatever the Sergeant does. Before we move on, change the connection back to be yourself.
As you have seen, making a database TRUSTWORTHY implies a big security risk. If you make a database TRUSTWORTHY and grant the database owner AUTHENTICATE SERVER, any user in that database who has the permission to create and impersonate users can elevate to sysadmin.
When I suggest that a database should be owned by a non-privileged SQL login, this is an important reason. If the database is owned by sa, a DBA who is in a rush between different tasks can be lured to set a database TRUSTWORTHY, because it sounds so ...innocent. But if he also suggested that he needs to grant a permission, I like to imagine that the gut reaction of most DBAs is that this is maybe something that should not be done casually.
Note: antother situation where you as a DBA might be told that you need to set the database trustworthy is when a developer has an unsafe assembly and has not been able to (read: hasn't care to) sign the assembly with a key. In this case, the database owner needs to be granted permission UNSAFE ASSEMBLY. This has the same consequence: users in that database who have permissions to create assemblies can now elevate to sysadmin, although it takes more skill to exploit than AUTHENTICATE SERVER. In the appendix there is a chapter Loading CLR Objects which discusses loading of assemblies in more detail.
What we have seen explains why we are sandboxed when we use EXECUTE AS USER, be that as a statement or a clause. That is, without the sandbox, users with permissions to create and impersonate users can create a user for some with server-level permission and impersonate that person to elevate their own permissions. Or put in another words: the impersonation of a user should in the normal case only be valid in the realm where the impersonation occurs.
All this said, TRUSTWORTHY is only a security risk if there is a separation of duties between server and database level. On a server which is dedicated to an important tier-one application, there may be no specific users who have elevated permissions on database level only, because the server-level DBAs perform both the server-level and the database-level administration. That is, there is no one who can elevate his permissions. Yet, if you are at such a site and server and consider to make the database TRUSTWORTHY, ask yourself if you believe that this will continue to be true. What if you one day bring in a consultant to work with performance tuning that you make db_owner and grant server permissions needed for monitoring, but you do not want make the consultant sysadmin?
...and meanwhile, Michelle is exercising her procedure-writing skills again:
USE Playground
go
EXECUTE AS USER = 'Michelle'
go
CREATE PROCEDURE #elevate AS
EXECUTE AS USER = 'LIVERPOOL\GMartin'
CREATE LOGIN Maxwell WITH PASSWORD = 'SilverHammer'
ALTER SERVER ROLE sysadmin ADD MEMBER Maxwell
REVERT
go
EXEC dynamic_playdata2 'zz'' EXEC #elevate --'
go
DROP PROCEDURE #elevate
go
REVERT
DROP LOGIN Maxwell
Not bad of a user without login to elevate to sysadmin!
So that is one more thing to check before you make the database TRUSTWORTHY – no procedures with EXECUTE AS OWNER and SQL injection holes.
It should not come as a surprise that I conclude this section with a strong recommendation against using EXECUTE AS to package server-level permissions. While I prefer certificates for packaging of database permissions, I think EXECUTE AS still is acceptable on this level, if you understand and handle the ramifications for auditing and row-level security. But for server-level permissions, always, always use certificate signing.
Before we move on, turn off the TRUSTWORTHY setting for Playground.
ALTER DATABASE Playground SET TRUSTWORTHY OFF
Despite the horrors in the previous section, there is one situation where EXECUTE AS is a fully satisfactory solution and to the degree that it stands out as a the only reasonable choice: server-level triggers that needs elevated permissions. Since they are server-level objects, the EXECUTE AS clause now implies EXECUTE AS LOGIN, so there is no sandboxing and no need for TRUSTWORTHY. And since such triggers are quite confined in what they do, using certificate signing just seems like overkill. (Not the least since because of a disconnect in the syntax, it is not possible to sign server triggers!)
This section has a separate script, 08_logintri.sql.
There are two types of server-level triggers: DDL triggers and logon triggers. We will look at implementing a login trigger that logs all logins to a table in a dedicated database.
WARNING! Before you start this exercise, by all means check that you don't already have a logon trigger on your instance. If you have, you may want to do this exercise on a different instance. Also, since something could go wrong with the logon trigger, don't do this exercise on a server with other users – they may not like being locked out!
First we create a database PlayAudit, and in accordance to what we have said earlier, we create a unique login to be the owner of this database.
USE master
go
CREATE DATABASE PlayAudit
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN PlayAudit$owner ' +
'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN PlayAudit$owner DISABLE
DENY CONNECT SQL TO PlayAudit$owner
ALTER AUTHORIZATION ON DATABASE::PlayAudit TO PlayAudit$owner
Next, we create a table to audit all logins on the server in the PlayAudit database:
USE PlayAudit
go
CREATE TABLE loginaudit (
auditid bigint NOT NULL IDENTITY,
spid int NOT NULL
CONSTRAINT def_loginaudit_spid DEFAULT @@spid,
username sysname NOT NULL
CONSTRAINT def_loginaudit_username DEFAULT original_login(),
logintime datetime2(3) NOT NULL
CONSTRAINT def_loginaudit_logintime DEFAULT sysdatetime(),
appname sysname NULL
CONSTRAINT def_loginaudit_appname DEFAULT app_name(),
hostname sysname NULL
CONSTRAINT def_loginaudit_hostname DEFAULT host_name(),
client_host varchar(48) NULL
CONSTRAINT def_loginaudit_client_host DEFAULT
eventdata().value('(/EVENT_INSTANCE/ClientHost/text())[1]', 'varchar(48)'),
is_pooled bit NULL
CONSTRAINT def_loginaudit_is_pooled DEFAULT
eventdata().value('(/EVENT_INSTANCE/IsPooled/text())[1]', 'bit'),
-- Extra demo columns!
token_name sysname NOT NULL,
token_type nvarchar(128) NOT NULL,
usage nvarchar(128) NOT NULL,
CONSTRAINT pk_loginaudit PRIMARY KEY(auditid)
)
The last three columns are from sys.login_token and we will capture them only for the sake for the demo; you would never have them in a regular login-auditing table.
All real auditing columns are defined with defaults, because we know what we want to capture. Some columns are defined as nullable, although we expect them to always have values. One thing to keep in mind here is that if any of the default expressions above would unexpectedly return NULL, do we want the login to succeed anyway? Or do we really want the login to fail? Since the application name and the host name come from the connection string, they are not entirely trustable anyway, so I let them slip. And I am not sure about the ClientHost element from eventdata, so I let it pass as well.
The column is_pooled indicates whether the connection was a brand-new connection or a connection that was re-initiated with sp_reset_connection, that is, reused from the API's connection pool.
The one problem to solve is how the logon trigger will be able to write to the table. We could enable the guest user in PlayAudit and grant guest INSERT permission on the table. But that is an extraordinarily bad idea, and sooner or later you will find Kilroy was here entries, as users could insert rows from SSMS to poke fun with you.
No, the permission to write to the table must be packaged into the trigger, and the table definition is already prepared for this with the use of original_login for the username column. EXECUTE AS serves this purpose well, and given the confined task it seems simplest to use the database owner in the EXECUTE AS clause, although according to the principle of granting minimum permission we should create a specific login. But there should not be any SQL-injection holes in a logon trigger, because there should not be any dynamic SQL at all in a logon trigger. (Logon triggers should be as simple as possible to avoid that they error out and thereby causing users to be locked out.)
Here is the trigger code:
USE master
go
CREATE TRIGGER logintri ON ALL SERVER WITH EXECUTE AS 'PlayAudit$owner' FOR LOGON AS
INSERT PlayAudit.dbo.loginaudit (token_name, token_type, usage)
-- DEFAULT VALUES
SELECT name, type, usage
FROM sys.login_token
The commented line is what you would use if we did not have those columns from sys.login_token for demo purposes.
Once you have the trigger in place, you can try logging in a few times, and if possible, also try from a different machine. Don't forget to test with an unprivileged user like Sgt Pepper. Once you have done this, run:
SELECT * FROM PlayAudit.dbo.loginaudit
I got this output when I tested. (I've broken up the columns in two segments for readability, and I have shortened the application name for Management Studio to save space):
auditid spid username logintime appname hostname
------- ---- ---------- ------------------------ ------------ -----------
1 51 SgtPepper 2017-08-16 22:10:50.312 SQLCMD RC2
2 51 SgtPepper 2017-08-16 22:10:50.312 SQLCMD RC2
3 51 SgtPepper 2017-08-16 22:10:58.117 SSMS - Query LIVERPOOL
4 51 SgtPepper 2017-08-16 22:10:58.117 SSMS - Query LIVERPOOL
5 53 SgtPepper 2017-08-16 22:10:58.152 SSMS - Query LIVERPOOL
6 53 SgtPepper 2017-08-16 22:10:58.152 SSMS - Query LIVERPOOL
client_host is_pooled token_name token_type usage
---------------- --------- ----------------- ------------ -------------
192.168.145.139 0 PlayAudit$owner SQL LOGIN GRANT OR DENY
192.168.145.139 0 public SERVER ROLE GRANT OR DENY
<local machine> 0 PlayAudit$owner SQL LOGIN GRANT OR DENY
<local machine> 0 public SERVER ROLE GRANT OR DENY
<local machine> 0 PlayAudit$owner SQL LOGIN GRANT OR DENY
<local machine> 0 public SERVER ROLE GRANT OR DENY
The most interesting columns in the context of this article are the last three. We can see that there are two server tokens: PlayAudit$owner and public. The usage for both is GRANT OR DENY, that is, there is no sandboxing, but the trigger can access the PlayAudit database.
Note: when you test by logging in from SSMS, you may be puzzled to see two connections directly after each other. SSMS makes an extra connection behind your back for IntelliSense, even if you have turned it off.
I would recommend that before you move on that you drop the logon trigger:
DROP TRIGGER logintri ON ALL SERVER
This section briefly covers how impersonation inside SQL Server affects the world outside of it.
Just like impersonation on database level is by default not trusted on server level, impersonation in SQL Server is not trusted in Windows and the world outside. The reason is obvious: a DBA should not be able to elevate his permissions to be Administrator in Windows by impersonating a Windows administrator. Some operations in SQL Server that interact with the outside world impersonates your login if you are logged in with integrated security. (Which is possible, since Windows has authenticated you and SQL Server got your token when you connected to SQL Server.) These operations will fail, if you have impersonated a Windows login inside SQL Server. One such example is linked servers, something that I discuss more in detail in the appendix, in the chapter Controlling Access to Linked Servers.
SQL Server MVP Adam Machanic made me aware of a case that relates to CLR modules. If your CLR module makes some external access outside SQL Server you may want to use the security context of the user who is currently logged into SQL Server, rather than the service account for SQL Server. This is only possible if the user is logged in with integrated authentication. To this end you can retrieve the object SqlContext.WindowsIdentity, which has a number of properties and methods, including one that permits you impersonate the user. But if there is an EXECUTE AS somewhere on the call stack, SqlContext.WindowsIdentity will return null in most cases. (There are some situations where it will return the identity of the service account for SQL Server, which you don't have much use for.)
The last area that we will look at this article is how to handle permissions for cross-database access. This is quite a complex area, because the scenarios where you want to access in a different database can be vastly different. Consider these two extremes:
Your real-world situation may fall somewhere in between or be more specific. There is not really any solution that is best for all scenarios. For this reason, we will look at five different options for handling permissions cross-database access:
Certificate signing is very well apt for the first scenario above, but you are likely to find it impractical in the second where ownership chaining and EXECUTE AS are better fits. The first option may be your preference for some scenarios that fall between the extremes. Enabling the guest user is the least recommendable option, but there are a few specialised scenarios where it makes sense.
The script for this chapter is in 09_crossdbaccess.sql.
For these exercises we need a new test user, one that has a server-level login, but who has no elevated permissions anywhere. Let me introduce Lovely Rita, a traffic warden by profession. We will create a login for her and make her a user in Playground and a member of Playrole:
SET NOCOUNT, XACT_ABORT ON USE master go CREATE LOGIN Rita WITH PASSWORD = 'MeterMaid' go USE Playground go CREATE USER Rita ALTER ROLE Playrole ADD MEMBER Rita
To test cross-database access, we also need to create another database. In fact, we will create one or more for most techniques we will look at. For this section we will work with the database DirectPlay and we create it according to the practice we have established. (You may sense that this is a perfect candidate for automation through a stored procedure or even better a PowerShell script you can run on any server.)
USE master
go
CREATE DATABASE DirectPlay
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN DirectPlay$owner ' +
'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN DirectPlay$owner DISABLE
DENY CONNECT SQL TO DirectPlay$owner
ALTER AUTHORIZATION ON DATABASE::DirectPlay TO DirectPlay$owner
We also need a table in this database to play with and while we're at it, we add a stored procedure to the database and add some data to the table.
USE DirectPlay
go
CREATE TABLE Directtable (id int NOT NULL,
somedata nvarchar(40) NOT NULL,
whodidit sysname NOT NULL
CONSTRAINT Direct_default_whodidit DEFAULT USER,
CONSTRAINT pk_Direct PRIMARY KEY (id)
)
go
CREATE PROCEDURE add_directdata @id int, @somedata nvarchar(40) AS
INSERT Directtable(id, somedata)
VALUES(@id, @somedata)
SELECT id, somedata, whodidit FROM Directtable WHERE id = @id
go
EXEC add_directdata 1, 'The Long and Winding Road'
We move over to Playground, where we make some attempts to permit Rita to work with Directtable.
USE Playground go CREATE PROCEDURE get_direct_data @id int AS SELECT id, somedata, whodidit FROM DirectPlay.dbo.Directtable WHERE id = @id go EXECUTE AS LOGIN = 'Rita' EXEC get_direct_data 1 go REVERT
This is the error message:
Msg 916, Level 14, State 1, Procedure get_direct_data, Line 2
The server principal "Rita" is not able to access the database "DirectPlay" under the current security context.
Which is not very surprising, since Rita is not a user in DirectPlay. As long we don't have any other arrangements in place, the users in Playground who need to access data in DirectPlay, must also be users in DirectPlay, so let's address that.
USE DirectPlay go CREATE ROLE PlaygroundUsers CREATE USER Rita ALTER ROLE PlaygroundUsers ADD MEMBER Rita
As it can be practical to know which users in DirectPlay that are guests from Playground, we create a role for them, and this also permits us to tailor the permissions for these people. We add Rita to the role, and she makes a new attempt:
USE Playground go EXECUTE AS LOGIN = 'Rita' EXEC get_direct_data 1 go REVERT
There is no success, but the error message is different:
Msg 229, Level 14, State 5, Procedure get_direct_data, Line 2
The SELECT permission was denied on the object 'Directtable', database 'DirectPlay', schema 'dbo'.
There is no ownership chaining, so there is a permission check, and since PlaygroundUsers has not been granted any permission, the check fails.
To overcome this, we would have to grant SELECT permission on some level to PlaygroundUsers. But wait a minute! Assume that DirectPlay is like Playground, that is, the regular users of DirectPlay only get EXECUTE permissions on the dbo schema. Why would we all of a sudden grant these Playground people extra permissions? After all, there is no guarantee that they will always come in from Playground – there is nothing to stop them from connecting directly to DirectPlay.
That is, if the Playground application needs to access DirectPlay, that should preferably be through stored procedures. And to keep the users of Playground who are not regular users of the DirectPlay application out from the rest of the DirectPlay, we should put these stored procedures in a separate schema:
USE DirectPlay go CREATE SCHEMA PlaygroundGuests go GRANT EXECUTE ON SCHEMA::PlaygroundGuests TO PlaygroundUsers go CREATE PROCEDURE PlaygroundGuests.get_direct_data @id int AS SELECT id, somedata, whodidit FROM dbo.Directtable WHERE id = @id
In the Playground database, you change the get_direct_data procedure to read:
USE Playground go ALTER PROCEDURE get_direct_data @id int AS EXEC DirectPlay.PlaygroundGuests.get_direct_data @idRita can now run the procedure successfully:
EXECUTE AS LOGIN = 'Rita' EXEC get_direct_data 1 go REVERT
Let's now assess this solution. We can see that there are two potential pain points:
I say "potential", because it depends on the actual situation. When it comes to the first point, it could be that people generally are users of both applications, and in that case there is no point with the extra PlaygroundGuests schema. Or the case might that only one or two of the users in Playground need to run the procedure in DirectPlay.
When it comes to the second point, you may be in the lucky situation where granting SELECT permission to PlaygroundGuests is entirely permissible. This way the problem vanishes, at least as long as there is no need for PlaygroundGuests to update data in DirectPlay with data from Playground. There may also be middle grounds, where DirectPlay data is exposed through views or table-valued functions in a controlled way and PlaygroundUsers are granted access on these views. Or it may be feasible to capture the data from DirectPlay in OUTPUT parameters or in a temp table with INSERT-EXEC and use that for further processing.
But you can definitely run into situations where not being able to join tables in the two databases freely is a serious limitation. In that case, you need to find something else than this simple-minded solution. The next solution has the same issue, but the last three remove the second pain point, as they all in their own way permit you to freely write joins across the databases within a stored procedure without granting direct access to the tables.
This can be seen as a variation of the previous solution. Instead of adding users from the source database as users in the target database, you simply say:
GRANT CONNECT TO guest
Now anyone on the server can access the database, and if they don't have a user of their own in the database, they will map to the guest user and any permissions they need you have to grant to guest.
While this relieves you of having to administer users, it also deprives you of any control who can access the database, so you should only do this if what you really want is "anyone on the server should be able to access this database". A possible case is a database with reference data that freely can be used from any application.
A more elaborate scenario is a server that hosts a multi-tenant application with one database per tenant. The tenant users can only access their databases through a web UI and they have no access to SQL Server directly. The application has a common database with not only reference data, but also common utility stored procedures / functions and tables where you record billing or telemetry information. The tenants administer their users themselves through the web UI, but you don't want to have all the tenant users in that common database. A simple solution is to enable the guest user and be done with it. I will return to this scenario in the last section of this chapter for a different approach.
But apart from a few special situations, enabling guest is definitely considered bad practice and nothing you should do, because normally, you do want to control who can access a certain database. That said, of the four system databases, three – master, tempdb and msdb – all have guest enabled, and you would cause yourself some problems if you disabled guest in any of them. (And, in fact, SQL Server will not even permit you when it comes to master and tempdb.)
Let's now look at how certificate signing can be used to permit cross-database access. Before we look at the theory, let's set up a new database to test in. The initial setup is the same as for DirectPlay. That is, we have a dedicated owner, and there is a table with a stored procedure to add data to it. Note that no users are added.
USE master
go
CREATE DATABASE PlaySign
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN PlaySign$owner ' +
'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN PlaySign$owner DISABLE
DENY CONNECT SQL TO PlaySign$owner
ALTER AUTHORIZATION ON DATABASE::PlaySign TO PlaySign$owner
go
USE PlaySign
go
CREATE TABLE Signtable (id int NOT NULL,
somedata nvarchar(40) NOT NULL,
whodidit sysname NOT NULL
CONSTRAINT Sign_default_whodidit DEFAULT USER,
CONSTRAINT pk_Sign PRIMARY KEY (id)
)
go
CREATE PROCEDURE add_signdata @id int, @somedata nvarchar(40) AS
INSERT Signtable(id, somedata)
VALUES(@id, @somedata)
SELECT id, somedata, whodidit FROM Signtable WHERE id = @id
go
EXEC add_signdata 1, 'Back in the USSR'
Say now that we want to permit users in the Playground database to add data to Signtable through a stored procedure in the Playground database. We want them to use a certain id range that we have set aside for them, and we enforce that in the procedure below:
USE Playground
go
CREATE PROCEDURE add_signdata @id int, @somedata nvarchar(40) AS
DECLARE @low int = 20000,
@high int = 15000
IF @id BETWEEN @high AND @low
BEGIN
INSERT PlaySign.dbo.Signtable(id, somedata)
VALUES(@id, @somedata)
SELECT id, somedata, whodidit
FROM PlaySign.dbo.Signtable
WHERE id = @id
END
ELSE
RAISERROR('Illegal id. The permitted range is %d - %d', 16, 1, @low, @high)
The recipe for packaging cross-database access in a stored procedure with certificate signing is very similar to when packaging server-level permissions. That is:
Since we have looked at this step by step in the past, I take the liberty to run all steps in one go this time. The statement to drop the private key is commented out for the simple reason that we will change and re-sign the procedure in an instant. In a real-world situation you should absolutely drop the private key.
USE Playground
go
CREATE CERTIFICATE [SIGN add_signdata for PlaySign]
ENCRYPTION BY PASSWORD = 'Lucy in the Sky with Diamonds'
WITH SUBJECT = '"GRANT INSERT, SELECT ON Signtable"'
go
ADD SIGNATURE TO dbo.add_signdata BY CERTIFICATE [SIGN add_signdata for PlaySign]
WITH PASSWORD = 'Lucy in the Sky with Diamonds'
go
--ALTER CERTIFICATE [SIGN add_signdata for PlaySign] REMOVE PRIVATE KEY
go
DECLARE @cert_id int = cert_id('[SIGN add_signdata for PlaySign]')
DECLARE @public_key varbinary(MAX) = certencoded(@cert_id)
DECLARE @sql nvarchar(MAX) =
'CREATE CERTIFICATE [SIGN Playground.dbo.add_signdata]
FROM BINARY = ' + convert(varchar(MAX), @public_key, 1)
PRINT @sql
USE PlaySign
EXEC (@sql)
go
CREATE USER [SIGN Playground.dbo.add_signdata]
FROM CERTIFICATE [SIGN Playground.dbo.add_signdata]
GRANT INSERT, SELECT ON dbo.Signtable TO [SIGN Playground.dbo.add_signdata]
You may note here that I have opted to name the certificate differently in the two databases in order to convey the relevant information to the respective power user in the databases. Recall that what identifies a certificate is not the name, but the thumbprint.
Rita gives it a try:
USE Playground go EXECUTE AS LOGIN = 'Rita' EXEC add_signdata 15001, 'You have a parking ticket!' go REVERT
(Yes, she is lovely, didn't I say that?) Success, the row is inserted and she can read the inserted row.
id somedata whodidit
------- ---------------------------- -----------
15001 You have a parking ticket! Rita
And note here: Rita has no permission on Signtable – in fact, she is not even added as a user in the PlaySign database. How did this work out? Let's look at the user tokens, in both databases:
USE Playground
go
ALTER PROCEDURE add_signdata @id int, @somedata nvarchar(40) AS
DECLARE @low int = 20000,
@high int = 15000
SELECT name, type, usage FROM sys.user_token
SELECT name, type, usage FROM PlaySign.sys.user_token
IF @id BETWEEN @high AND @low
BEGIN
INSERT PlaySign.dbo.Signtable(id, somedata)
VALUES(@id, @somedata)
SELECT id, somedata, whodidit
FROM PlaySign.dbo.Signtable
WHERE id = @id
END
ELSE
RAISERROR('Illegal id. The permitted range is %d - %d', 16, 1, @low, @high)
Let's first see what we have before we add any signature to the procedure:
EXECUTE AS LOGIN = 'Rita' EXEC add_signdata 15002, 'No, Sir, you cannot park here' go REVERT
This is the output:
name type usage
-------------- ---------- --------------
Rita SQL USER GRANT OR DENY
public ROLE GRANT OR DENY
Playrole ROLE GRANT OR DENY
Msg 916, Level 14, State 1, Procedure add_signdata, Line 6
The server principal "Rita" is not able to access the database "PlaySign" under the current security context.
You are probably not surprised to see that Rita cannot see user tokens in a database she does not have access to. Now, let's sign the procedure and try again:
ADD SIGNATURE TO dbo.add_signdata BY CERTIFICATE [SIGN add_signdata for PlaySign] WITH PASSWORD = 'Lucy in the Sky with Diamonds' go EXECUTE AS LOGIN = 'Rita' EXEC add_signdata 15002, 'No, Sir, you cannot park here' go REVERT
This is the output:
name type usage
---------------------------------- -------------------------- -------------
Rita SQL USER GRANT OR DENY
public ROLE GRANT OR DENY
Playrole ROLE GRANT OR DENY
name type usage
---------------------------------- --------------------------- -------------
public ROLE GRANT OR DENY
SIGN Playground.dbo.add_signdata USER MAPPED TO CERTIFICATE GRANT OR DENY
id somedata whodidit
----------- ------------------------------- -----------
15002 No, Sir, you cannot park here Rita
You can see that the tokens from Playground are the same as when the procedure was unsigned. (There is no extra token related to the certificate, since there is no user mapped to the certificate in Playground.) Now we also see two tokens from PlaySign. There is the omnipresent public role and then there is the certificate user which contributes with three permissions. That is, beyond the INSERT and SELECT permissions that we granted explicitly, it also has CONNECT permission on the database as this is automatically granted when you create a user of any type with CREATE USER. You can verify this by revoking that permission and try again:
USE PlaySign go REVOKE CONNECT FROM [SIGN Playground.dbo.add_signdata] go USE Playground go EXECUTE AS LOGIN = 'Rita' EXEC add_signdata 15003, 'When I am free to take some tea with you???' go REVERT
The error message is now the same as when the procedure was not signed at all.
An interesting property of this solution is that Signe and Sgt Pepper can make these arrangements on their own with involving the server-level DBA. This is not true for the two remaining solutions. However, there is a difference if they are to run this on their own. To wit, if the same person is the master of both databases (being the server-level DBA or the power user of both), that person can run the script above as a single unit. (If you are the server-level DBA you may want to make the same precautions that I did in GrantServerToSP_server.sql and I discussed in the section Security Considerations to protect yourself against malicious local power users.)
But this would not be possible for Signe and Sgt Pepper, because they are power users in their respective databases only, and may not even have access to the other database themselves. Sgt Pepper would have to run the script up to the statement PRINT @sql and then somehow transfer the output to Signe who can run the generated statement in her database, followed by the CREATE USER and GRANT statements. A further complication is that Signe may not trust Sgt Pepper not to cheat on her. She can sit beside him and make sure that the private key is really dropped, but as I discussed chapter 5, the Sergeant may add a DDL trigger to his database to roll back that statement. Signe can make precautions to prevent this, insisting on viewing the certificate in sys.certificates and also require that the script uses a random GUID for the password that is never displayed. Then again, Signe and Sgt Pepper may be in different offices in different cities, and she could only monitor him through remote desktop – does that feel secure to you?
There is a solution to avoid this. It's more complicated, but it has the advantage that the private key only lives in PlaySign and never appears in Playground. In passing, I will note that the technique could also be applied for server-level permissions. Before we explore this solution, let's get rid of the current set of certificates, signature and user:
USE Playground go DROP SIGNATURE FROM add_signdata BY CERTIFICATE [SIGN add_signdata for PlaySign] DROP CERTIFICATE [SIGN add_signdata for PlaySign] go USE PlaySign go DROP USER [SIGN Playground.dbo.add_signdata] DROP CERTIFICATE [SIGN Playground.dbo.add_signdata]
Next, we create the certificate, the user and grant permissions in PlaySign as before – except that there is now a private key for the certificate:
USE PlaySign
go
CREATE CERTIFICATE [SIGN Playground.dbo.add_signdata]
ENCRYPTION BY PASSWORD = 'Lucy in the Sky with Diamonds'
WITH SUBJECT = '"GRANT INSERT, SELECT ON SignTable"'
go
CREATE USER [SIGN Playground.dbo.add_signdata]
FROM CERTIFICATE [SIGN Playground.dbo.add_signdata]
GRANT INSERT, SELECT ON dbo.Signtable TO [SIGN Playground.dbo.add_signdata]
Now to the solution. You may recall that there is a form of ADD SIGNATURE that permits us to add a pre-calculated signature. So Signe could create Sgt Pepper's procedure in her database, sign the procedure and then extract the signature from sys.crypt_properties. This is somewhat error-prone, since she needs to gets a perfectly faithful copy of the procedure, including leading and trailing blank lines. Another alternative is to get the procedure code from sys.sql_modules in Playground and use the built-in function signbycert to compute a signature. This requires that Sgt Pepper grants Signe access to Playground and at least VIEW DEFINITION on the procedure, but Signe has all right to request that any way so that she can review what she is about to sign.
Here is script for this latter option. When you run it, you can run it all at once, and it has to be a single batch because of the scope of the variables.
USE PlaySign
go
DECLARE @cert_id int = cert_id('[SIGN Playground.dbo.add_signdata]'),
@public_key varbinary(MAX),
@signature varbinary(8000),
@certsql nvarchar(MAX),
@signsql nvarchar(MAX)
SELECT @public_key = certencoded(@cert_id)
SELECT @certsql = 'CREATE CERTIFICATE [SIGN add_signdata for PlaySign]
FROM BINARY = ' + convert(varchar(MAX), @public_key, 1)
SELECT @signature = signbycert (@cert_id, definition,
N'Lucy in the Sky with Diamonds')
FROM Playground.sys.objects o
JOIN Playground.sys.sql_modules sm ON o.object_id = sm.object_id
WHERE o.name = 'add_signdata'
AND o.schema_id = 1
SELECT @signsql = 'ADD SIGNATURE TO dbo.add_signdata
BY CERTIFICATE [SIGN add_signdata for PlaySign]
WITH SIGNATURE = ' + convert(varchar(MAX), @signature, 1)
PRINT @certsql
PRINT @signsql
USE Playground
EXEC(@certsql)
EXEC(@signsql)
When Signe and Sgt Pepper collaborate, Signe would run the script up to PRINT @signsql, and then she would send the Sergeant the contents of @certsql and @signsql and he can then run them at his leisure. If he wishes, he can change the name of the certificate. That is not an issue for Signe, and nor is it really any business of hers.
Let's test that the solution works:
USE Playground go EXECUTE AS LOGIN = 'Rita' EXEC add_signdata 15004, 'Sit on the sofa with a sister or two' go REVERT
While this solution is more secure than the general recipe where we start with creating the certificate in the database where the procedure resides, I need to add the caveat that I don't think it will work in all situations. Two cases come to mind:
I am not sure that these are the only two exceptions.
We will look at one more variation. To be able to do that, let's remove the signature, the certificate and the user again:
USE Playground go DROP SIGNATURE FROM add_signdata BY CERTIFICATE [SIGN add_signdata for PlaySign] DROP CERTIFICATE [SIGN add_signdata for PlaySign] go USE PlaySign go DROP USER [SIGN Playground.dbo.add_signdata] DROP CERTIFICATE [SIGN Playground.dbo.add_signdata]
When I presented this session on SQL Friday some time back, the host, my MVP colleague Magnus Ahlkvist, asked if we can create a database user from a login created from a certificate to arrange for cross-database access. That thought had not occurred to me, but it turns out that this is indeed possible. To see this in action, first open GrantPermsToSP_server.sql, and set up the parameters like this:
-- Set up parameters: the procedure to sign and the database it belongs to.
DECLARE @database nvarchar(260) = 'Playground',
@procname nvarchar(520) = 'dbo.add_signdata'
-- The permissions to grant through the certificate. Leave table empty
-- to only remove current permissions.
DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
INSERT @perms VALUES
('CONNECT SQL')
Here we are packaging the permission CONNECT SQL with add_signdata, but it only serves as a dummy, so that the script will actually sign the procedure. (If we leave @perms empty, the script will only remove existing signature etc, but not create any certificate and signature.) CONNECT SQL in itself is not particularly useful to package with a stored procedure, since without this permission you cannot connect the instance at all.
Once we have executed the script, we can run this in the PlaySign database to create a database and grant the required permissions:
USE PlaySign go CREATE USER "SIGN [Playground].[dbo].[add_signdata]" GRANT INSERT, SELECT ON dbo.Signtable TO "SIGN [Playground].[dbo].[add_signdata]"
Rita makes a test:
USE Playground go EXECUTE AS LOGIN = 'Rita' EXEC add_signdata 15005, 'Me, looking older because I have a cap?' go REVERT
This completes successfully.
Whether there is a compelling use case for this last variation, I am not certain. But I thought that I should share it, since some readers may find use for it. If nothing else, as the example shows, it permits you to be a little lazy and let GrantServerToSP_script.sql to do most of the work for you.
In any case, no matter which of the three solutions you apply, certificate signing is a very palatable method in the case you have an occasional access between databases that are part of different applications, and of which the user base is largely disjunct. The access from Playground can be constrained to procedures that have been signed with certificates from PlaySign and where each certificate user has been granted the exact permissions needed for the procedure in question.
On the other hand, if you have a multi-database application where there are cross queries all over the place, it is likely that you will find certificates to be a bit too much hassle. Even if you can mitigate the situation with good automation, it will have to be admitted that certificates are good for the few and exceptional cases, but if you need to sign everything to handle permissions it gets a little out of hand.
Note: My MVP colleague Uwe Ricken ran into an interesting problem. He had followed the recipe above, and yet he got the error message that Rita could not access the other database. It turned out that his in his case, the table being accessed had a column of the hierarchyid data type. This is a built-in type that is implemented in the CLR. He found that he could get his procedure to work if he added Rita as a user in PlaySign – and interesting enough, it kept on working if he later dropped her as a user. The answer to this puzzle is that during compilation SQL Server is apparently making some extra access for CLR data types which requires that the user has access to the database. Since this occurs during compilation, the token for the certificate user has not yet been added, and thus there is a permission error. The workaround for this issue is to add a wrapper procedure that calls the actual procedure, and sign both the wrapper and the real procedure with the certificate.
Let it be said directly: this method opens a security hole that permits a local power user to elevate his permission to sysadmin, although the way to do it is a little more complicated and roundabout than when the database is set to TRUSTWORTHY. There may be a way to close the hole, but it is of dubious nature and may cause other things to break. More about later. I will first talk about the method as such.
By default, ownership chaining does not work across databases, but it can be enabled. There are two ways to do this. You can enable it for the entire server with the configuration parameter cross db ownership chaining, and then it applies to all databases. I recommend against using this option. It is much better to enable cross-database chaining only for the databases that need it, and this is what we will work with in this section.
To play with ownership chaining, we create two databases, but there is one difference from how we have created databases previously:
USE master
go
CREATE DATABASE PlayChain1
CREATE DATABASE PlayChain2
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN PlayChain$owner ' +
'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN PlayChain$owner DISABLE
DENY CONNECT SQL TO PlayChain$owner
ALTER AUTHORIZATION ON DATABASE::PlayChain1 TO PlayChain$owner
ALTER AUTHORIZATION ON DATABASE::PlayChain2 TO PlayChain$owner
We have established the principle that each database should be owned by a SQL login, existing to own only that database. However, if you have a multi-database application, it can make sense to have the same owner for all databases that belong to the application. Even more so, if you wish to employ ownership chaining across the databases.
Next, we set things up in the databases. Rita needs to be present in both databases, this is a requirement when you rely on ownership chaining. (But since the assumption is that the databases are for the same application, this is something you would most likely have to do anyway.) We create a table in one of the databases, and a stored procedure to access the table in the other database:
USE PlayChain1
go
CREATE USER Rita
go
CREATE TABLE Chaintable (id int NOT NULL,
somedata nvarchar(40) NOT NULL,
whodidit sysname NOT NULL
CONSTRAINT Chain_default_whodidit DEFAULT USER,
CONSTRAINT pk_Chain PRIMARY KEY (id)
)
go
USE PlayChain2
go
CREATE USER Rita
CREATE ROLE ChainUsers
ALTER ROLE ChainUsers ADD MEMBER Rita
GRANT EXECUTE ON SCHEMA::dbo TO ChainUsers
go
CREATE PROCEDURE add_chaindata @id int, @somedata nvarchar(40) AS
INSERT PlayChain1.dbo.Chaintable(id, somedata)
VALUES(@id, @somedata)
SELECT id, somedata, whodidit FROM PlayChain1.dbo.Chaintable WHERE id = @id
You may note that in PlayChain1, we did not set up any permissions for Rita, only in PlayChain2.
Rita tries to enter a parking ticket in Chaintable with help of the stored procedure:
USE PlayChain2 go EXECUTE AS LOGIN = 'Rita' EXEC add_chaindata 1, 'Parking in the middle of a roundabout?' go REVERT
But this fails:
Msg 229, Level 14, State 5, Procedure add_chaindata, Line 2
The INSERT permission was denied on the object 'Chaintable', database 'PlayChain1', schema 'dbo'.
add_chaindata and Chaintable have the same owner, PlayChain$owner, but since cross-database ownership chaining is not enabled, there is still a permission check.
To enable ownership chaining, the option DB_CHAINING must be set for both databases:
ALTER DATABASE PlayChain1 SET DB_CHAINING ON ALTER DATABASE PlayChain2 SET DB_CHAINING ON
While many database options only require membership in db_owner to be changed, DB_CHAINING is like TRUSTWORTHY: it requires that you have CONTROL SERVER. That is, if you are administrator only on database level, you need to talk to the server-level DBA to enable cross-database chaining for your database. (Who, for reasons that will prevail, have all reasons to say no.)
Rita can now issue her parking ticket:
USE PlayChain2 go EXECUTE AS LOGIN = 'Rita' EXEC add_chaindata 1, 'Parking in the middle of a roundabout?' go REVERT
The output:
id somedata whodidit
------ ---------------------------------------- -----------
1 Parking in the middle of a roundabout? Rita
Once the two databases have been enabled for cross-database chaining, you can develop for the two-database application just like you do for the single-database application. That is, you rely on ownership chaining for the plain vanilla stuff, and if you need dynamic SQL or something else where ownership chaining does not cut it, you use certificate signing in those cases.
As I mentioned, there is a security problem with enabling cross-database ownership chaining. In fact, there are several. Or rather, there are several ways that cross-database ownership chaining can lead to exploits.
Let's first look at what can happen if chaining is enabled for unrelated databases. Say that Sgt Pepper is a regular user of the Chain application:
USE PlayChain1 go CREATE USER SgtPepper go USE PlayChain2 go CREATE USER SgtPepper ALTER ROLE ChainUsers ADD MEMBER SgtPepper
There is still no cause for concern, but assume now that Sgt Pepper persuades the server-level DBA that his database also needs to have DB_CHAINING enabled:
ALTER DATABASE Playground SET DB_CHAINING ON
Sgt Pepper then writes this stored procedure in is own database:
USE Playground
go
CREATE PROCEDURE my_add_chaindata @id int, @somedata nvarchar(40) AS
INSERT PlayChain1.dbo.Chaintable(id, somedata, whodidit)
VALUES(@id, @somedata, 'Kilroy was here!')
SELECT id, somedata, whodidit FROM PlayChain1.dbo.Chaintable WHERE id = @id
He tries to run his procedure:
EXECUTE AS LOGIN = 'SgtPepper' EXEC my_add_chaindata 2, 'With Love from Me to You' go REVERT
But there is a permission error:
Msg 229, Level 14, State 5, Procedure my_add_chaindata, Line 2
The INSERT permission was denied on the object 'Chaintable', database 'PlayChain1', schema 'dbo'.
Both my_add_chaindata and Chaintable are owned by dbo – but it is not the same dbo, so there is no ownership chaining. However, we have already seen that the not-so-good old Sergeant is not one who gives up that easily. Being the administrator and power user for Playground, he creates a user for PlayChain$owner in his database and then makes PlayChain$owner owner of my_add_chaindata. Now he runs the procedure again:
EXECUTE AS LOGIN = 'SgtPepper' go CREATE USER PlayChain$owner ALTER AUTHORIZATION ON my_add_chaindata TO PlayChain$owner EXEC my_add_chaindata 2, 'With Love from Me to You' go REVERT
Now that the procedure and the table have the same owner, ownership chaining applies and his plot succeeds:
id somedata whodidit
----- ----------------------------------- ----------------
2 With Love from Me to You Kilroy was here!
That is, casual use of DB_CHAINING can lead to vulnerabilities between user databases, and that is why it is not a good idea to use the configuration parameter to turn on cross-database ownership chaining on instance level.
But that is only the start. Of the system databases, master, tempdb and msdb all have DB chaining enabled by default. Furthermore, as we learnt in the previous section, the guest user is enabled in these databases, so all users have access to them. This can be exploited by anyone with database powers to create users and store procedures in a user database enabled for DB chaining. Here is an example of how.
Sgt Pepper starts off with writing a procedure that lists the contents in the sysjobs table in msdb and tries to run it:
USE Playground
go
EXECUTE AS LOGIN = 'SgtPepper'
go
CREATE PROCEDURE list_jobs AS
SELECT * FROM msdb.dbo.sysjobs
go
EXEC list_jobs
go
REVERT
This first attempt results in an error message:
Msg 229, Level 14, State 5, Line 322
The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.
There is a permission error, but we can note that Sgt Pepper is able to access the database; this is because the guest user has been granted CONNECT permission in msdb.
But what if Sgt Pepper tries the trick above, and creates a user for the owner of msdb in Playground and then makes that user the owner of list_jobs?
The owner of msdb is sa. So:
EXECUTE AS LOGIN = 'SgtPepper' go CREATE USER sa go REVERT
The result is negative:
Msg 15405, Level 16, State 1, Line 339
Cannot use the special principal 'sa'.
There seems to be a road block. But we know that the Sergeant is the creative sort of person. Maybe this works:
EXECUTE AS LOGIN = 'SgtPepper' go CREATE USER my_sa FOR LOGIN sa go REVERT
The error message is the same. However, what about creating a user for some other SQL login and then remap that user with ALTER USER?
EXECUTE AS LOGIN = 'SgtPepper' go CREATE USER my_sa FOR LOGIN DirectPlay$owner ALTER USER my_sa WITH LOGIN = sa go REVERT
Yup! That worked out. Sgt Pepper can now complete his plot:
EXECUTE AS LOGIN = 'SgtPepper' go ALTER AUTHORIZATION ON list_jobs TO my_sa EXEC list_jobs go REVERT
Ownership chaining permits him to list the jobs in msdb. Obviously, this is quite harmless, but if he can write stored procedures that runs SELECT on sysjobs and related tables through ownership chaining, he can write stored procedures insert and modify data into these tables. That is, he can add and modify jobs. For instance, he can add a job that makes him sysadmin. Or add a job that perform tasks for him as sysadmin.
Can you protect yourself against this attack, by turning off DB_CHAINING for msdb or disable the guest user? I was able to drop guest from msdb, although Books Online says that you cannot do that. However, it is a clear hint that you should not. And, indeed, when I tried to backup a database as a non-sysadmin user I got permission errors when BACKUP tried to record the backup in msdb. (The backup as such was successful anyway.) What breaks if you disable ownership chaining, I have not been able to figure out, but I am not brave enough to recommend you to turn off ownership chaining for msdb. That would have to be your own decision.
As I mentioned, DB_CHAINING is also enabled by default for tempdb and master, and SQL Server does not permit you to change the setting for these databases. Can they be exploited for something? This query returns objects in master to which public has not been granted permission:
USE master
go
SELECT *
FROM sys.all_objects o
WHERE NOT EXISTS (SELECT *
FROM sys.database_permissions dp
WHERE dp.major_id = o.object_id
AND dp.class = 1
AND dp.grantee_principal_id = 0)
It returns over 150 objects. Of these, the major part is in the sys schema, and it seems that these are in fact in the resource database, and when attempting to access them with the trick above, I still get a permission error. A few objects are in the dbo schema, but they seem fairly innocent. But I am not going to vouch for that there are no vulnerabilities with regards to master. And for that matter, you may have your own private objects in master.
Note: If you think renaming sa is an option, it is not. Any user who wishes to know what sa has been renamed to can use suser_name(1) to find out. Nor does it help to disable sa as a login.
Does this mean that cross-database ownership chaining is just as bad as TRUSTWORTHY + AUTHENTICATE SERVER and should not be used?
Yes and no. If you are looking for real security and you have no trust in the persons who are power users in the database, there is definitely no difference. On the other hand, if you trust these people not to be rogue, but you only want to keep them out of sysadmin to avoid that they cause accidental damage, I think there is a difference. Making a database trustworthy more or less invites them to use EXECUTE AS if they need some server-level thing done quickly and you are in a meeting. The hurdle of going through a job is certainly higher.
And of course there is always the situation that there are no local power users. That is, everyone who has elevated permission in the database are already sysadmin, so no one can elevate and enabling ownership chaining is harmless.
You will have to make your own assessments to decide whether enabling cross-database ownership chaining is acceptable for you, but you have seen all the caveats and you should be aware of them.
The EXECUTE AS clause can be used to implement cross-database access, but before you consider this option, there are two initial conditions that needs to be fulfilled:
You will also need to consider the risk for permission elevation across databases, and I will discuss this just below.
For EXECUTE AS to work, the originating database must have been marked as TRUSTWORTHY, and the owner of the originating database must have been granted AUTHENTICATE permission in the target database. The latter is a trivial matter, if the databases have the same owner.
EXECUTE AS + TRUSTWORTHY is an mainly an option for a something like a multi-database application. I don't think it is a good choice for occasional cross access between unrelated applications, like the Payroll database having the need to access the HR database in a single place. For one thing, most likely there are power users in Payroll who are not power users (or even users) in HR. If Payroll is marked TRUSTWORTHY, and the owner of Payroll is granted AUTHENTICATE in HR, these users can access the HR database with db_owner rights, in the same way that Sgt Pepper previously elevated to sysadmin when we made Playground TRUSTWORTHY. On the other hand, in a multi-database application where the set of power users is the same in all databases, this not a concern. (Please pay attention the emphasis.)
EXECUTE AS certainly have a couple of advantages over cross-database ownership chaining:
When I introduced EXECUTE AS to package permissions, I said that you should create a proxy user for each procedure and grant this user permissions tailored for the procedure in question. Admittedly, this model is not really practical for an application with cross-database references all over the place. If you have to update the permission for the proxy user every time you change a procedure to reference a new table, this becomes a bit of a burden.
But I would really like to discourage you from using EXECUTE AS OWNER across the board, given the risks this incurs if there are SQL-injection holes. At a minimum have one proxy user for read-only access and another for write access. For procedures that use dynamic SQL consider to use tailored proxy users for these.
You may also recall that I suggested that you should create these proxy users WITHOUT LOGIN. You may think that for cross-database access, you need to create actual logins and create users for them in each database. But that is not necessary. You can create the same login-less user in two databases, as long as you as you make sure they have the same SID. (To be precise: you can do this on SQL 2012 or later. The option to specify a SID with CREATE USER is not available in SQL 2008.)
Here is an example that brings everything I've talked of so far together. Let's first create some databases.
USE master
go
CREATE DATABASE ImpersonalPlay1
CREATE DATABASE ImpersonalPlay2 WITH TRUSTWORTHY ON
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN ImpersonalPlay$owner ' +
'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN ImpersonalPlay$owner DISABLE
DENY CONNECT SQL TO ImpersonalPlay$owner
ALTER AUTHORIZATION ON DATABASE::PlayChain1 TO ImpersonalPlay$owner
ALTER AUTHORIZATION ON DATABASE::PlayChain2 TO ImpersonalPlay$owner
As with PlayChain1 and PlayChain2, the databases have the same owner; we assume they are part of the same application. We mark ImpersonalPlay2 as TRUSTWORTHY, but not ImpersonalPlay1.
Let's now prepare things in ImpersonalPlay1:
USE ImpersonalPlay1
go
CREATE TABLE ImpersonalTable
(id int NOT NULL,
somedata nvarchar(40) NOT NULL,
whodidit sysname NOT NULL
CONSTRAINT Impers_default_whodidit DEFAULT original_login(),
CONSTRAINT pk_impersonal PRIMARY KEY (id)
)
go
CREATE USER ImpersonalReader WITHOUT LOGIN
CREATE USER ImpersonalWriter WITHOUT LOGIN
ALTER ROLE db_datareader ADD MEMBER ImpersonalReader
ALTER ROLE db_datareader ADD MEMBER ImpersonalWriter
ALTER ROLE db_datawriter ADD MEMBER ImpersonalWriter
We create our standard test table, but note that the default of the auditing column is different so that it will work with impersonation. We also create two login-less users that we add to db_datareader and db_datawriter explicitly. (ImpersonalWriter is added to both roles, since db_datawriter does not give SELECT permission, and most likely that will be needed also in updating procedures.) You may also note that we do not add Rita as a user in this database.
Now over to ImpersonalPlay2 where we first create the proxy users:
USE ImpersonalPlay2
go
DECLARE @sql nvarchar(MAX)
SELECT @sql = (SELECT 'CREATE USER ' + quotename(name) + ' WITHOUT LOGIN ' +
'WITH SID = ' + convert(varchar(200), sid, 1) +
char(13) + char(10)
FROM ImpersonalPlay1.sys.database_principals
WHERE name LIKE 'Impersonal%'
AND type = 'S'
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
PRINT @sql
EXEC(@sql)
That is, we get the SIDs from the first database by running a query against sys.database_principals. And then we embed it in the FOR XML mumbo-jumbo to build a concatenated string to produce the CREATE USER commands for this database. When I ran this, I got this output:
CREATE USER [ImpersonalReader] WITHOUT LOGIN WITH SID = 0x010500000000000903000000F856ED49191FD74B858CCC9C6AD1E798
CREATE USER [ImpersonalWriter] WITHOUT LOGIN WITH SID = 0x010500000000000903000000256621A8D12810439E3C83D8862DF6FA
We go on with adding Rita as a user and our standard user role:
CREATE USER Rita CREATE ROLE ImpersonalUsers ALTER ROLE ImpersonalUsers ADD MEMBER Rita GRANT EXECUTE ON SCHEMA::dbo TO ImpersonalUsers
You may note that we don't add the proxy users to any roles in ImpersonalPlay2, as they exist in this database only to appear in EXECUTE AS clauses. (But if we would join tables in ImpersonalPlay1 and ImpersonalPlay2 to each other, it would be a different matter.)
We create our standard procedure, this time with an EXECUTE AS clause:
CREATE PROCEDURE add_impersonaldata @id int, @somedata nvarchar(40)
WITH EXECUTE AS 'ImpersonalWriter' AS
INSERT ImpersonalPlay1.dbo.ImpersonalTable(id, somedata)
VALUES(@id, @somedata)
SELECT id, somedata, whodidit
FROM ImpersonalPlay1.dbo.ImpersonalTable
WHERE id = @id
Rita is now ready to try the procedure:
USE ImpersonalPlay2 go EXECUTE AS LOGIN = 'Rita' EXEC add_impersonaldata 2, 'We need to tow this car away' go REVERT
The row is inserted successfully. The column whodidit does not say Rita, though, but that was only because we impersonated her through EXECUTE AS LOGIN to make testing easier. You could login directly with Rita (password Meter Maid), if you want to see that whodidit is updated correctly.
We have now looked at five ways to implement cross-database access. All have their pros and cons, and which is the best for you depends on your context. I have tried to capture some scenarios that I believe are typical, but yours may not resemble of any of them. In that case, you will have to use your own good judgement to decide what is best for you.
We are coming to the end of the main article. There is still an appendix where I elaborate on how you can use the techniques I have discussed here in various specific situations. In this article we have look at three ways to package permissions in a stored procedure. Here is a quick summary of their properties:
If you have done all the demos in this article, you now have quite a few databases, logins etc on your server. You can use the script CleanupAll.sql to drop all the objects created by the scripts. Please review and edit the clean-up script before you run it, if there is anything you want to keep. (But whatever you do, don't drop the PlayAudit database, while keeping the logon trigger!)
Through the years there have been people who have suggested improvements or provided input to this article or its predecessor. I like to extend big thanks to current and former SQL Server MVPs Dan Guzman, Martin Bell, Adam Machanic, Hugo Kornelis, Razvan Socol, Kent Tegels, Victor Isakov, Uwe Ricken, Magnus Ahlkvist and Paul White as well as to Imran Mohamed, Jerry Horochowianka, Andrew Chester, Mark Belding, Tomáš Zíka and Michael K. Campbell.
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 recommend that you post a question to a public forum for SQL Server.
...and beware of TRUSTWORTHY!
I have changed the recipe for signing procedures in a user database that needs server-level permission and the script GrantPermsToSP_server.sql as well as the script GrantPermsToSP_server_2008.sql so that the certificate is first created in the user database, which makes the process simpler.
I have also improved the scripts in terms of security: I identified two ways where a malicious power user could use a DDL trigger to lure the server-level DBA to run unwanted actions when running the signing scripts, and I have added protection against these exploits.
There is a new section Security Considerations at the end of chapter 5 that discusses these and other threats you need to consider when you use certificate signing. There is addition to the last paragraphs in the section Into the Sandbox which explores how the sandbox can be a security measure you can utilise.
Finally, I have introduced the same change to the recipe in the section Certificate Signing in the chapter on cross-database access, and in this section I also introduce a model based on ADD SIGNATURE with a pre-computed signature.
Since the article is entirely rewritten, I have truncated much of the revision history, only retaining the first entry.