GRANT Statement – Authorization in SQL Server

Only authorized users are able to execute statements or perform operations on an entity. If an unauthorized user attempts to do either task, the execution of the Transact-SQL statement or the operation on the database object will be rejected.

There are three Transact-SQL statements related to authorization:

• GRANT
• DENY
• REVOKE

Before you read about these three statements, I will repeat the most important facts concerning the security model of the Database Engine. The model separates the world into principals and securables. Every securable has associated permissions that can be granted to a principal. Principals, such as individuals, groups, or applications, can access securables. Securables are the resources to which the authorization subsystem regulates access. There are three securable classes: server, database, and schema, which contain other securables, such as login, database users, tables, and stored procedures.

GRANT Statement

The GRANT statement grants permissions to securables. The syntax of the GRANT statement is

GRANT {ALL [PRIVILEGES]} | permission_list
	[ON [class::] securable] TO principal_list [WITH GRANT OPTION]
	[AS principal ]

The ALL clause indicates that all permissions applicable to the specified securable will be granted to the specified principal. (For the list of specific securables, see Books Online.) permission_list specifies either statements or objects (separated by commas) for which the permissions are granted. class specifies either a securable class or a securable name for which permission will be granted. ON securable specifies the securable for which permissions are granted (see Example 12.15 later in this section). principal_list lists all accounts (separated by commas) to which permissions are granted. principal and the components of principal_list can be a Windows user account, a login or user account mapped to a certificate, a login mapped to an asymmetric key, a database user, a database role, or an application role.

Table 12-3 lists and describes all the permissions and lists the corresponding securables to which they apply.

Table 12-3 – Permissions with Corresponding Securables

The following examples demonstrate the use of the GRANT statement. To begin,

Example 12.11 demonstrates the use of the CREATE permission.

USE sample;
GRANT CREATE TABLE, CREATE PROCEDURE
	TO peter, mary;Code language: PHP (php)

In Example 12.11, the users peter and mary can execute the Transact-SQL statements CREATE TABLE and CREATE PROCEDURE. (As you can see from this example, the GRANT statement with the CREATE permission does not include the ON option.)

Example 12.12 allows the user mary to create user-defined functions in the sample database.

USE sample;
GRANT CREATE FUNCTION TO mary;Code language: PHP (php)

Example 12.13 shows the use of the SELECT permission within the GRANT statement.

USE sample;
GRANT SELECT ON employee
	TO peter, mary;Code language: PHP (php)

In Example 12.13, the users peter and mary can read rows from the employee table.

Example 12.14 shows the use of the UPDATE permission within the GRANT statement.

USE sample;
GRANT UPDATE ON works_on (emp_no, enter_date) TO peter;Code language: PHP (php)

After the GRANT statement in Example 12.14 is executed, the user peter can modify values of two columns of the works_on table: emp_no and enter_date.

Example 12.15 shows the use of the VIEW DEFINITION permission, which controls the ability of users to read metadata.

USE sample;
GRANT VIEW DEFINITION ON OBJECT::employee TO peter;
GRANT VIEW DEFINITION ON SCHEMA::dbo TO peter;Code language: PHP (php)

Example 12.15 shows two GRANT statements for the VIEW DEFINITION permission. The first one allows the user peter to see metadata about the employee table of the sample database. (OBJECT is one of the base securables, and you can use this clause to give permissions for specific objects, such as tables, views, and stored procedures.) Because of the hierarchical structure of securables, you can use a “higher” securable to extend the VIEW DEFINITION (or any other base) permission. The second statement in Example 12.15 gives the user peter access to metadata of all the objects of the dbo schema of the sample database.

Example 12.16 shows the use of the CONTROL permission.

USE sample;
GRANT CONTROL ON DATABASE::sample TO peter;Code language: PHP (php)

In Example 12.16, the user peter effectively has all defined permissions on the securable (in this case, the sample database). A principal that has been granted CONTROL also implicitly has the ability to grant permissions on the securable; in other words, the CONTROL permission includes the WITH GRANT OPTION clause (see Example 12.17).

The CONTROL permission is the highest permission in relation to several base securables. For this reason, CONTROL at a particular scope implicitly includes CONTROL on all the securables under that scope. Therefore, the CONTROL permission of user peter on the sample database implies all permissions on this database, all permissions on all assemblies in the database, all permissions on all schemas in the sample database, and all permissions on objects within the sample database.

By default, if user A grants a permission to user B, then user B can use the permission only to execute the Transact-SQL statement listed in the GRANT statement. The WITH GRANT OPTION gives user B the additional capability of granting the privilege to other users, as shown in Example 12.17.

USE sample;
GRANT SELECT ON works_on TO mary
	WITH GRANT OPTION;Code language: PHP (php)

In Example 12.17, the user mary can use the SELECT statement to retrieve rows from the works_on table and also may grant this privilege to other users of the sample database.