This article first appeared in the SQLServerGeeks Magazine.
Author: Erland Sommarskog
Subscribe to get your copy.
I ended my tip in the most recent SQLServerGeeks Magazine with a cliff-hanger. In my August article, I showed how you can use regular expressions in Python from T-SQL. If you put such an operation in a stored procedure, the users would need to hold the permission EXECUTE ANY EXTERNAL SCRIPT, which you may not want to grant them directly.
This month we will look at how we can package that permission with the stored procedure instead – and not only specifically that permission, but we will look at this from a general perspective. We want users to be able to perform a certain operation in a controlled manner in a stored procedure, without granting them any other permission than to execute that stored procedure.
As long it is only a matter of direct data manipulation, through SELECT, INSERT, UPDATE etc, you don’t need to take any specific action if the stored procedure and the table have the same owner, which very often is the case, as in most databases everything is owned by dbo. In this case, SQL Server employs ownership chaining which means that as long as the owner is the same, no permission check is performed at all.
Ownership chaining only applies to this specific, but common, case. Here are some examples of situations, where it does not, and it may seem at first that you have to grant permissions to users directly:
• You want to use dynamic SQL in your stored procedure. A string of dynamic SQL is an anonymous stored procedure of its own, which does not have an owner. Therefore, SQL Server makes an explicit permission check inside the dynamic SQL.
• You need to inquire metadata. For instance, you only want to update an table if it actually exists, so you do:
IF object_id('dbo.PossibleTable') IS NOT NULL
UPDATE dbo.PossibleTable
SET ...
However, this requires that the permission VIEW DEFINITION has been granted on PossibleTable, or else object_id will return NULL.
• You want superusers of the application to be able to add users through the application.
• As discussed above, you want users to be able to run a certain Python script embedded into your stored procedure.
There are two techniques to package permissions into stored procedures. One technique, the EXECUTE AS clause, seems easy to use, but it comes with some side effects that can be nasty, unless you understand them and plan ahead. The other technique, certificate signing, at first seems more complicated, and indeed it takes some time to digest in full. However, it is easy to automate, and you don’t have to understand it in full in order to use it. And more importantly, it does not come with any side effects.
I describe both these techniques in detail in my article Packaging Permissions in Stored Procedures on my web site. Here in the SQLServerGeeks Magazine I will only give you a short introduction to give you a taste if it, so that you can use the special management procedure that I present here.
Before we go on, here is an example to play with. The script creates a table and a stored procedure that accesses that table through dynamic SQL. The script also creates a login-less user and grants that user permission to run the procedure but no permissions on the table:
CREATE TABLE sometable (a int NOT NULL)
go
CREATE PROCEDURE testproc AS
EXEC('SELECT a FROM sometable')
go
CREATE USER testuser WITHOUT LOGIN
GRANT EXECUTE ON testproc TO testuser
To test the permission, we can impersonate that user and run the procedure. When you impersonate a database user, you use the command EXECUTE AS USER and you return back to your true self with the REVERT command. (Which you always should place in a batch of its own, to be sure that it is executed, as a batch may not run to the end if it is aborted by an error.)
EXECUTE AS USER = 'testuser' go EXEC testproc go REVERT
The output is:
Msg 229, Level 14, State 5, Line 17 The SELECT permission was denied on the object 'sometable', database 'tempdb', schema 'dbo'.
Let’s now look at how we can resolve this error without granting testuser permission on the table directly.
The recipe for how to package permissions into a stored procedure through certificate signing has four steps:
1. Create a certificate.
2. Sign the procedure with the certificate.
3. Create a user from that certificate. (This is a special type of user.)
4. Grant the user the permissions you want package with the stored procedure.
The effect is that when a user runs the procedure, SQL Server checks that the signature is valid, and if it is, SQL Server adds the user to the user tokens (which you can inspect in sys.user_token), and therefore the permissions of the certificate user apply in combination with the user’s own permissions.
Now, while may seem like a bit of mumbo-jumbo, I have stored procedure GrantPermsToSP which perform these steps for you and which I present in more detail in the above-mentioned article.
You find the full listing for GrantPermsToSP, including creation of the schema and the table type it uses, here: https://www.sommarskog.se/grantperm/GrantPermsToSP.sql.txt. Copy the script to SSMS, remove the initial USE statement, and run it to create schema, table type and procedure. Once you have done this, you can run the below to package the SELECT permission with the stored procedure:
DECLARE @perms Management.Permission_list
INSERT @perms (perm) VALUES ('SELECT ON sometable')
EXECUTE Management.GrantPermsToSP 'dbo.testproc', @perms, @debug = 1
When you specify @debug = 1, the procedure displays the commands it executes. I don’t include them here, but you are encouraged to look at them on your own. You will find that they correspond to the recipe I gave above.
Run the test again:
EXECUTE AS USER = 'testuser' go EXEC testproc go REVERT
This time the procedure executes without error.
It is important to understand that if you change the procedure, the signature is no longer valid, and, in fact, SQL Server removes it entirely. Here is an example:
ALTER PROCEDURE testproc AS
EXEC('SELECT a FROM sometable AS x')
go
EXECUTE AS USER = 'testuser'
go
EXEC testproc
go
REVERT
The error message is now back, and you need to rerun GrantPermsToSP to restore the permission. What you would do in many cases is to put the call to GrantPermsToSP at the end of the file that creates the stored procedure, so that the permissions are re-packaged into the procedure every time it is modified. (And you would modify what permissions you package as needed.)
As I mentioned, the dynamic SQL is a scope of its own. We can tell from the example that when we enter the dynamic SQL, the certificate user is still present among the security tokens; it is this token that contributes with the SELECT permission. The same is also true if you call a system procedure or a system function. But when you invoke user-written code, be that another stored procedure, a trigger or a function, in this case the certificate is removed from the tokens, so if you want that extra permission to apply in the inner module too, you need to sign that one as well. This may seem like a hassle, but I think this is a great design. (Space does not permit me to elaborate on why in this article, but see my longer article for a discussion.)
When you use this technique, you should always make the effort to identify the minimum permission(s) you need to package, following best practice for security. Say that the stored procedure has an SQL-injection hole. The less powerful permissions you have packaged with the procedure, the less is the damage that an intruder can incur.
Let’s now turn to the EXECUTE AS clause. The proper implementation of this technique is to create a proxy user WITHOUT LOGIN and grant that user the permissions you want to package. Then you add the clause WITH EXECUTE AS to the stored procedure. Here is an example:
CREATE USER testproc$proxy WITHOUT LOGIN
GRANT SELECT ON sometable TO testproc$proxy
go
ALTER PROCEDURE testproc WITH EXECUTE AS 'testproc$proxy' AS
EXEC('SELECT a FROM sometable')
Test it:
EXECUTE AS USER = 'testuser' go EXEC testproc go REVERT
This completes without error.
What happens is impersonation, just as with the EXECUTE AS USER statement. That is, for the duration of the stored procedure, we are testproc$proxy, and none of our own permissions apply. Furthermore, if the procedure invokes other procedures or functions or fire triggers, we are testproc$proxy also inside these modules.
This can have nasty side effects. It is common that auditing is based on functions such as SYSTEM_USER, USER_NAME(), SUSER_SNAME() etc. Likewise, schemes for row-level security are often based on any of these functions. All these functions will return testproc$proxy or just a SID relating to this user, and not the actual user running the procedure. That is, causal use of the WITH EXECUTE AS clause in stored procedure can result in incorrect auditing and incorrect results returned by queries using row-level security. And because the impersonation propagates into other modules, these errors may occur in code that has no knowledge of that impersonation is happening elsewhere.
This is not a complete show-stopper for WITH EXECUTE AS, because you can design your auditing and row-level security to rely on functions that are unaffected by impersonation. One option is the function original_login() which returns name of the user that actually logged into SQL Server. Another alternative is session_context(), which requires that you first set a value with sp_set_session_context. Thus, to be able to use WITH EXECUTE AS, you must plan ahead. And more importantly, you cannot start using it in your system, without first investigating the possible side effects. So, from this perspective, this technique is actually more difficult to use than certificate signing, which does not have any of these side effects.
Despite these caveats, WITH EXECUTE AS appears to be the more popular method, and in fact most people don’t bother to create the proxy user, but cuts it short with:
ALTER PROCEDURE testproc WITH EXECUTE AS OWNER AS
EXEC('SELECT a FROM sometable')
That is, the procedure now runs as dbo. Which means that if there is an SQL injection hole, an intruder can use it do anything in the database. It goes without saying that this is bad practice. And, yes, the side effects for auditing and row-level security is still there.
In this article, I have only discussed how to use these techniques for permissions in the current database. They can also be used to package server-level permissions, or permissions for cross-database access. However, this requires more space to discuss than is available in the Magazine, so I refer you to my article Packaging Permissions in Stored Procedures where I discuss this in detail, and I also offer an automation script, similar to GrantPermsToSP, to package server-level permissions.
This article first appeared in the SQLServerGeeks Magazine.
Author: Erland Sommarskog
Subscribe to get your copy.
