INF: How Grant and Revoke work in SQL Server 6.5.

ID: Q154422

The information in this article applies to:

Microsoft SQL Server version 6.5

SUMMARY

In SQL Server version 6.5, the implementation of permissions has changed compared to previous versions. This article describes the SQL Server 6.5 behavior.

MORE INFORMATION

The Sysprotects table maintains information about user and group permissions. A change in SQL Server 6.5 from the previous version is how it writes to the Sysprotects table, which in turn affects what permission is assigned to a user or to a group. SQL Server determines what to write to the Sysprotects table based on the current content of the table.

SQL Server evaluates whether a row exists in the Sysprotects table for the specific permission, action, object, and user or group. If a row exists in Sysprotects relating to the specific permission, SQL Server performs one of the following steps:

If a row exists, and the current GRANT or REVOKE statement duplicates the existing row, nothing is entered in Sysprotects.

If a row exists with GRANT permission and the current REVOKE statement is the converse of the previously entered row, the previously entered row is deleted from Sysprotects.

If a row exists with REVOKE permission, and the current GRANT statement is the converse of the previously entered row, the previously entered row is deleted and a new row with GRANT permission is entered in Sysprotects.

If no row exists in Sysprotects relating to the specific permission, SQL Server performs one of the following steps (depending on who the permission is being granted to or revoked from, and what other permissions exist in the Sysprotects table):

If a GRANT permission is issued, a new row with GRANT permission is entered in Sysprotects.

A user is subordinate to a group, and a group is subordinate to a public. If a REVOKE permission is issued for a user or group and the user's group or public group has already been granted permission (has an entry with GRANT permission in Sysprotects), a new row with REVOKE permission is entered in Sysprotects.

If a REVOKE permission is issued for a user or group and there is no entry with GRANT permission in Sysprotects for user's group or public group, no new row is entered in Sysprotects.

Based on the above steps, permissions (in SQL Server 6.5) operate in a strict hierarchy with any explicit GRANT or REVOKE permission at the user level, overriding any explicit GRANT or REVOKE permission at the group level, which in turn overrides any explicit GRANT or REVOKE permission at the PUBLIC group level. Permissions granted to (or revoked from) the user level or the group level are evaluated independently of any permissions granted to (or revoked from) the group the user belongs to or the all- encompassing PUBLIC group.

The following example displays the behavior mentioned above. In the example, user1 is a user and group1 is a group which contains user1.

Step 1: Grant permission to a user. This will enter a row with GRANT in Sysprotects.


GRANT select ON authors_test TO user1
go
sp_helprotect authors_test
go

Result:


Owner Object       Grantee Grantor ProtectType Action Column
----- ------------ ------- ------- ----------- ------ ---------
dbo   authors_test  user1   dbo     Grant      Select (All+New)

Step 2: Issue another GRANT statement to group1. This enters another row in Sysprotects.


GRANT select ON authors_test TO group1
go
sp_helprotect authors_test
go

Result:


Owner Object       Grantee Grantor ProtectType Action Column
----- ------------ ------- ------- ----------- ------ ---------
dbo   authors_test  user1   dbo     Grant      Select (All+New)
dbo   authors_test  group1  dbo     Grant      Select (All+New)

Step 3: REVOKE permission from group1, which cancels the previously assigned GRANT to group1 and only the one row with GRANT to user1 is left in Sysprotects.


REVOKE select ON authors_test FROM group1
go
sp_helprotect authors_test
go

Result:


Owner Object       Grantee Grantor ProtectType Action Column
----- ------------ ------- ------- ----------- ------ ---------
dbo   authors_test  user1   dbo     Grant      Select (All+New)

Step 4: Issue a REVOKE from either group1 or public. This does not place any entry in Sysprotects.


REVOKE select ON authors_test FROM group1
go
sp_helprotect authors_test
go

Result:


Owner Object       Grantee Grantor ProtectType Action Column
----- ------------ ------- ------- ----------- ------ ---------
dbo   authors_test  user1   dbo     Grant      Select (All+New)

Step 5: Issue a GRANT to public and then a REVOKE from group1. This enters two more entries in Sysprotects.


GRANT select ON authors_test TO public
go
REVOKE select ON authors_test FROM group1
go
sp_helprotect authors_test
go

Result:


Owner Object       Grantee Grantor ProtectType Action Column
----- ------------ ------- ------- ----------- ------ ---------
dbo   authors_test  user1   dbo     Grant      Select (All+New)
dbo   authors_test  group1  dbo     Revoke     Select (All+New)
dbo   authors_test  public  dbo     Grant      Select (All+New)

Step 6: Issue a GRANT to group1. This removes the existing REVOKE permission from group1 and adds a new entry for GRANT permission to group1.


GRANT select ON authors_test TO group1
go
sp_helprotect authors_test
go

Result:


Owner Object       Grantee Grantor ProtectType Action Column
----- ------------ ------- ------- ----------- ------ ---------
dbo   authors_test  user1   dbo     Grant      Select (All+New)
dbo   authors_test  group1  dbo     Grant      Select (All+New)
dbo   authors_test  public  dbo     Grant      Select (All+New)

Additional query words: sql

Keywords : kbnetwork SSrvGen SSrvSQL_Admin
Version : 6.5
Platform : WINDOWS
Issue type :