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:

• DELETE - required to be able to delete data rows from a table or a view on which permissions are set,
• INSERT- required to insert data rows into a table or a view on which permissions are set,
• SELECT - required to view data rows (or individual columns) in a table or a view on which permissions are set,
• UPDATE - required to modify data rows (or individual columns) in a table or a view on which permissions are set,
• EXECUTE - required to execute a stored procedure or a scalar-valued user-defined function on which permissions are set,
• REFERENCES - required to be able to reference a primary key in a table on which permissions are set by another table's foreign key or to be able to create a user-defined function or a view that includes WITH SCHEMABINDING clause referencing an object on which permissions are set.

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:

• grant - gives a target database user, role, or Windows account the ability to execute a particular type of statement or to access a specific database object. When working with database objects using SQL Server Enterprise Manager, the "granted permissions" status is indicated by the green checkmark in the relevant statement column (in the Object properties dialog box, under the specific permission type).

  For example, the following T-SQL statement grants CREATE TABLE statement permission to the user JohnDoe in the current database:

   GRANT CREATE TABLE TO JohnDoe 

  Similarly, the following T-SQL statement grants SELECT permissions on the ShipperID and CompanyName columns of the Shippers table to the same user:

   GRANT SELECT (ShipperID, CompanyName) ON Shippers TO JohnDoe 

• deny - prevents a target database user, role, or a Windows account from executing a particular type of statement or accessing a specific database object. Deny permissions always take precedence over grant permissions. When working with database objects using SQL Server Enterprise Manager, the "denied permissions" status is indicated by the red cross in the relevant statement column (in the Object properties dialog box, under the specific permission type).

  For example, the following T-SQL statement denies CREATE TABLE statement permission to the user JohnDoe in the current database:

   DENY CREATE TABLE TO JohnDoe 

  Similarly, the following T-SQL statement denies SELECT permissions on the ShipperID and CompanyName columns of the Shippers table to the same user:

   DENY SELECT (ShipperID, CompanyName) ON Shippers TO JohnDoe 

• revoke - removes previously assigned permission (grant or deny) from a target database user, role, or a Windows account. Revoke might effectively eliminate impact of either granting or denying permissions, depending on a number of factors (e.g. which one has been assigned most recently, or whether permissions were granted or denied to an individual user or a role that the user is a member of). Revoking is done by removing relevant entries from the syspermissions table (which are added by both grant and deny actions). When working with database objects using SQL Server Enterprise Manager, the "revoked permissions" status is indicated by an empty checkbox in the relevant statement column (in the Object properties dialog box, under the specific permission type).

  For example, the following T-SQL statement revokes CREATE TABLE statement permission from the user JohnDoe in the current database:

   REVOKE CREATE TABLE FROM JohnDoe 

  Similarly, the following T-SQL statement revokes SELECT permissions on the ShipperID and CompanyName columns of the Shippers table from the same user:

   REVOKE SELECT (ShipperID, CompanyName) ON Shippers FROM JohnDoe 

  Note that you can grant, deny or revoke object permissions specifying individual columns of a table or a view to which permission apply.

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:

• all permissions assigned to a specific user within a database with the following sample syntax (in this example, all permissions assigned to JohnDoe in the current database):

  sp_helprotect NULL, 'JohnDoe'

• all permissions assigned by a specific user within a database with the following sample syntax (in this example, all permissions assigned by dbo of the current database):

  sp_helprotect NULL, NULL, 'dbo'

• all permissions assigned to a specific database object with the following sample syntax (in this example, all object permissions assigned to Shippers table):

  sp_helprotect Shippers

• all users with a specific permission assigned to them. The value of fourth parameter indicates permission type - 's' for statement permissions and 'o' for object permissions. For example, to list all users with object permissions in the current database, you would use the following syntax:

  sp_helprotect NULL, NULL, NULL, 'o'

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.

» See All Articles by Columnist Marcin Policht