3 4
Permissions are used to control access to database objects and to specify which users can perform certain database actions. You can set both server and database permissions. Server permissions are used to allow DBAs to perform database administration tasks. Database permissions are used to allow or disallow access to database objects and statements. In this section, we'll look at the types of permissions and how to allocate them.
As just mentioned, server permissions are assigned to DBAs and allow them to perform administrative tasks. These permissions are defined on the fixed server roles. User logins can be assigned the fixed server roles, but these roles cannot be modified. (Server roles are explained in the section "Using Fixed Server Roles" later in this chapter.) Server permissions include SHUTDOWN, CREATE DATABASE, BACKUP DATABASE, and CHECKPOINT permissions. Server permissions are used only for authorizing DBAs to perform administrative tasks and do not need to be modified or granted to individual users.
Database object permissions are a class of permissions that are granted to allow access to database objects. Object permissions are necessary to access a table or view by using SQL statements such as SELECT, INSERT, UPDATE, and DELETE. An object permission is also needed to use the EXECUTE statement to run a stored procedure. You can use Enterprise Manager or T-SQL commands to assign object permissions.
To use Enterprise Manager to grant database object permissions to a user, follow these steps:
Figure 34-14. The Database User Properties window.
Figure 34-15. The Permissions tab of the Database User Properties window.
To use T-SQL to assign object permissions to a user, you run the GRANT statement. The GRANT statement has the following syntax:
GRANT { ALL | permission } [ column ON {table | view} ] | [ ON table(column) ] | [ ON view(column) ] | [ ON { stored_procedure | extended_procedure } ] TO security_account [ WITH GRANT OPTION ] [ AS { group | role } ]
The security_account parameter must be one of the following account types:
Using the GRANT OPTION keyword allows the user or users specified in the statement to grant the specified permission to other users. This can be useful when you grant permissions to other DBAs. However, the GRANT option should be used with care.
The AS option specifies whose authority the GRANT statement is run under. To run the GRANT statement, a user or role must have been specifically granted authority to do so.
Here is an example of using the GRANT statement:
GRANT SELECT, INSERT, UPDATE ON Customers TO MaryW WITH GRANT OPTION AS Accounting
The AS Accounting option is used because the Accounting role has permissions to grant permissions on the Customers table. The GRANT OPTION keyword allows MaryW to grant these permissions to other users.
MORE INFO
To view a list of permissions that can be specified in the GRANT statement, look up "GRANT, described (GRANT)" in the Books Online index.
You can use the T-SQL REVOKE command to revoke a user's object permissions. The REVOKE statement has the following syntax:
REVOKE [ GRANT OPTION FOR ] { ALL [ PRIVILEGES ] | permission } [ column ON {table | view} ] | [ ON table(column) ] | [ ON view(column) ] | [ ON { stored_procedure | extended_procedure } ] { TO | FROM } security_account [ CASCADE ] [ AS { group | role } ]
The security_account parameter must be one of the following account types:
The GRANT OPTION FOR option allows you to revoke permissions you previously granted by using the GRANT OPTION keyword, as well as revoke the permission. The AS option specifies whose authority the REVOKE statement is run under.
Here is an example of using the REVOKE statement:
REVOKE ALL ON Customers FROM MaryW
The REVOKE ALL statement will remove all permissions the user MaryW has on the Customers table.
MORE INFO
To view a list of permissions that can be specified in the REVOKE statement, look up "REVOKE, described (REVOKE)" in the Books Online index.
In addition to assigning database object permissions, you can assign statement permissions. Object permissions enable users to access existing objects within the database, whereas statement permissions authorize them to create database objects, including databases and tables. The statement permissions are listed here:
You can assign statement permissions by using either Enterprise Manager or T-SQL.
To use Enterprise Manager to grant database statement permissions to a user, follow these steps:
Figure 34-16. A database's Properties window.
Figure 34-17. The Permissions tab of a database's Properties window.
To assign statement permissions to a user by using T-SQL, you use the GRANT statement. The GRANT statement has the following syntax:
GRANT { ALL | statement } TO security_account
The statement permissions that can be assigned to a user are CREATE DATABASE, CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW, DROP TABLE, DROP VIEW, BACKUP DATABASE, and BACKUP LOG, as described earlier. For example, to add the CREATE DATABASE and CREATE TABLE statement permissions to the user account JackR, use the following command:
GRANT CREATE DATABASE, CREATE TABLE TO 'JackR'
As you can see, adding statement permissions to a user account is not a complex process.
You can use the T-SQL statement REVOKE to remove statement permissions from a user account. The REVOKE statement has the following syntax:
REVOKE { ALL | statement } FROM security_account
For example, to remove just the CREATE DATABASE statement permissions from the user account JackR, use the following command:
REVOKE CREATE DATABASE FROM 'JackR'
As you can see, removing statement permissions from a user account is also not a complex process.