 |
|
| SQL Server Tips: |
|
|
|
DATABASE MANAGEMENT AND ADMINISTRATION
Ownership chaining in SQL Server 2005
Serdar Yegulalp, Contributor
09.20.2006
Rating: -3.75- (out of 5)
|
There are many occasions where multiple database objects access each other one after the other. One example is a stored procedure that invokes a view, which, in turn, invokes a table. When this happens, SQL Server has to evaluate the permissions on the referred objects. It does so in a way unlike what would happen if those same objects were being accessed directly. Such arrangements are called ownership chains. Understanding how they work is important if you're using one type of object to access another in an environment where not everyone is granted equal access to everything (and shouldn't be).
There are three basic rules in ownership chaining:
- If you have access to a given object, and then reference another object through it (such as a view that accesses a table) where both objects have the same owner, permissions on the second object are not checked. If you have access to the first item, it's assumed you'll have access to the second.
- If you have access to a given object, and then reference another object through it, but the second object does not have the same owner, the permissions on the second object are checked against the retrieving user's permissions. If the user doesn't have permissions that allow access to that second object, he can't read it.
- All of this is instigated in the security context of the user who invokes the original object. If the user doesn't have access to that original object, nothing happens (which is just standard security practice).
With one key exception, these rules work regardless of the object types being accessed. The rules do not apply if you're chaining to an object in another database within the same instance of SQL Server. This kind of access is called cross-database ownership chaining (CDOC). By default, this feature is turned off in SQL Server because it's a bit too easy to use as a way to defeat object se...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
curity for someone with local access and malice in mind.
SQL Server 2005 does have some new features, making it possible to work across databases or differently owned objects without having to explicitly grant access or use CDOC. For instance, if you use CDOC to allow cross-database access for a stored procedure, you might be better off using something like a Service Broker queue to do the same thing. Or, you could create a matching stored procedure on the other database that has been written to securely handle such things.
Another change to the way ownership chaining works in SQL Server 2005 involves the EXECUTE AS clause, which lets you run many operations in the context of another user. It can only be used by someone who has the authority to impersonate other users, i.e., a sysadmin. While it lets you choose which identity to use if permissions are checked during execution, it doesn't force them to be checked (although there's an exception to this which I'll go into in a moment).
EXECUTE AS is useful in a variety of ways. Primarily, it allows you to easily simulate (in the context of other activity) whether a given user can access something in a chained fashion. This is an important function considering ownership chains are often taken for granted. Because of the way chaining works, it's not always obvious you will (or won't) have access to a dependent object, therefore it's a good idea to audit such things whenever possible using EXECUTE AS. It's also useful as a way to run a potentially risky operation in a restricted-user context. Another benefit is if you create a stored procedure that uses EXECUTE AS, you can define more precisely the security context the stored procedure runs in.
In addition, EXECUTE AS can force an ownership chain to break by using EXECUTE AS SELF. If you use EXECUTE AS SELF in the context of a stored procedure, for instance, permissions on any of the underlying objects called by the stored procedure will be explicitly checked. This sort of thing happens on multiple levels, including when you invoke a stored procedure that calls another stored procedure and, for instance, when EXECUTE AS SELF is being used somewhere in the chain.
Note that EXECUTE AS will not work if SQL Server is running as a local account. When it's set up as a local account, it can't read the group memberships of Windows domain accounts. Be aware of this if you happen to have an instance of the SQL Server Desktop Engine installed and you're running it as a local account for the sake of security.
About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
More on SearchSQLServer.com
Tip: Granting permissions in SQL Server 2005
Book excerpt: SQL Server permissions and new objects
Expert advice: Access across databases when executing a stored procedure
|
|
|
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
