SQL University: Multiple Paths and Ownership Chaining

Welcome back to our last class this term on security in SQL Server here at SQL University. According to the syllabus, you should be picking up history next week with Jorge Segarra. SQL Server has a rich history. I know you'll enjoy the coming week. However, before we move on, let's close on some intermediate security topics. First let's talk about something I call "multiple paths."

"Hi, my name Is... What? My name is..."

Within Windows, a Windows user can be a member of numerous security groups. And the way permissions work, permissions can be assigned to any, all, or none of those security groups. For instance, when I was an infrastructure and security architect, I wore several hats. Each of those hats was mapped to a security group. So if you looked at my membership, here's what you would have seen:

Domain Admins

Server Admins

DBAs

Perimeter Security Admins

Incident Response Team Members

It's entirely possible to allow multiple security groups to connect to SQL Server. For instance, on a particular SQL Server, Server Admins could be allowed in as well as DBAs. DBAs would have full rights over the SQL Server. Server Admins would have rights to use a couple of databases, but not all the databases on the SQL Server. So when I come into SQL Server, who am I? It looks like a complicated mess, but really it's not. I'm both a member of DBAs and a member of Server Admins. So as far as SQL Server is concerned, I come in as both. That's right, both. So that raises the next question: What are my permissions?

If you remember back to last class, I talked about how you could have multiple permission sets. We used the example of three sets, which I called sets A, B, and C. And we talked about how the permissions added up, unless there was a DENY involved. Well, what we were discussing then is possible based on the situation I'm presenting now. I'm authenticated by SQL Server against the login for Server Admins and against the login for DBAs. And since the DBAs have complete control over the SQL Server, I have complete control over the SQL Server.

Let's take another example. Assume I'm not longer in the DBAs security group. Let's say the Server Admins have access to a database called ServerInventory. The Incident Response Team Members have access to a database called IncidentResponse. And there's another database called DBAsOnly that only the DBAs can get into. Since I'm a member of Server Admins, I have access to ServerInventory. Since I'm a member of the Incident Response Team Members I also have access to IncidentResponse. But since I'm no longer a member of DBAs, I do not have access to DBAsOnly. Make sense?

Another way to think of it is imagine a row of lockers, like back in high school. Being a member of Server Admins gives me a key to one locker (database). Being a member of Incident Response Team Members gives me a key to a different locker. The school (SQL Server) looks and sees I'm a member of both groups. Because of this, it hands me both keys. But since I'm not a member of DBAs, I'm not handed the key to the locker for DBAsOnly. Hopefully that gives you a visual picture of how SQL Server handles who you are. This is done both at the server level as well as at the individual database level. And because I can hold multiple keys (logins), those can map to multiple permission sets, like what we covered on Wednesday. Really, this is no different than file and folder permissions at the operating system level. It works the same way in SQL Server as it does in Windows.

"Know your role!"

The Rock had many sayings when he was in World Wrestling Entertainment (formerly the World Wrestling Federation), but among them was, "Know your role!" When it comes to SQL Server, this saying is important. At the server level, it is possible to put a login into several pre-defined roles which we call fixed server roles. These give a blanket set of permissions. Take, for instance, the sysadmin fixed server role. That role allows you to have complete control over the SQL Server. If a login is a member of that role, it can do anything within that SQL Server. And just like at the server level, there are roles at the database level, too. There are fixed database roles which have preset permissions, just like server roles. There are also user-defined database roles. These are role you can create. They are just like Windows security groups in that they are designed to group together database users. Also, you can assign permissions against the role, just like with security groups. Therefore, at the database level, the best practice recommendation is straight-forward:

Create roles with logical names that correspond to the different levels of permissions in your database.

Assign database users to those roles.

Grant permissions directly to the roles, not individual database users.

Ownership Chaining

Ownership chaining is a feature of SQL Server. Basically if you have two objects, and they have the same owner, they can form an ownership chain. For instance, let's say I have a table and a view. The view refers to the table. If both objects have the same owner, then I can have an ownership chain. Let's say I have the ability to issue a SELECT against the view. But I have no permissions against the table. If I try to issue a SELECT directly against the table, I'll get an Access Denied error. But if I issue a SELECT against the view, it works. It works even though the view hits the table. Why? Because SQL Server allows an ownership chain to form.

Basically, SQL Server sees that the same user owns both objects. So it assumes that if the owner has built one object to refer to another, like our view referring to our table, the owner meant for the reference to work. And as a result, when you go from the view to the table, SQL Server won't check permissions on the table. It will assume the owner knew what he or she was doing when the reference was created. We can use this to control access to the base tables in our databases. By controlling access through functions, stored procedures, and views, we can control how the data is displayed and how the data is manipulated. With ownership chaining, access to the base tables happens, but only through the methods we've explicitly built. So, for instance, this won't work:

DELETE FROM dbo.SomeVeryBigTable;
GO

If an end user tries to execute this, that person will receive an Access Denied type of error. This is good because imagine if they were able to execute it. Then we're stuck in recovery mode and that's bad. Now imagine we built a stored procedure like the following:

CREATE PROC dbo.ControlledDelete
  @SomeID INT
AS
BEGIN
  DELETE FROM dbo.SomeVeryBigTable
  WHERE SomeID = @SomeID;
END;
GO

In this case the end user can still delete rows. But our stored procedure only allows the deletion of one row at a time, and you need to know the right ID. We now only have to grant EXECUTE rights against this stored procedure and everything will work as we'd like. We don't have to grant any sort of permissions against the table itself. All right, enough on the theory. Let's round out the week with a few demonstrations. Afterwards, look to our coach for some drills to help you get stronger in SQL Server security.

Practical Demonstrations: