Database Security in SQL Server 6.5 - SETUSER
(Page 5 of 8 )
Only dbo (and, of course, sa) can run the SETUSER command. Unlike sa, SQL Server forces dbo to adhere to the permissions set on objects in the database, which means that an object owner can prevent dbo from managing objects in the database she owns. That is clearly not an acceptable situation, so we have the SETUSER command to allow dbo to impersonate another user in the database. While the consequences of the SETUSER command are in effect, dbo has all the rights, privileges, and permissions of the user, including those of dboo if the user owns any objects. Changing permissions on an object is just a matter of executing the SETUSER command and then making the changes while impersonating the dboo. As you will see in some examples in the next section, this ability can be useful when creating objects that will be owned by another user or in testing permissions for a given user.
You’ll shortly explore assigning permissions, but first I need to cover a couple of topics: who can view the tables in your database and who can execute stored procedures.
Viewing System Tables By default, SQL Server grants permission to query system tables to all users when it creates a new database. No one can update or delete rows in the tables unless he executes a special system stored procedure (i.e., sp_configure 'allow updates', which only sa can use), but he can run SELECT queries on them. So far in 9 years, I have never found a reason to prevent users from seeing the contents of these tables; but you should know they have the ability, in case you have a need to prevent it.
Executing Stored Procedures Because all the system stored procedures reside in the Master database, and the guest account is active in that database, and because the guest account has EXECUTE permission on the system stored procedures, every database user can run them, with a couple of exceptions. The system stored procedures that perform a task only dbo or sa should be able to perform (for example, sp_adduser) check the UID (which is always 1 for dbo)to prevent unauthorized use. All the rest of the procedures, however, can be run by anyone.
If you need to limit access to a system stored procedure, you must first run sp_adduser to map the SID to a UID in the Master database. Then, you will need to revoke the execute permission from the user. Once again, I have found no reason to worry about this, but you should know about it just in case it makes a difference in your environment.
Finally, because system stored procedures reside in the Master database, individual dbos who do not have sa privileges have no way of limiting their usage within their databases. In addition, there is no way to confine the effects of anything your system administrator does to a single database. Denying someone access to a system stored procedure means that person cannot run the procedure not only in one particular database, but also on the server as a whole. You should, therefore, think carefully about the ramifications of changing the default setup.
The Art of Assigning Permissions Assigning permissions in SQL Server 6.5 is really more of an art than a science. Although SQL Server does follow some specific rules, the algorithm has eight decision points. This makes it difficult—but not impossible, as you’ll see—to decide exactly what steps you need to follow to change a user’s permissions in a particular way. First, you’ll examine the rules SQL Server follows, and then you’ll go on to look at examples of how you can work with these rules to attain the assignment of your desired permissions.
What Happens in Sysprotects?
Everything starts with the database system table sysprotects; therefore, let’s look at how your assignments of permissions affect it.
GRANT and REVOKE Each time you issue a GRANT or REVOKE statement, a row is either added or removed from sysprotects to change the list of permissions.
NOTE SQL Server 6.5 has a different behavior prior to Service Pack 5. Because Service Pack 5a (version 6.5.416) is the standard for this book, I focus on how it behaves. Just remember that if you work on a system with an earlier service pack, the description in this section will not be accurate.
Here are the rules SQL Server follows and the steps it takes.
For GRANT statements:
- If no row exists matching the object, user, permission, and action, add one and exit.
- If a row matches the object, user, permission, and action, do nothing and exit.
- If a row matches the object, user, and permission but has a revoke action (a value of 206), change the permission to a grant action (205) and exit.
- If the public group, or a group of which the user is a member, has a row matching the object and permission, add a row and exit.
For REVOKE statements:
- If no row exists matching the object, user, permission, and action, do nothing and exit.
- If a row exists matching the object, user, permission, and action, do nothing and exit.
3. If a row matches the object, user, and permission but has a
grant action (205), remove the row and exit.
4. If the public group, or a group of which the user is a member,
has a row matching the object and permission, add a row and
exit.
Group Permissions Permissions affecting groups add a little complexity—group and user permissions follow a hierarchy in which permissions assigned to a user override permissions assigned to a user-defined group, and both user-specific permissions and group permissions override permissions assigned to public. In other words, permissions assigned specifically to FredJ override permissions assigned both to the Test group and to public, and permissions assigned to the Test group override permissions assigned to public. The rules for groups are as follows.
For GRANT statements:
- If no row exists matching the object, group, permission, and action, add a row and exit.
- If a row exists matching the object, group, permission, and action, do nothing and exit.
- If a row matches the object, user, and permission but has a revoke action (206), change the action to 205 and exit.
For REVOKE statements:
- If the public group has a row matching the object and permission, add a row and exit.
- If no row exists matching the object, group, permission, and action, do nothing and exit.
- If a row exists matching the object, group, permission, and action, do nothing and exit.
- If a row matches the object, user, and permission but has a grant action (205), remove the row and exit.
These rules make a lot more sense if you run a few examples, so let’s do that now. Each of the samples in the sections that follow assumes the Test database has the users JaneD, Joe_Smith, and FredJ and the user-defined group test. If you didn’t set this up according to the instructions at the beginning of the chapter, go ahead and do so now.
Granting and Revoking Permissions
Run the following commands:
SETUSER 'Joe_Smith'
SELECT * FROM PermDemo
SETUSER
GRANT SELECT ON PermDemo TO Joe_Smith
SELECT a.name, b.*
FROM sysobjects a, sysprotects b
WHERE a.id = b.id AND a.name = 'PermDemo'
SETUSER 'Joe_Smith'
SELECT * FROM PermDemo
SETUSER
The following table shows the result of the preceding code.
name id uid action protecttype
PermDemo 16003088 3 193 205
You should see that there is a new row in sysprotects.Joe’s first SELECT statement on PermDemo will fail, and his second one will succeed. This is a good demonstration of the fact that if a user does not have permissions on an object, that user cannot do anything with it. If you run the commands a second time, you will notice that the new row is unchanged and that Joe’s first SELECT now works.
In this next example, you revoke the SELECT permission from Joe_Smith:
SETUSER 'Joe_Smith'
SELECT * FROM PermDemo
SETUSER
REVOKE SELECT ON PermDemo FROM Joe_Smith
SELECT a.name, b.*
FROM sysobjects a, sysprotects b
WHERE a.id = b.id AND a.name = 'PermDemo'
SETUSER 'Joe_Smith'
SELECT * FROM PermDemo
SETUSER
The following table shows the result of the preceding code.
name id uid action protecttype
Now if you run this example, you will notice there are no rows in sysprotects, and Joe’s second SELECT on PermDemo fails. This is an example of how the REVOKE command removes GRANT permissions by removing the row from sysprotects. If you run the commands a second time, both of Joe’s SELECTs will fail, and there will be no rows in sysprotects. This is the behavior that was implemented in Service Pack 5.
 This article is excerpted from SQL Server Security Distilled by Morris Lewis (Apress, 2004; ISBN 1590592190). Check it out at your favorite bookstore today. Buy this book now. |
Next: Working with Groups >>
More MS SQL Server Articles
More By Apress Publishing
