SQL Server 2014: protection against Database Administrator

Forrards
March 30, 2023

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)

Forrards
March 30, 2023
Share
Recent articles:
Database consultancy: SaaS database considerations
March 31, 2023

Utilizing given approach each client get his own database. In such case every client`s data stays isolated from others. It simplifies backing up the data, installing new clients but causes higher cost of maintenance.

More
Database consultancy: tree-like structure
March 31, 2023

Subject recursion is well covered in the literature, but, nevertheless, the problem of output “tree” does not mean the client and SQL Server many baffled. So, put the problem: there is a table with the name and record id field indicating the parent identifier. Immediately fill in this table, some sort of test data:

More
Database consultancy: Hierarchy in MS SQL Server
March 31, 2023

In our time of greatest prevalence of databases were relational databases, which are the main targets of the tables and the relationships between them. Tables can solve most problems for data storage and manipulation. But in the real world entity requiring storage is not always presented in a tabular form. One of these very common types of data structures other than the table is a tree structure, where each data element is the parent and the offspring. An example of such a structure may be the structure of state enterprises, which is headed by the director (the root of the tree), his deputies, heads of departments from which are subject to certain deputies, employees of departments, which are subject to the rulers.

More

Subscribe to our blog

    

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Database consultancy: SaaS database considerations

Utilizing given approach each client get his own database. In such case every client`s data stays isolated from others. It simplifies backing up the data, installing new clients but causes higher cost of maintenance.

March 31, 2023
Database consultancy: tree-like structure

Subject recursion is well covered in the literature, but, nevertheless, the problem of output “tree” does not mean the client and SQL Server many baffled. So, put the problem: there is a table with the name and record id field indicating the parent identifier. Immediately fill in this table, some sort of test data:

March 31, 2023

Discuss your project with us

    

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.