Assigning Server-Level Permissions
In this post, Data Education founder and trainer Adam Machanic takes things one step further in regards to assigning permissions. Here, he explains that signing a stored procedure can also be used to propagate server-level permissions.
The previous post showed only how to assign database-level permissions using a certificate. Signing a stored procedure can also be used to propagate sever-level permissions, such as
BULK INSERT or
ALTER DATABASE. Doing so requires creation of a proxy login from a certificate, followed by creation of a database user using the same certificate. To accomplish this, the certificate must be backed up after being created, and restored in the database in which you are creating the user. Once the database user is created, the procedure to apply permissions is the same as when propagating database-level permissions.
To begin with, create a certificate in the master database. Unlike previous examples, this certificate must include a password in its definition, in order to encrypt its private key. Once the certificate has been created, use it to create a proxy login:
CREATE CERTIFICATE alter_db_certificate ENCRYPTION BY PASSWORD = 'stR()Ng_PaSSWoRDs are?BeST!' WITH SUBJECT = 'ALTER DATABASE permission' GO CREATE LOGIN alter_db_login FROM CERTIFICATE alter_db_certificate GO
This login, in case you can’t tell from the name, will be used to propagate
ALTER DATABASE permissions. The next step is to grant the appropriate permissions to the login:
GRANT ALTER ANY DATABASE TO alter_db_login
At this point, the next step required is to back up the certificate to a file. The certificate can then be restored from the file into the database of your choosing, and from there can be used to create a database user that will have the same permissions as the server login, by virtue of having been created using the same certificate.
BACKUP CERTIFICATE alter_db_certificate TO FILE = 'C:\alter_db.cer' WITH PRIVATE KEY ( FILE = 'C:\alter_db.pvk', ENCRYPTION BY PASSWORD = 'an0tHeR$tRoNGpaSSWoRd?', DECRYPTION BY PASSWORD = 'stR()Ng_PaSSWoRDs are?BeST!' )
Once backed up, the certificate can be restored in a database. For the purpose of this example, a new database can be created and used to keep things simple:
CREATE DATABASE alter_db_example GO USE alter_db_example GO CREATE CERTIFICATE alter_db_certificate FROM FILE = 'C:\alter_db.cer' WITH PRIVATE KEY ( FILE = 'C:\alter_db.pvk', DECRYPTION BY PASSWORD = 'an0tHeR$tRoNGpaSSWoRd?', ENCRYPTION BY PASSWORD = 'stR()Ng_PaSSWoRDs are?BeST!' ) GO
It is worth noting that at this point, the certificate’s physical file should probably be either deleted or backed up to a safe storage repository. Although the private key is encrypted with the password, it would certainly be possible for a dedicated attacker to crack it via brute force. And since the certificate is being used to grant
ALTER DATABASE permissions, such an attack could potentially end in some damage being done—so play it safe with these files.
After the certificate has been created in the database, the rest of the process is just as before. Create a stored procedure that requires the privilege escalation, create a user based on the certificate, and sign the stored procedure with the certificate:
CREATE PROCEDURE SetMultiUser AS BEGIN ALTER DATABASE alter_db_example SET MULTI_USER END GO CREATE USER alter_db_user FOR CERTIFICATE alter_db_certificate GO ADD SIGNATURE TO SetMultiUser BY CERTIFICATE alter_db_certificate WITH PASSWORD = 'stR()Ng_PaSSWoRDs are?BeST!' GO
Finally, the permissions can be tested. In order for propagation of server-level permissions to work, the user executing the stored procedure must be associated with a valid server login, and the login must be impersonated rather than the user. So this time,
CREATE USER WITHOUT LOGIN will not suffice:
CREATE LOGIN test_alter WITH PASSWORD = 'iWanT2ALTER!!' GO CREATE USER test_alter FOR LOGIN test_alter GO GRANT EXECUTE ON SetMultiUser TO test_alter GO
test_alter login can be impersonated, and the stored procedure executed:
EXECUTE AS LOGIN='test_alter' GO EXEC SetMultiUser GO
This example was obviously quite simplistic, but it should serve as a basic template that you can adapt as necessary when you need to provide escalation of server-level privilege to database users.