SQL Server 2000 Security - Part 7 - Statement and Object Permissions
July 13, 2004
SQL Server 2000 offers a number of security mechanisms that impose predefined levels of control over database objects and operations. We have discussed the most common of them, such as server and database roles, in the previous articles of this series. While they are convenient and easy to manage, their main drawback is lack of flexibility. The best recourse for such limitation is the use of much more granular and flexible object and statement permissions.
Statement permissions restrict access to statements that result in the creation of databases and their objects, as well as to database and transaction log backups (i.e. executing any of the CREATE DATABASE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW, BACKUP DATABASE, or BACKUP LOG statements via T-SQL or SQL Server Manager interface). Object permissions control operations involving various database objects - in particular:
Object permissions, unlike statement permissions, are set on a specific object, (i.e. for two database objects of the same type, a particular user might have different object permissions), while statement permissions are database-wide (or server-wide in case of CREATE DATABASE statement). Another significant difference between the two relates to who controls them. Statement permissions can be assigned either by members of sysadmin fixed server role or by members of database owner and security admin fixed database role. Object permissions can also (in addition to the same roles as statement permissions) be managed by owners of the objects themselves and can be delegated to other users, database roles, or Windows accounts using WITH GRANT OPTION (more about this shortly).
As far as similarities are concerned, the operations involving changing statement and object permissions are typically associated with making changes to the syspermissions (and sysprotects, for backward compatibility reasons) table in a target database. In addition, for both statement and object permissions, it is possible to perform one of three actions - grant, revoke, and deny:
While managing statement and object permissions can be handled both with the graphical interface of SQL Enterprise Manager and with T-SQL statements, the latter offers more functionality. In particular, it allows you to delegate object permissions to database users, roles or Windows accounts using the WITH GRANT OPTION clause. Here is a sample statement that grants SELECT permissions on the Shippers table to a user JohnDoe using WITH GRANT OPTION:
GRANT SELECT ON Shippers TO JohnDoe WITH GRANT OPTION
This allows JohnDoe to subsequently use the GRANT SELECT statement to assign permissions on the same object to another database user, role or Windows account. You should be careful when applying WITH GRANT OPTION, since it allows a target user to grant permissions to other accounts without your knowledge. Make sure you use it in a controlled manner and monitor the number of users with elevated privileges. Interestingly, if you delegate granting permissions to a database role (rather than a user), users who are its members will need to use the AS clause when granting permissions to others:
GRANT SELECT ON Shippers TO JaneDoe AS DoeRole
In this example, the assumption is that the
GRANT SELECT ON Shippers TO DoeRole WITH GRANT OPTION
has been executed previously, granting an option to delegate SELECT object permissions on the Shippers table to members of the DoeRole user-defined database role. As you can see, when a role member executes the GRANT SELECT statement, it is necessary to specify that the execution takes place in the context of the DoeRole role (hence the AS DoeRole clause).
In general, you should use Windows groups or database roles to control permissions. This simplifies management when assigning the same level of access to multiple database or Windows users. Note that you can also easily deal with exceptions, where a user who belongs to a Windows group or a database role needs to have different permissions than the rest of its members. In such cases, you can take advantage of the fact that most restrictive permissions (i.e. deny) have priority when multiple level of permissions come into play (so, for example, denying permission to a specific user that is, at the same time, granted to a role the user is a member of, will take precedence) . This also means that if you want to ensure that a particular database or Windows user does not have a specific type of permission, you can simply create a role or a Windows group, place the user in it, and set the deny permissions on the role or group level.
Since it might be difficult to determine cumulative permissions with different levels at which they are applied, you can use sp_helprotect stored procedure to determine:
As you can see, SQL Server 2000 provides plenty of options for controlling statement and object permissions on a very granular level. In our next article, we will be discussing replication related security.