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.
