Crossing the Line: Ownership Chains

Security concerns affect almost every action of every SQL Server user, administrator, and developer. SQL Server manages multiple collections of entities that are organized hierarchically, with the server at the top. Beneath the server level is a collection of databases, and beneath that are objects. SQL Server's security model has two parts: authentication and authorization. Authentication is a process by which SQL Server validates and establishes the identity of an individual attempting to access an object. Authorization is the process by which SQL Server decides whether a given identity is allowed to access a requested object.

In this and upcoming columns, I'll be discussing some aspects of SQL Server 2000 security that frequently cause confusion. By understanding these topics, you not only can avoid confusion and better secure your SQL Server system today, you'll better appreciate the changes in Yukon, the next release of SQL Server. In this month's column, I look at a topic that involves elements of both authentication and authorization: cross-database ownership chaining.

Chain, Chain, Chain

In his article "Security Through Ownership Chains" (July 2002), Dan Guzman discusses in detail the concept of ownership chains and describes their value as a security feature. Within a database, ownership chaining involves only authorization—a user who's authorized to execute a stored procedure is automatically authorized to perform any data-access operations the procedure specifies on objects that have the same owner as the procedure. The security benefit is that users don't have explicit permission to access the objects; they can access only the data that the stored procedure allows them to access, and only if all internal tests in the procedure are satisfied.

For example, imagine that you have to grant user1 SELECT permission on table1 to let that user use the proc1 stored procedure to view data from table1. Proc1 might include a WHERE clause that selects only the rows that are relevant to user1, but because user1 has SELECT permission on table1, she can read all the table's rows. Because of ownership chaining, user1 doesn't need SELECT permission on table1 if table1 has the same owner as proc1. If the procedure owner isn't the same as the owner of the referenced table, you have a broken ownership chain. In that case, SQL Server must make sure that the user executing the procedure is also authorized to access the underlying object. For this reason, Microsoft recommends that all objects within a database be owned by the same owner; many DBAs find that making the dbo user the owner of all objects is the easiest way to manage ownership chaining.

One frequently misunderstood ownership-chain concept is the difference between a login and a username. Although a login and a username might look the same, you need to understand the differences between them. You use a login—either a Windows login such as MyDomainkalen or a SQL Server login such as sa—to gain access to SQL Server.

When you use the sp_grantdbaccess procedure to grant a login access to a database, SQL Server associates that login with a username in that database and stores the username in the database's sysusers table. A column in the sysusers table includes a security ID (SID) that SQL Server uses as a foreign key to reference the master database's sysxlogins table, which holds the login. Thus, each username in a database maps to a login in the master database. The process of allowing access to a database and assigning a username to a login is beyond the scope of this article, but here are a few key points about logins and usernames that you need to be aware of:

Crossing Databases

Guzman's article briefly mentions special difficulties involved with ownership chains crossing databases; in this article, I go into more detail. In cross-database ownership chaining, an object in one database references objects in another database. And when a user accesses the first object, SQL Server needs to determine the user's identity in the other database. So, in addition to authorization, cross-database ownership chaining involves authentication.

Because usernames are local to a database, figuring out a user's identity in another database isn't always straightforward. The Microsoft article "INF: Object Ownership Chain Checking Across Databases Depends on the Login That Is Mapped to the Object Owners" (which was originally titled "INF: Object Ownership Chain Checking Across Databases Depends on Database Ownership"), originally included completely incorrect information. The article stated that if a procedure in one database accesses objects in another, SQL Server won't check the permissions on the referenced objects if the two databases have the same owner. In other words, the article said that the ownership chain depends not on ownership of the objects but on ownership of the databases that contain the objects. This statement is inaccurate, and Microsoft corrected the article after I reported the mistake.

Let me reiterate: Cross-database ownership-chain checking doesn't depend on the database owner but rather on the logins to which the object owners map. Some people might not realize the distinction, especially if they follow Microsoft's recommendation that the user dbo should own all objects. Regardless of who owns the objects, the difficulties described in the Microsoft article still apply. Suppose user1 in database1 owns view1, and view1 references table2 in database2, which is owned by user1 in database2. User3 in database1 has permission to select view1's data. If user3 tries to retrieve the data from view1, will SQL Server check user3's permissions on table2 in database2?

The Microsoft article now says that you need to ask instead: Do user1 in database1 and user1 in database2 map to the same login in the master database? If so, SQL Server doesn't need to check user3's permissions in database2; otherwise, SQL Server needs to verify that user3 has permission to access table2 in database2. In general, ownership chaining is good because it lets you encapsulate activities that a particular user or group of users can perform and not give users any more permissions than they need. However, when the referenced objects exist in separate databases, database chaining can compromise security.

One reason for putting objects and users into separate databases is to provide different security environments. For example, login Dan might have many permissions on one set of tables in the company's Inventory database and might even belong to the db_owner role, but in the HR database, he might be a regular user with a limited set of permissions. If the sa owns both databases, the username dbo maps to the sa login for both databases. If dbo owns a table full of sensitive data in the HR database, Dan shouldn't be able to read it because he's not privileged in the HR database. But because Dan belongs to the db_owner role in the Inventory database, he can create a view there that accesses the sensitive data in the HR database. Dan can make dbo the owner of his view so that the view in Inventory and the table in HR have the same owner (i.e., the owners map to the same login—in this case, sa). So, from the Inventory database, Dan can select from the view he created and see data in HR that he isn't supposed to see.

Changes in SP3

If you want to allow a privileged user in one database access to data in another database in which he isn't so privileged, that's your choice. But as of SQL Server 2000 Service Pack 3 (SP3), Microsoft assumes that you don't want to. Microsoft article "INF: Cross-Database Ownership Chaining Behavior Changes in SQL Server 2000 Service Pack 3"  describes a new behavior that takes effect when you install SP3. If you want users of an object in one database to automatically be able to access dependent objects in another database that has the same owner, you must enable a new database option called db chaining in both databases.

As a shortcut, you can enable a serverwide option called cross DB ownership chaining that allows database chaining in all databases. Note that when you upgrade to SQL Server 2000 SP3 or SP3a, you can enable cross-database ownership chaining at the server level. The default is to not enable it. If you leave the option at the default, cross-database ownership chaining isn't in effect. Keep in mind that this behavior is different from the behavior in earlier releases, so applications that depend on cross-database ownership chaining probably won't work when you upgrade.

Also note that this server option isn't available through Enterprise Manager. You must use the sp_configure stored procedure as follows:

EXEC sp_configure 'Cross DB Ownership Chaining', 1RECONFIGURE

To illustrate SP3's new behavior, log in to Query Analyzer as a system administrator, then run the code that Listing 1 shows. The code creates two new databases, database1 and database2, then creates in database2 a table containing sensitive data. Next, run the code in Listing 2, which adds a new login called user1 and grants it access to both databases, in which by default the corresponding username is user1. In database1, the code adds user1 to the db_owner database role.

Now, open a new connection by choosing File, Connect and log in as user1 (which has no password). First, verify which database you're in, then try to select the sensitive data from database2:

— Log in as user1.SELECT db_name()SELECT * FROM database2.dbo.sensitive_   data

You should get this error:

Server: Msg 229, Level 14, State 5, Line 1

SELECT permission denied on object 'sensitive_data',database 'database2', owner 'dbo'.

Next, create in database1 a dbo-owned view that selects the rows from the sensitive_data table in database2. Try to select from that view:

CREATE VIEW dbo.sensitive ASSELECT * FROM database2.dbo.sensitive_dataGOSELECT * FROM dbo.sensitiveGO

If you're running SQL Server 2000 SP3 and you haven't enabled cross-database ownership chaining, you should get an error message that permission on the sensitive_data table is denied.

Go back to the connection where you're logged in as an administrator. To enable cross-database ownership chaining, you can either set the global configuration option or set a database option in both databases involved in the chain, as the code in Listing 3 shows.

Finally, return to user1's connection, and try again to select from the view:

— You're logged in as user1.SELECT * FROM dbo.sensitiveGO

This time, you should be able to see the sensitive data in database2 even though user1 has no special rights in that database. You can't select the data directly, but you can build a view that lets you perform an action that you shouldn't be able to do as user1. In addition, when cross-database ownership chaining is in effect, you as user1 could create a dbo-owned stored procedure that lets you not only retrieve the data in sensitive_data but modify it as well.

One important fact isn't mentioned in the second Microsoft article or the Guzman article. Even if all the conditions are met—that is, db chaining is enabled in both databases, and the view and table owners map to the same login—if user1 has no access to database2, an attempt to select from the dbo.sensitive view will fail. You can test this behavior by removing user1's access from database2:

— You're logged in as sa.USE database2EXEC sp_revokedbaccess user1

Finally, return to user1's connection and try to select from the view:

— You're logged in as user1.SELECT * FROM dbo.sensitiveGO

This time, you get a different error message:

Server: Msg 916, Level 14, State 1,    View Sensitive, Line 2Server user 'user1' is not a valid    user in database 'database2'.

Although the SP3 behavior is more secure than the previous behavior of allowing cross-database access to act just like ownership chains within a database, it's still a little too broad. It would be nice to be able to choose who has what access across databases, but we'll have to wait for that capability. I hope we won't have to wait too long.