Database Security in SQL Server 6.5 - Working with Groups
(Page 6 of 8 )
In this example, you assign the SELECT permission to public:
SETUSER 'Joe_Smith'
SELECT * FROM PermDemo
SETUSER
GRANT SELECT ON PermDemo TO public
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
SETUSER 'JaneD'
SELECT * FROM PermDemo
SETUSER
The following table shows the result of the preceding code.
name id uid action protecttype
PermDemo 16003088 0 193 205
As you would expect, Joe’s first SELECT on PermDemo fails, but both his second SELECT and Jane’s SELECTs succeed. As in the first example, sysprotects contains a single row, but in this case, uid indicates a group instead of a user, because 0 is the UID for the public group.
Things get interesting in this next example. You’ll see what happens when you repeat the second example and try to revoke the permission to SELECT 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
PermDemo 16003088 0 193 205
PermDemo 16003088 3 193 206
The difference now is that the public group has permissions assigned to it. SQL Server adds a second row to revoke Joe_Smith’s SELECT permission that he receives from public. Now when the server checks permissions, everyone other than Joe will have SELECT permission. Once again, running the REVOKE command a second time will not affect the rows in sysprotects.
Now let’s see what happens if you revoke the SELECT permission from the public group:
SETUSER 'JaneD'
SELECT * FROM PermDemo
SETUSER
REVOKE SELECT ON PermDemo FROM public
SELECT a.name, b.*
FROM sysobjects a, sysprotects b
WHERE a.id = b.id AND a.name = 'PermDemo'
SETUSER 'JaneD'
SELECT * FROM PermDemo
SETUSER
The following table shows the result of the preceding code.
name id uid action protecttype
PermDemo 16003088 3 193 206 Now, the row in sysprotects granting SELECT permission to the public group is gone, leaving just Joe_Smith’s REVOKE row. It turns out that this series of commands in this order is the only way to explicitly deny a permission to a user. You have to grant the permission to a group, revoke it from a user, and then revoke it from the group to leave just the user with a revoked permission.
Before you see how to eliminate that row, you’ll first look at what happens when you grant the SELECT permission to the test group, of which Joe_Smith is a member:
SETUSER 'Joe_Smith'
SELECT * FROM PermDemo
SETUSER
GRANT SELECT ON PermDemo TO test
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
SETUSER 'FredJ'
SELECT * FROM PermDemo
SETUSER
SETUSER 'JaneD'
SELECT * FROM PermDemo
SETUSER
The following table shows the result of the preceding code.
name id uid action protecttype
PermDemo 16003088 3 193 206
PermDemo 16003088 16384 193 205
Once again, both SELECT commands fail for Joe_Smith. For FredJ, who is also a member of the test group, the SELECT succeeds, but for JaneD it fails, because she is not a member. This is an example of how Joe’s individual permissions take precedence over the group permissions.
Let’s look now at how to remove Joe’s row from sysprotects:
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
SETUSER 'FredJ'
SELECT * FROM PermDemo
SETUSER
SETUSER 'JaneD'
SELECT * FROM PermDemo
SETUSER
The following table shows the result of the preceding code.
name id uid action protecttype
PermDemo 16003088 3 193 205
PermDemo 16003088 16384 193 205
At this point, both Joe_Smith and FredJ can query the PermDemo table, but JaneD cannot. Notice that the GRANT statement did not remove Joe’s row from sysprotects. Instead, it changed the action value from a 206 to a 205, which means Joe_Smith has now been explicitly granted SELECT permission on PermDemo. Should you revoke the test group permission, Joe would still be able to query the table.
What happens if you revoke Joe’s permission right now is another interesting question:
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
SETUSER 'FredJ'
SELECT * FROM PermDemo
SETUSER
SETUSER 'JaneD'
SELECT * FROM PermDemo
SETUSER
The following table shows the result of the preceding code.
name id uid action protecttype
PermDemo 16003088 16384 193 205
The result of running these commands is that the ability to query the table stays the same, but Joe’s row is deleted from the sysprotects table. If you have a user with a single explicit REVOKE entry on an object, the way to remove it is by granting the same permission on the object and then revoking that permission. The same process will take care of a single explicit GRANT entry too. The key is to remember that the first statement reverses the action, and the second statement removes the row.
Permission Hierarchies
This next to last example rounds off the discussion by showing the hierarchy of permission assignment:
REVOKE SELECT ON PermDemo FROM test
SELECT a.name, b.*
FROM sysobjects a, sysprotects b
WHERE a.id = b.id AND a.name = 'PermDemo'
--Should be no rows in sysprotects now
GRANT SELECT ON PermDemo TO public
REVOKE SELECT ON PermDemo FROM test
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
SETUSER 'FredJ'
SELECT * FROM PermDemo
SETUSER
SETUSER 'JaneD'
SELECT * FROM PermDemo
SETUSER
The following table shows the result of the preceding code.
name id uid action protecttype
PermDemo 16003088 0 193 205
PermDemo 16003088 3 193 205
PermDemo 16003088 16384 193 206
JaneD can query the table because she is a member of the public group. FredJ cannot query the table because the Test group has an explicit REVOKE entry. On the other hand, Joe_Smith’s SELECT statement works because he has a row in sysprotects granting him SELECT permission.
These examples certainly do not cover every permutation, but they should give a sense of why the order of your commands is extremely important. In general, the real complexity comes from assigning permissions to groups. Without them, the algorithm is very simple, although a bit limited, particularly if you want to assign explicit REVOKE permissions to a user.
It is when sysprotects contains permissions for groups that you have to pay attention because SQL Server will add rows in that situation when it would not otherwise. In addition, removing group rows from sysprotects does not also remove the rows for the group’s members. As you saw, if you want to remove those rows, you have to go through a two-step process for each user. All of these considerations combined make designing an easily managed security plan difficult for SQL Server 6.5.
What you will find is that the limitation of two groups (public and one other one) will force you to assign permissions on an individual basis most of the time. You should start by assigning the permissions that all users should have to the public group. You can then use other groups to grant or revoke permissions to smaller sets of users.
NOTE Just remember that if the public group has a row in sysprotects, REVOKE statements will add a row in most situations. It is, in fact, the only way to set up explicit REVOKE permissions for both users and groups.
After you have exhausted the limited options for assigning permissions through groups, you will need to assign all the rest of the permissions on a user-by-user basis.
The main point for network administrators to take from this is that SQL Server 6.5 groups do not behave like Windows NT/2000 groups. Whereas the most logical way to manage users’ permissions in Windows is through groups, SQL Server 6.5
almost forces you to manage permissions without them. At least now when you read Chapter 4, you will have an appreciation of why SQL Server 7.0 and 2000 are such an improvement.
 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: Ownership Chains >>
More MS SQL Server Articles
More By Apress Publishing
