Troubleshooting Security Issues

There are inherent problems in trying to secure any environment. Two types of situations are bound to arise during the course of any implementation. You are likely to witness situations in which a user cannot access the data needed because of incorrectly applied restrictions, and conversely someone may be able to gain access to confidential information that he has no rights reading.

The SQL Profiler would be the tool of choice for troubleshooting most security issues because it has the capability to audit and review activity that occurred on an instance of SQL Server. This allows a security administrator to review any of the auditing events, including the success and failure of a login attempt and the success and failure of permissions in accessing statements and objects.

Dealing with Broken Ownership Chains

One problem that can arise when you are using views occurs when you have different owners for objects in your database. Whenever there is a change in ownership, the owner of each object has to grant permissions to the object. When the ownership of objects in a chain is changed, there is a break in ownership or you have a broken ownership chain.

When you're dealing with ownership chains, the single point that will reduce permission management for you is to have a single owner for the entire chain. The dbo makes a nice owner for all objects in the database. This means that you have to apply permissions only onceto the upper-level objects. If you have a broken ownership chain, you may have to apply permissions to objects along the entire chain, which makes it more difficult to implement permissions and provides reduced security. The reduced security is caused by the additional permissions granted to subsequent objects, which may create holes in your data security.

Ownership chains and the problems that can occur have been a favorite topic for SQL exams for a few years now. Expect to see at least one question dealing with objects that have multiple ownership.

If the same person owns a series of objects, such as views and the base tables, then he or she has to grant permissions to only the upper-layer objects or views. This is designed to make administration easier. If you don't own all the objects, permissions can falter in use of the objects.

In almost every database scenario, object ownership should be limited to a single user: dbo.

Ownership chains were designed to make it easier for you to assign permissions, and to enhance security by requiring users to have permissions to only the upper-level objects, such as views or stored procedures. As long as the same person owns all the objects in the chain, permission is only checked at the first object that she accesses. In this case, Mary was granted permission to the view (DBOPermsView) but was denied access to the table (DBOPermsTable). Because the dbo owns both objects, as access moves from the view to the table, the permissions are not checked, and Mary has access to the data. However, if Mary attempts to access the table directly, the permissions are checked at the table and she is denied access. The reasoning behind the ownership chain works like this: If I own a table and I own the view, then when I grant permissions to the view, I obviously want the user to have access to the table. By not granting specific permissions to the table, you also restrict access to the data because this data is accessible only through the view.