Nowadays security and data privacy are in special focus. When I deliver a training, I always refer to a DBA as the “guardian of the data.” There are two aspects of being a guardian.
The first one is integrity. It includes tasks like checking database consistency, creating backups and in case of problems being prepared to fix the database by having well designed, comprehensive DR plan.
The second one is security. Making sure that only authorized users have access to the data, and only to those data that they really need.
There are a lot of source about security guidelines on the internet like blogs, technical articles, documentations. Though, I think there are some important options and a database combination which have lack of appropriate attention. In this article, I would like to focus on this combination of options and demonstrate why it is important to be aware of it and to handle it correctly.
Let’s play a Role Playing Game in which you are an Agent of the Impossible Database Mission Force with code name Bobby. By the way, Bobby’s first assignment was to delete the “Student” table… 😉 This time you (as Bobby) will use a subtler approach.
Here is your mission:
“Your mission Bobby, should you decide to accept it to steal a Very Important Data from the TargetDB database hosted by a SQL Server. You need to get this data and delete it (again).
We have an undercover agent who can assist you in a very minimal level, but his identity cannot be revealed. It is possible to get a Login for you, but unfortunately, as a security protocol, every login on the server has explicit DENIED permissions on the target database and the target data as well. The only information our agent can provide you to plan the action are the ones which were generated for verification by the security protocol during the creation of your login (see the attached files).
As always, should you or any of your I.D.M. Force be caught and/or fired, the Secretary will disavow any knowledge of your actions. This message will NOT self-destruct in five seconds this time 😉
Good luck Bobby!”
Database information from the target server.
As every decent agent, let’s do the homework and check what you are really dealing with.
You cannot just log in and connect to the TargetDB, since every single permission is denied for your login and its mapped user explicitly. You need to get in from another database. Unfortunately, cross-database actions are not easy things to do. Consider it as a closed door with two locks on it. I am not going to drill down into the technical details. You can do that by reading the Extending Database Impersonation by Using EXECUTE AS MSDN article (I highly recommend to do so).
The first lock is “Trusting the authenticator” and the second is “Trusting the database.”
Let’s start with the first lock. Trusting the authenticator means that for access to another database B, the owner of database A must have been granted (explicitly or implicitly) the AUTHENTICATE permission in database B.
Wait a minute, the authenticator on database level is the owner of the database itself (Don’t mix it up with the db_owner database role!). Check the database owners.
As you can see even though they follow a pretty good practice by using one account per server (more about that later on), which is not SA, this way they just kindly opened the first lock to you.
Let’s focus on the second lock. So somehow, you need to manage to have a database created on the server with TRUSTWORTHY property ON. Everybody knows that the best practice here is to set the TRUSTWORTHY database property to OFF. Maybe not everybody knows why, but it doesn’t matter right now since such a request would be very suspicious.
This is the time when we should say: “that is a really an Impossible Mission”…or not? What if you already have such a database?
It is the MSDB database.
The second lock is done. You did not even need to “break” any of the locks.
Right now, you have to deal with one challenge: somehow you need to get into the MSDB database with db_owner database role.
Why do you need the db_owner database role? Because it has implicit, impersonate permission, but obviously, you cannot ask the database administrators to give you access to the MSDB with explicit impersonate permission. That would be also very suspicious.
Since MSDB is not usually in the database administrators’ focus (more about that later on) I think this mission is not impossible anymore. I don’t want to give any tips and tricks how you can manage to have this access, so I leave it to your imagination, but let’s assume that somehow you got it.
Let’s see what Bobby is capable of just because he got a user with the db_owner database role in the MSDB database:
Try to connect to the TargetDB:
This is an expected error. Remember the security protocol applied on the provided login.
Let’s start it…
Try to connect to the TargetDB and to select the target data:
It works! Let’s modify it and after that verify the action.
That’s all, it is done.
Like a piece of cake. MISSION ACCOMPLISHED!
As you saw, in this article I focused on a particular security database configuration combination. Those were the owner of the database and the TRUSTWORTHY database option paying particular attention to MSDB. The presented scenario was just one in which sensitive data can be compromised in the same way.
This article is the first in a series of three that are related to a particular security database configuration combination. This was just an introduction. I hope you have enjoyed it. In the next articles I will explain the reason why I thought that this article (the combination of the options introduced), this topic is important.
Latest posts by Robert Virag (see all)
- SQL Server Security Ponderings – Part 2 | Database owner + TRUSTWORTHY - May 5, 2017
- SQL Server Security Ponderings – Part 1 | DMI – Bobby Returns - December 5, 2016