By: Jeffrey Yao | Last Updated: 2018-01-23 | Comments | Security
There are many best practices and guidelines regarding security settings in SQL Server, such as granting users the minimum required permissions, setting the database trustworthy setting off, renaming the sa login, etc. But I seldom see examples illustrate if these practices are not followed and the potential harm to SQL Server security. In this tip we look at some detailed examples.
SQL Server is a pretty robust system especially when all security best practices are followed. However, in the real world, due to various business requirements, we cannot always follow all best practices, otherwise, Microsoft could simply just harden all security settings in the system, for example: removing options for users to configure any security settings.
For DBAs, as long as we understand the potential security loopholes, we will be more confident in making informed decisions and adopt necessary mitigation approaches. We will look at a few common cases to explore how security settings may cause unexpected problems.
Case 1 – Database user with db_securityadmin privilege gaining db_owner privilege in database
In this case, we will see how a user with db_securityadmin privilege can become a member of the db_owner role.
We will first create a database [DB1] and then create a login and corresponding user in [DB1].
|DB1||John||[John] is a user in DB1. [John] is a member of db_securityadmin.||[John] can get privilege of a db_owner member.|
Let’s first set up the environment. We will run the following code in a SSMS window [Win_1], connected as a sysadmin.
use master; if db_id('db1') is not null drop database DB1; create database DB1; alter database DB1 set recovery simple; go create login John with password='HelloWorld!', check_policy=off; go use DB1 create user [John] for login [John]; alter role db_securityadmin add member [John]; go
Now we will open another SSMS window (Win_2) and log in as [John].
After connecting as [John], we will then try to create a table as follows:
-- this is Win_2, logged in as [John] use DB1 create table dbo.TestTable (id int);
And we will get the following error:
Msg 262, Level 14, State 1, Line 3
CREATE TABLE permission denied in database 'DB1'.
This is expected because [John] is just a db_securityadmin member, who will not be able to do any DDL/DML work.
So how can [John] elevate himself to do DDL/DML or even more work, like creating/dropping a user? It is actually pretty simple.
In [Win_2] SSMS window, we can run the following code.
-- this is Win_2, logged in as [John] use DB1 create role TestRole; --first create a new role grant control on database::db1 to [TestRole]; --grant the maximum permission to this new role alter role [TestRole] add member [John]; --add [John] self to this new role -- now [John] can DML/DDL any database objects -- such as create/populate/select/drop a table create table dbo.table_john (id int) insert into dbo.table_john (id) values (1), (2); select * from dbo.table_john; drop table dbo.table_john;
The script mainly does two things, first it creates a database role [TestRole] and then grants database [Control] permission to this new role, and then adds [John] to this new role, thus [John] gains almost all privileges inside [DB1]. We can see this by running the following:
use DB1 select * from sys.fn_my_permissions(null, 'database') order by permission_name;
We will get a total of 74 permissions (this is what I got in SQL Server 2016), like the following:
Case 2 – Database trustworthy setting = 1
In this case, we will see how a database user in one database (with trustworthy = 1) can take ownership of another database.
We will create two databases and create two logins and their corresponding users.
|DB1||John||[John] is a db_owner member in DB1, [John] is not a user in DB2. [Mary] is a user of db_datareader in DB1||[John] can become db_owner of DB2 if DB1 has trustworthy set on|
|DB2||Mary||[Mary] is a db_owner of DB2|
Let’s first set up the environment.
We first open an SSMS window (Win_1) as a sysadmin and run the following code.
use master; create database DB1; create database DB2 alter database DB1 set recovery simple; alter database DB2 set recovery simple; go Use DB1; create user [John] for login [John]; alter role db_owner add member [John]; create user [Mary] for login [Mary]; alter role db_datareader add member [Mary]; go use DB2 create user [Mary] for login [Mary]; alter role db_owner add member [Mary]; go -- in DB2, we create a table and populate it with two rows Use DB2 create table dbo.t (id int, a varchar(100)); insert into DB2.dbo.t (id, a) values (1, 'hello'), (2, 'world'); go
Now we open another SSMS window [Win_2] and log in as [John]. In this new window [Win_2], we run the following script and we can find that [John] cannot access DB2 data:
-- this is Win_2 use DB1 print 'I am ' + quotename(user_name(), ''); select * from db2.dbo.t;
We get the following message:
So [John] cannot access DB2 objects, and this is as expected. Now even if we try to execute as [Mary] in [Win_2] and access DB2.dbo.t, we still cannot succeed as shown below:
-- still run inside Win_2 exec as user = 'mary' select suser_sname(), * from db2.dbo.t --delete from db2.dbo.t -- you can even delete revert
Now if we check the trustworthy setting of DB1, we can see that it is set to off (by default):
We will turn on the trustworthy setting for DB1 to show what happens if this is on (this would be done in window [Win_1] by a sysadmin):
alter database DB1 set trustworthy on; --check trustworthy setting of DB1, DB2 select name, is_trustworthy_on from sys.databases where name in ('db1', 'db2');
Now, we will run the previous two scripts again in [Win_2] window as user [John] and we will see:
- If run as [John] directly, we still cannot access DB2.dbo.t:
- But if [John] impersonates [Mary] (who is a db_owner of the DB2 database), [John] can access DB2 without problem:
Actually, through impersonation, if [DB1] is set with Trustworthy on, [John] is elevated to have the same privilege as user [Mary], which in this case, [Mary] is a db_owner of [DB2], even though [John] is not a user of [DB2].
For example, [John] can even add himself into the DB2 database as a db_owner member via the following dynamic code (run it in [Win_2] window):
use DB1 exec as user = 'Mary' --exec ('use db2; drop user [John]'); exec ('use db2; create user [John] from login [John]; alter role db_owner add member [John];') revert
We can verify it by running the following code in [Win_1] window.
We see [John] is now a user in [DB2] and is a member of the db_owner role.
Case 3 – A login with SecurityAdmin privilege can gain SysAdmin privilege
In Microsoft BOL, there is an important note about the [securityadmin] role as shown below:
Say we have a login named [John] and has [securityadmin] membership. We’ll see how [John] can access sysadmin privileges.
We first open an SSMS window [Win_1] as a sysadmin and run the following code:
-- this is window Win_1, logged in as sysadmin use master; select suser_sid('John') is not null drop login [John]; create login [John] with password='HelloWorld!', check_policy; -- add to [securityadmin] fixed server role alter server role securityadmin add member [John];
Now we open another SSMS window [Win_2] and log in as [John]:
-- this is window [Win_2] and logged in as [John] -- we create another login if suser_sid('Mary') is not null drop login [Mary]; create login Mary with password='HelloWorld!', check_policy=off; -- grant [Control Server] permission grant control server to [Mary];
Since [John] creates login [Mary], it means [John] knows Mary’s password, so John can open another SSMS windows [Win_3] and log in as [Mary]. Since [Mary] has [Control Server] privilege, [Mary] has almost exactly the same privilege as a sysadmin login, meaning [Mary] can create/drop databases, sp_configure anything or shutdown the SQL Server instance as shown below.
If [John] tries to do the same in [Win_2], he will not be able to.
In this tip, we have reviewed some cases how a SQL Server login or a database user can elevate to a higher privileged login / database user. This reminds us to be careful when granting such privileges and we need to put auditing in place in case such privileges are abused.
All scripts are tested in SQL Server 2016, but I expect them to be runnable on SQL Server 2008 and later versions as well.
There are a few other potential cases I did not mention in this tip, such as potential harmful CLR stored procedure when database trustworthy is on, or high privileged proxy accounts being utilized in malicious SQL Server Agent Jobs, or the cross database chaining setting and its potential impact to database user privileges, etc.
It may be worthwhile for you to dig deep and explore the potential risks. Only after knowing the risks and how such risks can materialize could we better mitigate these risks in our daily DBA practices.
You can also read a few security-related tips below:
- Ownership chaining in SQL Server security feature or security risk
- Microsoft SQL Server Security Best Practices
- Running SQL Server Agent with a least privilege service account
- SQL Server Security Checklist
Last Updated: 2018-01-23
About the author
View all my tips