SQL Server 2000 Security – Part 7 – Statement and Object Permissions

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

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles