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, InstantDoc 25145), 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 MyDomain\kalen 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:
- Sa is only a login, never a username.
- Dbo is only a username; it maps to a login that is the database owner.
- Logins never own objects and are never granted database permissions.
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"), http://support.microsoft.com/?kbid=272424, 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.
Prev. page
[1]
2
3
next page 
