Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level

Part of having good security is giving users the fewest / least permissions possible in order to execute the code. However, we always seem to find ourselves needing to allow someone to execute something that requires one or two higher-level permissions. Yet, in order to allow for just one extra operation (or maybe even more specific: one extra permission on just one object), our only option seems to be granting a permission that allows for doing a whole range of other things that we do not want to allow. Or, in the case of wanting a specific permission on a specific object, our only option seems to be granting that permission on all objects, or maybe even across all Databases.

Fortunately, there is a facility that allows for very granular security and can handle nearly all situations. Module Signing, introduced in SQL Server 2005, uses Certificates and/or Asymmetric Keys to selectively apply additional permissions to code: Stored Procedures, Triggers, Scalar UDFs, and Multi-statement TVFs. This is far more secure than using Impersonation as it does not require setting the Database property of TRUSTWORTHY to ON (a huge security risk) in order to attain Server-level permissions. Let’s take a look.

Test Case

Scenario: We have a Table that has an audit Trigger on it. The Trigger tries to capture certain connection info, per each event (not each row), of the User that executed the statement.

Problem: Accessing the DMV containing the connection info requires a Server-level permission: VIEW SERVER STATE 1 . Adding WITH EXECUTE AS OWNER to the CREATE TRIGGER statement will only work if we also set the Database option of TRUSTWORTHY to ON, but the security risks of turning that on are not acceptable.

Initial Setup

Create the low-privileged Login (Server-level) and User (Database-level), a simple Table, and a Trigger on that Table that merely prints a Connection property to the “Messages” tab.

USE [tempdb];
SET NOCOUNT, ANSI_NULLS, QUOTED_IDENTIFIER ON;

CREATE LOGIN [AppUser] WITH PASSWORD = 'NevrGonnaGesMe';
CREATE USER [AppUser] FOR LOGIN [AppUser];

CREATE TABLE dbo.TableWithTrigger
(
  [TableWithTriggerID] INT IDENTITY(1, 1) NOT NULL
    CONSTRAINT [PK_TableWithTrigger] PRIMARY KEY,
  [Something] NVARCHAR(50) NULL,
  [InsertTime] DATETIME2 NOT NULL
    CONSTRAINT [DF_TableWithTrigger_InsertTime] DEFAULT (SYSDATETIME())
);

GRANT INSERT ON [dbo].[TableWithTrigger] TO [AppUser];

EXEC(N'CREATE TRIGGER dbo.TableWithTrigger_AuditINSERT
  ON dbo.TableWithTrigger
  AFTER INSERT
AS
SET NOCOUNT ON;

DECLARE @NetAddress NVARCHAR(50);

SELECT  @NetAddress = conn.[client_net_address]
FROM    sys.dm_exec_connections conn
WHERE   conn.[session_id] = @@SPID;

PRINT ''Audited Net Address: '' + @NetAddress;
');

Test 1

Now that we have the items created we can impersonate the AppUser Login and attempt to insert a row into dbo.TableWithTrigger. If all goes well (don’t worry, it won’t), the Trigger will display the “net address” that we are connecting from.

EXECUTE AS LOGIN = 'AppUser';
SELECT SESSION_USER;
-- AppUser

INSERT INTO dbo.[TableWithTrigger] ([Something]) VALUES (N'test');
/*
Msg 300, Level 14, State 1, Procedure TableWithTrigger_AuditINSERT,
      Line XXXXX [Batch Start Line YYYYY]
VIEW SERVER STATE permission was denied on object 'server',
      database 'master'.
Msg 297, Level 16, State 1, Procedure TableWithTrigger_AuditINSERT,
      Line XXXXX [Batch Start Line YYYYY]
The user does not have permission to perform this action.
*/

REVERT;
SELECT SESSION_USER;
-- dbo

Oops. The example above shows that the AppUser Login does not have permission to access the sys.dm_exec_connections DMV. In order to access that DMV one needs the VIEW SERVER STATE permission, which is a Server-level permission. We know this not only from the name of the permission (not all permissions are that obvious), but from the fact that the error states that the permission was “denied on object ‘server'”.

The reason that the error is coming from the [master] Database is due to this being a Server-level issue, and many Server-level operations require being in the [master] Database. Logins can be created (well, as long as the Login doesn’t require an object that exists within the [master] DB) and dropped from any Database, but managing permissions for Logins requires that the current Database be set to [master].

Setup for Test 2

We have seen that the error does occur for this User. The following steps (none of which are difficult) should allow the User to access the DMV without compromising security.

-- 1) Create the Certificate:
CREATE CERTIFICATE [Permission$ViewServerState]
    ENCRYPTION BY PASSWORD = 'UseBetterPassword!'
    WITH SUBJECT = 'VIEW SERVER STATE permission',
    EXPIRY_DATE = '2099-12-31';
/*
The password is used to protect only the Private Key, not the Public
Key or the Certificate in general.

The default expiration date is 1 year from the date the Certificate is
created. However, in many situations, such as with Module Signing, the
expiration date is actually ignored. Still, I prefer to set the
expiration date to 2099.
*/


-- 2) Sign the Module:
ADD SIGNATURE
    TO [dbo].[TableWithTrigger_AuditINSERT]
    BY CERTIFICATE [Permission$ViewServerState]
    WITH PASSWORD = 'UseBetterPassword!';
/*
Verify using the following query:

SELECT OBJECT_NAME(cp.[major_id]) AS [name], *
FROM   sys.crypt_properties cp;
*/


-- 3) Backup the Certificate and Private Key to files (optional if
--    using SQL Server 2012 or newer, else required):
BACKUP CERTIFICATE [Permission$ViewServerState]
  TO FILE = 'C:\TEMP\cert.cer'
  WITH PRIVATE KEY
  (
     FILE = 'C:\TEMP\cert.pvk',
     DECRYPTION BY PASSWORD = 'UseBetterPassword!', -- decrypt the PVK
     ENCRYPTION BY PASSWORD = 'FilePassword!' -- encrypt PVK in file
  );

-- and/or to a VARBINARY string to be copied and pasted somewhere safe:
SELECT CERTPRIVATEKEY(CERT_ID(N'Permission$ViewServerState'),
                      'NewPassword!', -- encrypt the value returned
                      'UseBetterPassword!'); -- decrypt the Private Key
/*
You backup the Certificate so that you can recreate / restore it later.
The BACKUP command specifies two files because Certificates are two
pieces: the "Certificate" (confusingly the same name) is the Public Key
plus the meta-data (Expiration Date, Subject, etc), which is assumed,
and the Private Key, which is optional. For the Private Key, the
decryption and encryption passwords can be the same. They are different
here to make it visually clear that one of the passwords was used when
creating the Certificate, and the other is for something else. Using a
different password is more secure.

The CERTPRIVATEKEY built-in function was added in SQL Server 2012. If
you are using SQL Server 2005, 2008, or 2008 R2, then:
1) you do not have the CERTPRIVATEKEY function
2) this step is NOT optional; it is needed for Step 5 
*/


-- 4) Remove the Private Key (optional):
ALTER CERTIFICATE [Permission$ViewServerState]
    REMOVE PRIVATE KEY;
/*
Do this to prevent anyone from using the Certificate to sign other
modules so that they can also have this permission.
But, signing also requires knowing the password. If the password is
unknown to all but the DBAs, then it should be safe to keep the Private
Key in the Certificate. If it is removed and you need to sign something
new, or re-sign an object that has been changed and hence lost its
signature, then the Private Key can be restored using the info from
Step 3.
*/


-- 5) Copy Certificate to [master] (no need to include Private Key):
DECLARE @Cert NVARCHAR(4000) =
         CONVERT(NVARCHAR(4000),
                 CERTENCODED(CERT_ID(N'Permission$ViewServerState')),
                 1);

EXEC (N'USE [master];
CREATE CERTIFICATE [Permission$ViewServerState]
FROM BINARY = ' + @Cert);

/*
The CERTENCODED built-in function is not available in SQL Server 2005,
2008, or 2008 R2. When using those versions, execute the following:

EXEC (N'USE [master];
CREATE CERTIFICATE [Permission$ViewServerState]
  FROM FILE = ''C:\TEMP\cert.cer'';
');
*/


-- 6) Create Login and Grant Permission:
EXEC (N'USE [master];
CREATE LOGIN [Permission$ViewServerState]
    FROM CERTIFICATE [Permission$ViewServerState];

GRANT VIEW SERVER STATE TO [Permission$ViewServerState];
');

Test 2

Now that the Module Signing is in place, let’s try it again…

EXECUTE AS LOGIN = 'AppUser';
SELECT SESSION_USER;

INSERT INTO dbo.[TableWithTrigger] ([Something]) VALUES (N'test');
/*
Audited Net Address: <named pipe>
*/

SELECT conn.[client_net_address]
FROM   sys.dm_exec_connections conn
WHERE  conn.[session_id] = @@SPID;
/*
Msg 300, Level 14, State 1, Line XXXXX
VIEW SERVER STATE permission was denied on object 'server',
    database 'master'.
Msg 297, Level 16, State 1, Line XXXXX
The user does not have permission to perform this action.
*/

REVERT;
SELECT SESSION_USER;

Executing the Test 2 queries shows us that:

  1. The process now works: the DMV can be accessed without causing an error.
  2. The permission to access the DMV was not given to the “AppUser” Login since that Login cannot access the DMV.

Cleanup

Execute the following to remove all objects (Logins, Users, Certificates, Table, and Trigger) created by the example code above.

USE [tempdb];

IF (USER_ID(N'AppUser') IS NOT NULL)
BEGIN
    DROP USER [AppUser];
END;

IF (SUSER_ID(N'AppUser') IS NOT NULL)
BEGIN
    DROP LOGIN [AppUser];
END;

IF (OBJECT_ID(N'dbo.TableWithTrigger') IS NOT NULL)
BEGIN
    DROP TABLE [TableWithTrigger];
END;

IF (CERT_ID(N'Permission$ViewServerState') IS NOT NULL)
BEGIN
    DROP CERTIFICATE [Permission$ViewServerState];
END;

IF (SUSER_ID(N'Permission$ViewServerState') IS NOT NULL)
BEGIN
    DROP LOGIN [Permission$ViewServerState];
END;

EXEC (N'USE [master];
IF (CERT_ID(N''Permission$ViewServerState'') IS NOT NULL)
BEGIN
    DROP CERTIFICATE [Permission$ViewServerState];
END;
');

The Alternative: Impersonation

Rather than using Module Signing, you could always go the easier (and far less secure) route of using Impersonation. That requires specifying the EXECUTE AS clause of the CREATE TRIGGER statement (or CREATE FUNCTION, or CREATE PROCEDURE, etc). You can only specify Users (i.e. Database-level Principals) in the EXECUTE AS clause, so you would specify one that has a matching Login (i.e. a Server-level Principal) that has the VIEW SERVER STATE permission (either explicitly, or implicitly via a permission such as CONTROL SERVER or membership in a fixed Server role such as sysadmin).

However, Impersonation by default is quarantined to the Database, so you would need to set the Database property of TRUSTWORTHY to ON. But, doing that is a huge security risk, especially if the owner of the Database (i.e. the SID of the “dbo” User) has a matching Login that is highly privileged. Enabling TRUSTWORTHY allows all code in that Database to use Server-level permissions if there is a Login with a Security ID (SID) that matches the User specified in the EXECUTE AS clause of the CREATE object statement. Meaning, any code in that Database that uses WITH EXECUTE AS OWNER (assuming the owner is dbo) or EXECUTE AS 'dbo' now has Server-level permissions, even if the EXECUTE AS was only being used to grant higher Database-level permissions. And, given how many Databases are owned by sa, enabling TRUSTWORTHY effectively grants all EXECUTE AS 'dbo' code in that Database full sysadmin privileges.

And, if the security risk of using Impersonation wasn’t bad enough, it also doesn’t perform as well as Module Signing. This is due to the security context switching: changing out the current Login / User and their permissions with the impersonated account and its permissions, and then reverting the account and permissions back to the original when the module ends. With Module Signing, some additional permissions are added to the security context and then removed when the module ends. This adding and removing of extra permissions also increases the execution time, but not by nearly as much as Impersonation.

Conclusion

Granting high-level permissions can be as granular as you like. Since you assign the permissions to the code instead of to Logins / Users, the discrete operations of the module(s) that you sign are effectively those granular permissions. Putting that in terms of the example shown in this post:

If you grant the VIEW SERVER STATE permission to a Trigger by signing it with a Certificate that has an associated Login, and the Trigger only selects one row from the sys.dm_exec_connections DMV, then you haven’t given everyone with INSERT permission on that Table the VIEW SERVER STATE permission. You have only given that permission to the Trigger, and the Trigger only uses that permission in that one query to access one column of one specific row from that DMV.

AND, there is no side-effect (security-wise) of granting the additional permission(s) via Module Signing. This is a very important point because the alternative (i.e. enabling TRUSTWORTHY) only works by being a huge security hole: if it allowed the EXECUTE AS clause to reach up to the Server-level to get the VIEW SERVER STATE permission, then the process would have all of the other Server-level permissions of the Login associated with the EXECUTE AS User. Creating a Login that only has that one permission (like we did in the example above when setting up the Module Signing) will reduce the scope of the security issues for that Trigger, but it won’t:

  1. prevent anyone from using EXECUTE AS with that User in other modules to gain access to whatever else the VIEW SERVER STATE permission controls, nor will it…
  2. prevent anyone from using EXECUTE AS with some other User that has an associated Login that is highly privileged (EXECUTE AS OWNER and EXECUTE AS 'dbo' are effectively EXECUTE AS sa for any Database that is owned by sa, and there is a good chance that your Databases are indeed owned by sa).

Essentially, Module Signing only applies the additional permissions to what has been signed (which requires a password that can be kept private, and a private key that can be removed), and nothing more. On the other hand, there is no mechanism to limit what code can use EXECUTE AS, or what Users can be the EXECUTE AS User, when enabling TRUSTWORTHY.

AND, in addition to being more granular and more controllable, Module Signing also performs better than Impersonation. (I will provide the test code and results in a future post.)

Given that Module Signing is more secure and more performant than Impersonation, it is definitely worth the extra 30 seconds it takes to set it up (compared to setting up Impersonation).

For more information on Module Signing, please see:


  1. While it is true that the DMV in question does need the VIEW SERVER STATE permission, for this particular case it is not required to use the DMV in the first place. The Connection info for the current Session can be obtained from the CONNECTIONPROPERTY built-in function. If this were not example code then that would be the preferred option as it does not require any additional steps to get working. Coincidentally, this fact is itself a good example of the benefit of looking through the available built-in functions first to possibly avoid wasting time over-engineering a solution :-). 

6 thoughts on “Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level”

  1. Very interesting, thank you. We have implemented some kind of adminDB with encrypted store procedures in order for some users to execute some action which require elevate permissions. For instance to add a login the bulkadmin server role. The DB is set as trustworthy, the permission are granted to a database role to execute the store procedures only. Users cannot create objects in the adminDB. This should actually work in the testing we weren’t able to hack it. Probably we don’t reach the same level of security as through module signing, however it should be safe enough or do you still see some possible leaks ?

    Liked by 1 person

    1. What qualifies as safe “enough” is highly subjective, but it sounds “reasonably” secure ;-). It really comes down to control and comfort-level with the potential for something to get introduced later that allows for more than it should. Do you have tight control over what procs get added and updated? Outside of the Users that only have EXECUTE permission via the Role, how do procs get added / updated? Is there a release procs that places objects there that have been checked into source control? Can a developer sneak in a change to this proc that you have mentioned? What you have is secure as long as nothing happens outside of expectations. One major benefit of Module Signing is that it nearly eliminates the chances of something slipping through the cracks. In a small shop with all code flowing through 1 or 2 DBA’s then your approach should be fine. But over time, will someone add a proc to this DB, set it to EXECUTE AS 'dbo' (or OWNER), not understand that it now executes as sysadmin and unknowingly allow it to do too much, especially if Dynamic SQL is being executed? Your approach could be fine. Heck, there are systems that have the app log in as sa and exist for 20+ years and never get hacked or have anything bad happen. What you have should be fine. The only issue is that the door is now open for a proc to be added by someone who is allowed to do so yet does something that you would never allow had you reviewed it. That might not happen today, or even in the next year or so, but teams change, employees come and go. There might even be ways to do stuff with existing system procs that I am unaware of but can do damage in a TRUSTWORTHY DB.

      All of that to say: you could take 3 – 5 minutes to copy and paste the code I have above to: create and copy the Certificate, create the Login, grant the permission(s), alter the proc to remove the EXECUTE AS clause, sign the proc, and set TRUSTWORTHY OFF for that DB. Or, you can leave things as they are and you will likely still be ok.

      Like

      1. Thank you very much for your advice. You got the point, at the moment we are a very small group of DBA controlling the code and it works. But it is always good to think a bit ahead :-).

        Like

  2. This was very timely. I had just implemented impersonation to allow some dynamic sql that executes DDL. I wasn’t comfortable with that since I lost user context in the procedure (suser_sname, session_user etc return the impersonated account) and something about it just had a bad code smell to it. Then literally a week later I read this and viola! problem solved the right way, no more code smell. Thank you, this has definitely been added to my toolbox.

    Liked by 1 person

Thoughts? Questions? Comments? Suggestions? Words of praise?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s