Blog Post

SQL Server 2014: protection against Database Administrator

,

Such a topic related to SQL Server 2014 CTP1 may seem a bit strange, but it was chosen not randomly. The questions like “How can I protect my data against the DBA?” appeared on forums regularly.

Until recently this problem was not solved, but thanks to the emergence of 3 new server-level permissions in SQL Server 2014 (CONNECT ANY DATABASE, IMPERSONATE ANY LOGIN and SELECT ALL — USER SECURABLES) now we are more flexible in managing server roles. In particular, we can create an administrator’s server role with total restriction on access to user data.

Let me remind you that for the first time the option to create server roles appeared in SQL Server 2012 (CREATE SERVER ROLE) and in SQL Server 2014 developers went a little further.

But let’s go step by step. First, we create a new server role NewServerRole that belongs to the fixed server role sysadmin (members of sysadmin fixed server role can perform any activity on the server).

create server role NewServerRole authorization sysadmin;

go

Now for demonstration purposes we create a new login and include it in our server role NewServerRole. Do not forget to set up mixed mode authentication before (right-click on our database server => Properties => Security => Server authentication) and restart SQL Server.

sysadmin

create login NewLogin with password = 'Qwerty1';

go

alter server role NewServerRole add member NewLogin;

go

Now we grant CONTROL SERVER rights to our server role

grant control server to NewServerRole;

go

Now we can connect to our database server under a new login NewLogin and carry out a set of instructions to make sure that we have full rights.

--?reate new login

create login NewLogin2 with password = 'Qwerty1';

go

-- Change server options

exec sp_configure 'show advanced options', 1;

reconfigure;

go

exec sp_configure 'xp_cmdshell', 1;

reconfigure;

go

--Execute xp_cmdshell

exec xp_cmdshell 'dir c:';

go

--Go to data from master database

select * from master..spt_values;

go

And now we restrict our server role access to all user data:

deny select all user securables to NewServerRole;

go

Next step is to start session under our test login NewLogin and try to run the following script:

--Create new login

create login NewLogin3 with password = 'Qwerty1';

go

--Change server options

exec sp_configure 'show advanced options', 1;

reconfigure;

go

exec sp_configure 'xp_cmdshell', 1;

reconfigure;

go

--Run xp_cmdshell

exec xp_cmdshell 'dir c:';

go

--Go to data from master database

select * from master..spt_values;

go

As a result we will be able to carry out all the steps except the last one:

The SELECT permission was denied on the object ‘spt_values’, database ‘master’, schema ‘dbo’.

 Below is a number of scenarios with new permissions:

  • DBA with no access to user data (as in the example above)
  • DBA with no right to change logins
  • DBA with no right to give permission impersonate (to replace context) for other logins
  • The role with the opportunity to read ALL user data without administrator rights
  • The role with the opportunity to view different metadata, without access to user data
  • The role with the opportunity to impersonate yourself with any login except for those with administrative privileges (sa, sysadmin)

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating