Notes on SQL

And how to assign Server-Level permissions to a database user.

Background

In a previous article I gave an example of assigning permissions to a user and making use of the EXECUTE AS instruction. This is a very useful way of assigning database permissions in a very granular way, where a user should not have the associated role for those permissions.
However, if the permission required relates to a server-level role then another method is required.

Test Environment

The (very) basic test environment will initially consist of a test database with one stored procedure and a user that has execute permissions for this stored procedure.

Listing 1: Setting the basic test environment

CREATE DATABASE CertificateTest;
GO

USE CertificateTest;
GO

CREATE PROCEDURE [dbo].[GetReplicaDetailsCount]
(@CountHAGroups INT OUTPUT)
AS
BEGIN
	SET @CountHAGroups =
		(
			SELECT COUNT(*) FROM
			(
				SELECT
				AG.name
				FROM
				master.sys.availability_groups AS AG
				LEFT JOIN master.sys.dm_hadr_availability_group_states as agstates
				ON AG.group_id = agstates.group_id

			) AS AGCOUNT
		)
END;
GO

USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'TestUser', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [CertificateTest]
GO

CREATE USER [TestUser] FOR LOGIN [TestUser]
GO

GRANT EXECUTE ON [dbo].[GetReplicaDetailsCount] TO [TestUser]
GO

Executing the Store Procedure with Different Accounts

The stored procedure ‘GetReplicaDetailsCount’ doesn’t do a great deal – it returns the number of High Availability (HA) groups that are within the table ‘master.sys.availibilitygroups’. It has a join to ‘master.sys.dm_hadr_availability_group_states’, which requires VIEW SERVER STATE permission to access.

With my sysadmin role, I can execute this stored procedure without any issues.

Listing 2: Executing stored procedure as sysadmin role

USE [CertificateTest]
GO

DECLARE	@CountHAGroups int

EXEC [dbo].[GetReplicaDetailsCount]
		@CountHAGroups = @CountHAGroups OUTPUT

SELECT	@CountHAGroups as N'@CountHAGroups'

GO

Figure 1: Result from Listing 2

Although the count returned is zero (because there are no HA groups on this server) it does execute without any errors.

So now switch to a ‘normal’ user, that has execute permission on the stored procedure but nothing else.

Listing 3: Executing stored procedure without sysadmin role

USE [CertificateTest]
GO

EXECUTE AS USER = 'TestUser'; 

DECLARE	@CountHAGroups int

EXEC [dbo].[GetReplicaDetailsCount]
		@CountHAGroups = @CountHAGroups OUTPUT

SELECT	@CountHAGroups as N'@CountHAGroups'

REVERT

GO

Figure 2: Result from Listing 3

The message “The user does not have permission to perform this action” has been raised because the user account does not have the required permission to access the DMV ‘dm_hadr_availability_group_states’.

According to this Microsoft description of the DMV, the permission required for this is VIEW SERVER STATE. This permission is a sub-set of the server role ‘serveradmin’ but assigning the user that server role would give them access to much more than VIEW SERVER STATE. A user that also had the permissions to shut the server down might not be a good thing.

Why Use a Certificate

In the previous article I created a user account that had no login and then assigned the permissions required to that user, switching to that user within the required code.
However, in this case the VIEW SERVER STATE permission is a server-level permission – it cannot be assigned to a database user, so the ‘EXECUTE AS’ method will not work in that form. I don’t want to assign the role ‘serveradmin’ to the user, because that breaks the principal of least privilege – assigning a user the minimum levels of permissions to complete a given task.

So in this case I need a certificate, which will be shared between my ‘CertifcateTest’ database and ‘master’ database. It will be assigned the required permissions from within ‘master’ (via a login created from the certificate) and the certificate will then be associated with the stored procedure within ‘CertificateTest’ (via a user created from the certificate-created login). It shares some features that were demonstrated in the previous article but has several important differences.

Creating the Certificate

The first step is to create the certificate, with password within the ‘CertificateTest’ database.

Listing 4: Certificate creation

--Create the certificate
CREATE CERTIFICATE [ServerViewCertificate]
ENCRYPTION BY PASSWORD = 'SK@(67Hn00T$%GG'
WITH SUBJECT = 'Certificate to view server state';
GO

Within SSMS the certificate will be listed within the database, under ‘Security/Certificates’.

Having created this certificate, it needs to be copied to ‘master’. There is no script option within SSMS, so the easiest method is to create a backup and then restore it within ‘master’.

Listing 5: Backup the certificate to a file

BACKUP CERTIFICATE [ServerViewCertificate]
TO FILE = 'C:\Temp\ServerViewCertificate.cer';
GO

Now switch to the ‘master’ database and restore the certificate from the backup created in Listing 5.

Listing 6: Restore the certificate into ‘master’

--Switch to the master database
USE master;
GO

--Restore the certificate from the backup
--This means that the certificate is shared across two databases,
--so permissions that only the master database can assign
--are now accessible.
CREATE CERTIFICATE [ServerViewCertificate]
      FROM FILE = 'C:\Temp\ServerViewCertificate.cer'
GO

Now that we have the same certificate in both databases, it is possible to check that they have the same ‘thumbprint’. This step isn’t required but does help to verify it is all going well this far.

Listing 7: Check the thumbprints

SELECT
    name,
    thumbprint
FROM master.sys.certificates
WHERE
    name = N'ServerViewCertificate'
UNION ALL
SELECT
    name,
    thumbprint
FROM CertificateTest.sys.certificates
WHERE
    name = N'ServerViewCertificate';

Figure 3: Result from Listing 7

Now the login needs to be created and have the required permissions assigned.

Listing 8: Create Login and Permissions

USE master;
GO

--Create a SQL Server Login that is associated with the certificate
--Impossible to use as a genuine login.
CREATE LOGIN [ServerViewCertificateLogin] FROM CERTIFICATE [ServerViewCertificate];
GO

--Required to let the permissions work
GRANT AUTHENTICATE SERVER TO [ServerViewCertificateLogin];
GO

--The granular permission
GRANT VIEW SERVER STATE TO [ServerViewCertificateLogin];
GO

The permission for VIEW SERVER STATE has been assigned to the certificate, within ‘master’. AUTHENTICATE SERVER is also required because we will be changing the stored procedure in a subsequent step, to include EXECUTE AS OWNER.

Now a user needs to be created within ‘CertificateTest’, based upon the Login created against the certificate.

Listing 9: Create User

USE CertificateTest;
GO

--Create a user that is also associated with the certificate (via ServerViewCertificateLogin)
CREATE USER [ServerViewCertificateUser] FOR LOGIN [ServerViewCertificateLogin]
GO

The stored procedure requires a change – with the addition of the line ‘EXECUTE AS OWNER’.

Listing 10: Alter the stored procedure

USE CertificateTest;
GO

ALTER PROCEDURE [dbo].[GetReplicaDetailsCount]
(@CountHAGroups INT OUTPUT)
AS
BEGIN

	SET @CountHAGroups =
		(
			SELECT COUNT(*) FROM

			(
				SELECT
				AG.name
				FROM
				master.sys.availability_groups AS AG
				LEFT JOIN master.sys.dm_hadr_availability_group_states as agstates
				ON AG.group_id = agstates.group_id

			) AS AGCOUNT
		)

END;
GO

The stored procedure now needs to have the certificate associated with it. The password specified is the password that was used to create the certificate, in listing 4.

Listing 11: Add the certificate to the stored procedure

USE CertificateTest;
GO

--Add the certificate to the stored procedure. This digitally signs the procedure.
--Note that changing the code after this may invalidate the certificate.
ADD SIGNATURE TO GetReplicaDetailsCount BY CERTIFICATE [ServerViewCertificate] WITH PASSWORD = 'SK@(67Hn00T$%GG';
GO

If we repeat the test from Listing 3, we should now see the stored procedure execute without error.

Listing 12: Repeat listing 3 code

USE [CertificateTest]
GO

EXECUTE AS USER = 'TestUser'; 

DECLARE @CountHAGroups int

EXEC [dbo].[GetReplicaDetailsCount]
        @CountHAGroups = @CountHAGroups OUTPUT

SELECT  @CountHAGroups as N'@CountHAGroups'

REVERT

GO

Figure 4: Result from Listing 12

Now the procedure has the required permission, to access server-level data but without granting a user additional permissions that they would not require.

Preventing Future Use of a Certificate

I now have a certificate that can be used to assign VIEW SERVER STATE permission to any procedure, function (except inline table-valued functions) or trigger.
For example, if I were to create the following stored procedure, to select all columns from ‘master.sys.dm_hadr_availability_group_states’:

Listing 13: Basic procedure to select from HA table

USE CertificateTest;
GO

CREATE PROCEDURE [dbo].[GetHAGroupStates]
WITH EXECUTE AS OWNER
AS
BEGIN
	SELECT* FROM master.sys.dm_hadr_availability_group_states
END;
GO

GRANT EXECUTE ON [dbo].[GetHAGroupStates] TO [TestUser]
GO

ADD SIGNATURE TO GetHAGroupStates BY CERTIFICATE [ServerViewCertificate] WITH PASSWORD = 'SK@(67Hn00T$%GG';
GO

Executing this stored procedure as ‘TestUser’ will work perfectly, as the certificate is providing the permission.

Listing 14: Try the stored procedure as ‘TestUser’

USE [CertificateTest]
GO

EXECUTE AS USER = 'TestUser'; 

EXEC [dbo].[GetHAGroupStates];

REVERT;
GO

So, we have a certificate that can be used against code, by anybody that has the required permissions to create or alter code.

To prevent a certificate from being re-used in this way, there is a ‘REMOVE PRIVATE KEY’ option to the ALTER CERTIFICATE command. This allows code that already has the certificate applied to execute but prevents the certificate from being applied to new code.

Listing 15: ‘Disable’ the certificate

USE CertificateTest;
GO

ALTER CERTIFICATE [ServerViewCertificate]
REMOVE PRIVATE KEY;
GO

If I now attempt to add the certificate to some new code, it will error:

Listing 16: Attempt to create another stored procedure with the certificate

USE CertificateTest;
GO

CREATE PROCEDURE [dbo].[GetHAGroupStatesYetAgain]
AS
BEGIN
	SELECT* FROM master.sys.dm_hadr_availability_group_states
END;
GO

GRANT EXECUTE ON [dbo].[GetHAGroupStatesYetAgain] TO [TestUser];
GO

ADD SIGNATURE TO GetHAGroupStatesYetAgain BY CERTIFICATE [ServerViewCertificate] WITH PASSWORD = 'SK@(67Hn00T$%GG';
GO

Figure 5: Error returned from Listing 16

The error message “Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.” shows that the certificate can no longer be associated with any code, although it will still work against procedures etc that it was associated with before ‘REMOVE PRIVATE KEY’ was executed.

Summary

Using certificates can provide a very secure way to provide additional permissions at a granular level under very specific circumstances. It is slightly more complex than the more common impersonation method (EXECUTE AS <username>’) but is necessary where the permissions relate to server-level roles.

References
Authorization and Permissions in SQL Server
Why AUTHENTICATE SERVER?
EXECUTE AS