Join Adam Wilbert for an in-depth discussion in this video Understanding ownership chains, part of Database Foundations: Administration.
- When working with data stored in SQL Server, it's often that a user isn't directly accessing the data tables. They'll use a stored procedure or run a report that pulls in a view which will in turn fetch the data from a data table. When this happens, SQL Server relies on something called ownership chains in order to make sure that the database isn't exposing sensitive data to unauthorized users. Ownership chains apply to views, stored procedures, and user defined functions. Let's take a look at how they work by exploring the Invoices Totals view inside of the H+ Active database. We can find it by expanding the views table here, and it says View right there.
Let's right click on it, and open it up in the Design view. We can see that the invoices Totals view pulls in information from five different tables inside of the H+ Active database. Let's go ahead and close this down, and if we right click on it and choose Select Top 1000 Rows, we can see that the data is being processed into these four different columns here. Since I am the system administrator and the owner of all the tables in the view that assembles them, I can grant permissions to the view and choose to keep the tables out of reach. You might think that if a user doesn't have access to the tables, then the view won't function properly, but because of the ownership chain, SQL Server will allow my view to process correctly regardless of who is viewing it as long as I'm the one that gave them that permission.
Essentially, the ownership chain will allow the view to pull out the data from the tables without granting direct permission to the tables. Let's create a new user and explore how this works. I'll go ahead and close this down, and in the security folder for the whole system, I'll go into Logins, and I'll right click and choose New Login. Let's create a new SQL Server authentication login for a user named Brian. I'm going to give him a password of just 123 for now. I'll confirm that number as 123, and I'm going to turn off to enforce the password policy. We're not going to deal with that at this point.
Next, let's go over here to the User Mappings and attach Brian to the H+ Active database. I'll go ahead and say OK to make that change, and then back in the H+ Active database, I'll come into the Security folder, into Users, I'll find Brian's new account here, I'll right click on it, and go to Properties. Then, in the Securables section, I'll press the search button. I'll choose to search by objects of a specific type using the second option here. I'll press OK, and then we want to add in some views to Brian's account, but not any tables.
I'll make sure to check the Views option and press OK. That'll add in all of the different views that are in this database, including the two database owner views here plus all of the system views down below. Now, if you need to resort them, you can click on this Schema button up here, and we should sort it so that the dbo ones are at the top. Let's grant Brian access to the Invoices Totals view, so I'll select it from the list here, and then come down to the permissions section, and I want to grant him select permission, so that he can read the information from this view. I'll place a check mark here, and press OK. That'll save Brian's permissions, and now we can log out of the server, and we'll log back in as Brian and see what this looks like.
I'll change the authentication to SQL Server Authentication. We'll type in the username as Brian and his password as 123. Go ahead and say Connect. Then we'll browse out to our database. We'll come into the H+ Active database, and if we expand the tables folder, you'll notice that we don't see any of the data tables, but if I come into the Views folder, we'll see we have access to that one specific view that we granted him the permission to. I can right click on it and say Select Top 1000 Rows, and we'll see that the data gets processed like normal. So using Views and Stored Procedures to filter process your data is a great way to further secure the information that's stored in the database.
Ownership chains make it possible to grant users permission to the processed data and keep the original sources tucked safely out of sight.
Note: This course will also prepare certification candidates for the "Administer a database" domain of the Microsoft Technology Associate (MTA) Exam 98-364, Database Administration Fundamentals.
- Securing the database server
- Understanding Windows authentication vs. SQL Server authentication
- Assigning fixed server roles and fixed database roles
- Granting object-level permissions
- Understanding ownership claims
- Creating backups
- Restoring a database