DB2 Universal Database Security

This chapter is from the book 

Granting Authorities and Privileges

There are three different ways that users (and groups) can obtain database-level authorities and database/object privileges. They are:

• Implicitly. When a user creates a database, they implicitly receive DBADM authority for that database, along with several database privileges. Likewise, when a user creates a database object, they implicitly receive all privileges available for that object along with the ability to grant any combination of those privileges (with the exception of the CONTROL privilege), to other users and groups. Privileges can also be implicitly given whenever a higher-level privilege is explicitly granted to a user (for example, if a user is explicitly given CONTROL privilege for a tablespace, they will implicitly receive the USE privilege for that tablespace as well). Keep in mind that such implicitly assigned privileges are not automatically revoked when the higher-level privilege that caused them to be granted is revoked.

• Indirectly. Indirectly assigned privileges are usually associated with packages; when a user executes a package that requires privileges to execute that the user does not have (for example, a package that deletes a row of data from a table requires the DELETE privilege on that table), the user is indirectly given those privileges for the express purpose of executing the package. Indirectly granted privileges are temporary and do not exist outside the scope in which they are granted.

• Explicitly. Database-level authorities, database privileges, and object privileges can be explicitly given to or taken from an individual user or a group of users by any user that has the authority to do so. To explicitly grant privileges on most database objects, a user must have SYSADM authority, DBADM authority, or CONTROL privilege on that object. Alternately, a user can explicitly grant any privilege they were assigned with the WITH GRANT OPTION specified. To grant CONTROL privilege for any object, a user must have SYSADM or DBADM authority; to grant DBADM authority, a user must have SYSADM authority.

Granting and Revoking Authorities and Privileges from the Control Center

One way to explicitly grant and revoke database-level authorities, as well as several available privileges, is by using the various authorities and privileges management dialogs that are provided with the Control Center. These dialogs are activated by highlighting the appropriate database or object name shown in the Control Center panes and selecting either Authorities or Privileges from the corresponding database or object menu. Figure 3-15 shows the menu items that must be selected in the Control Center in order to activate the Table Privileges dialog for a particular table. Figure 3-16 shows how the Table Privileges dialog might look immediately after a table is first created. (A single check mark under a privilege means that the individual or group shown has been granted that privilege; a double check mark means the individual or group has also been granted the ability to grant that privilege to other users and groups.)

Figure 3-15. Invoking the Table Privileges dialog from the Control Center.

Figure 3-16. The Table Privileges dialog.

To assign privileges to an individual user from the Table Privileges dialog (or a similar authorities/privileges dialog), you simply identify a particular user by highlighting their entry in the recognized users list—if the desired user is not in the list, they can be added by selecting the “Add User” push button—and assign the appropriate privileges (or authorities) using the “Privileges” (or “Authorities”) drop-down list or the “Grant All” or “Revoke All” push buttons. To assign privileges to a group of users, you select the “Group” tab to display a list of recognized groups and repeat the process (using the “Add Group” push button instead of the “Add User” push button to add a desired group to the list if they are not already there).

Granting Authorities and Privileges with the GRANT SQL Statement

Not all privileges can be explicitly given to users/groups with the privileges management dialogs available. However, in situations where no privileges dialog exists (and in situations where you elect not to use the Control Center), database-level authorities and database/object privileges can be explicitly given to users and/or groups by executing the appropriate form of the GRANT SQL statement. The syntax for the GRANT SQL statement varies according to the authority or privilege being granted—the following sections show the syntax used to grant each database-level authority and database/object privilege available.

Database-level authorities and privileges

GRANT [DBADM | Privilege, ...] ON DATABASE TO [Recipient, ...]
   

where:

Privilege	Identifies one or more database privileges that are to be given to one or more users and/or groups.
Recipient	Identifies the name of the user(s) and/or group(s) that are to receive DBADM authority or the database privileges specified

Schema privileges

GRANT [Privilege, ...] ON SCHEMA [SchemaName] TO        [Recipient, ...] <WITH GRANT OPTION>
   

where:

Privilege	Identifies one or more schema privileges that are to be given to one or more users and/or groups.
SchemaName	Identifies by name the specific schema that all schema privileges specified are to be associated with.
Recipient	Identifies the name of the user(s) and/or group(s) that are to receive the schema privileges specified.

Tablespace privilege

GRANT USE OF TABLESPACE [TablespaceName]
   TO [Recipient, ...]
   <WITH GRANT OPTION>
   

where:

TablespaceName	Identifies by name the specific tablespace that the USE privilege is to be associated with.
Recipient	Identifies the name of the user(s) and/or group(s) that are to receive the USE privilege.

Table privileges

GRANT [ALL <PRIVILEGES> |
   Privilege <( ColumnName, ... )>  , ...]
   ON TABLE [TableName] TO [Recipient, ...]
   <WITH GRANT OPTION>
   

where:

Privilege	Identifies one or more table privileges that are to be given to one or more users and/or groups.
ColumnName	Identifies by name one or more specific columns that UPDATE or REFERENCES privileges are to be associated with. This option is not used if Privilege is not equal to UPDATE or REFERENCES.
TableName	Identifies by name the specific table that all table privileges specified are to be associated with.
Recipient	Identifies the name of the user(s) and/or group(s) that are to receive the table privileges specified.

Index privilege

GRANT CONTROL ON INDEX [IndexName] TO [Recipient, ...]
   

where:

IndexName	Identifies by name the specific index that the CONTROL privilege is to be associated with.
Recipient	Identifies the name of the user(s) and/or group(s) that are to receive the CONTROL privilege.

View privileges

GRANT [ALL <PRIVILEGES> |
   Privilege <( ColumnName, ... )>  , ...]
   ON [ViewName]
   TO [Recipient, ...]
   <WITH GRANT OPTION>
   

where:

Privilege	Identifies one or more view privileges that are to be given to one or more users and/or groups.
ColumnName	Identifies by name one or more specific columns that UPDATE privilege is to be associated with. This option is not used if Privilege is not equal to UPDATE.
ViewName	Identifies by name the specific view that all view privileges specified are to be associated with.
Recipient	Identifies the name of the user(s) and/or group(s) that are to receive the view privileges specified.

Package privileges

GRANT [Privilege, ...] ON PACKAGE <SchemaName.> [PackageID] TO [Recipient, ...] <WITH
    GRANT OPTION>
   

where:

Privilege	Identifies one or more package privileges that are to be given to one or more users and/or groups.
SchemaName	Identifies by name the schema in which the specified package is found.
PackageName	Identifies by name the specific package that all package privileges specified are to be associated with.
Recipient	Identifies the name of the user(s) and/or group(s) that are to receive the package privileges specified.

Routine privileges

GRANT EXECUTE ON [RoutineName |
   FUNCTION <SchemaName.> * |
   METHOD * FOR [TypeName] |
   METHOD * FOR <SchemaName.> * |
   PROCEDURE <SchemaName.> *]
   TO [Recipient, ...]
   <WITH GRANT OPTION>
   

where:

RoutineName	Identifies by name the routine that the EXECUTE privilege is to be associated with.
TypeName	Identifies by name the type in which the specified method is found.
SchemaName	Identifies by name the schema in which all functions, methods, or procedures—including those that may be created in the future—are to have the EXECUTE privilege granted on.
Recipient	Identifies the name of the user(s) and/or group(s) that are to receive the EXECUTE privilege.

Sequence privilege

GRANT USAGE ON SEQUENCE [SequenceName] TO PUBLIC
   

where:

SequenceName	Identifies by name the specific sequence that the USAGE privilege is to be associated with.

Server privilege

GRANT PASSTHRU ON SERVER [ServerName] TO [Recipient, ...]
   

where:

ServerName	Identifies by name the specific server that the PASSTHRU privilege is to be associated with.
Recipient	Identifies the name of the user(s) and/or group(s) that are to receive the PASSTHRU privilege.

Nickname privileges

GRANT [ALL <PRIVILEGES> | Privilege <( ColumnName, ... )>  , ...] ON [Nickname] TO
    [Recipient, ...] <WITH GRANT OPTION>
   

where:

Privilege	Identifies one or more nickname privileges that are to be given to one or more users and/or groups.
ColumnName	Identifies by name one or more specific columns that the REFERENCES privilege is to be associated with. This option is not used if Privilege is not equal to REFERENCES.
Nickname	Identifies by name the specific nickname that all privileges specified are to be associated with.
Recipient	Identifies the name of the user(s) and/or group(s) that are to receive the nickname privileges specified.

If the WITH GRANT OPTION clause is specified with the GRANT statement, the user and/or group receiving the privileges specified is given the ability to grant the privilege received (except for the CONTROL privilege) to other users. In all cases, the value specified for the Recipient parameter can be any combination of the following:

USER [UserName]	Identifies a specific user that the privileges specified are to be given to.
GROUP [GroupName]	Identifies a specific group that the privileges specified are to be given to.
PUBLIC	Indicates that the specified privilege(s) are to be given to the special group PUBLIC. (All users are a member of the group PUBLIC).

GRANT SQL statement examples

Now that we've seen the basic syntax for the various forms of the GRANT SQL statement, let's take a look at some examples.

Example 1. A server has both a user and a group named TESTER. Give the group TESTER the ability to bind applications to the database SAMPLE:

CONNECT TO SAMPLE
GRANT BINDADD ON DATABASE TO GROUP tester

Example 2. Give all table privileges available (except CONTROL privilege) for the table PAYROLL.EMPLOYEE to the group PUBLIC:

GRANT ALL PRIVILEGES ON TABLE payroll.employee TO PUBLIC

Example 3. Give user USER1 and user USER2 the privileges needed to perform just DML operations on the table DEPARTMENT using the view DEPTVIEW:

GRANT SELECT, INSERT, UPDATE, DELETE ON deptview TO USER user1, USER user2

Example 4. Give user JOHN_DOE the privilege needed to query the table INVENTORY, along with the ability to grant this privilege to other users whenever appropriate:

GRANT SELECT ON TABLE inventory TO john_doe WITH GRANT OPTION

Example 5. Give user USER1 the ability to run an embedded SQL application that requires package GET_INVENTORY:

GRANT EXECUTE ON PACKAGE get_inventory TO USER user1

Example 6. Give user USER1 the ability to use a user-defined function named PAYROLL.CALC_SALARY that has an input parameter of type CHAR(5) in a query:

GRANT EXECUTE ON FUNCTION payroll.calc_salary(CHAR(5)) TO USER user1

Example 7. Give user USER1 the ability to define a referential constraint between the tables EMPLOYEE and DEPARTMENT using column EMPID in table EMPLOYEE as the parent key:

GRANT REFERENCES(empid) ON TABLE employee TO USER user1

Example 8. Give the group PUBLIC the ability to modify information stored in the ADDRESS and HOME_PHONE columns of the table EMP_INFO:

GRANT UPDATE(address, home_phone) ON TABLE emp_info TO PUBLIC

Revoking Authorities and Privileges with the REVOKE SQL Statement

Just as there is an SQL statement that can be used to grant database-level authorities and database/object privileges, there is an SQL statement that can be used to revoke database-level authorities and database/object privileges. This statement is the REVOKE SQL statement, and like the GRANT statement, the syntax for the REVOKE statement varies according to the authority or privilege being revoked—the following sections show the syntax used to revoke each database-level authority and database/object privilege available.

Database-level authorities and privileges

REVOKE [DBADM | Privilege, ...] ON DATABASE FROM [Forfeiter, ...] <BY ALL>
   

where:

Privilege	Identifies one or more database privileges that are to be taken from one or more users and/or groups.
Forfeiter	Identifies the name of the user(s) and/or group(s) that are to lose DBADM authority or the database privileges specified.

Schema privileges

REVOKE [Privilege, ...] ON SCHEMA [SchemaName] FROM [Forfeiter, ...] <BY ALL>
   

where:

Privilege	Identifies one or more schema privileges that are to be taken from one or more users and/or groups.
SchemaName	Identifies by name the specific schema that all schema privileges specified are to be associated with.
Forfeiter	Identifies the name of the user(s) and/or group(s) that are to lose the schema privileges specified.

Tablespace privilege

REVOKE USE OF TABLESPACE [TablespaceName] FROM [Forfeiter, ...] <BY ALL>
   

where:

TablespaceName	Identifies by name the specific tablespace that the USE privilege is to be associated with.
Forfeiter	Identifies the name of the user(s) and/or group(s) that are to lose the USE privilege.

Table privileges

REVOKE [ALL <PRIVILEGES> | Privilege, ...] ON TABLE [TableName] FROM [Forfeiter, ...] <BY ALL>
   

where:

Privilege	Identifies one or more table privileges that are to be taken from one or more users and/or groups.
TableName	Identifies by name the specific table that all table privileges specified are to be associated with.
Forfeiter	Identifies the name of the user(s) and/or group(s) that are to lose the table privileges specified.

Index privilege

REVOKE CONTROL ON INDEX [IndexName] FROM [Forfeiter, ...] <BY ALL>
   

where:

IndexName	Identifies by name the specific index that the CONTROL privilege is to be associated with.
Forfeiter	Identifies the name of the user(s) and/or group(s) that are to lose the CONTROL privilege.

View privileges

REVOKE [ALL <PRIVILEGES> | Privilege, ...] ON [ViewName] FROM [Forfeiter, ...] <BY ALL>
   

where:

Privilege	Identifies one or more view privileges that are to be taken from one or more users and/or groups.
ViewName	Identifies by name the specific view that all view privileges specified are to be associated with.
Forfeiter	Identifies the name of the user(s) and/or group(s) that are to lose the view privileges specified.

Package privileges

REVOKE [Privilege, ...] ON PACKAGE <SchemaName.> [PackageID] FROM [Forfeiter, ...] <BY ALL>
   

where:

Privilege	Identifies one or more package privileges that are to be taken from one or more users and/or groups.
SchemaName	Identifies by name the schema in which the specified package is found.
PackageName	Identifies by name the specific package that all package privileges specified are to be associated with.
Forfeiter	Identifies the name of the user(s) and/or group(s) that are to lose the package privileges specified.

Routine privileges

REVOKE EXECUTE ON [RoutineName |
   FUNCTION <SchemaName.> * |
   METHOD * FOR [TypeName] |
   METHOD * FOR <SchemaName.> * |
   PROCEDURE <SchemaName.> *]
   FROM [Forfeiter, ...] <BY ALL> RESTRICT
   

where:

RoutineName	Identifies by name the routine that the EXECUTE privilege is to be associated with.
TypeName	Identifies by name the type in which the specified method is found.
SchemaName	Identifies by name the schema in which all functions, methods, or procedures—including those that may be created in the future—are to have the EXECUTE privilege revoked on.
Forfeiter	Identifies the name of the user(s) and/or group(s) that are to lose the package privileges specified.

Sequence privilege

REVOKE USAGE ON SEQUENCE [SequenceName] FROM PUBLIC
   

where:

SequenceName	Identifies by name the specific sequence that the USAGE privilege is to be associated with.

Server privilege

REVOKE PASSTHRU ON SERVER [ServerName] FROM [Forfeiter, ...] <BY ALL>
   

where:

ServerName	Identifies by name the specific server that the PASSTHRU privilege is to be associated with.
Forfeiter	Identifies the name of the user(s) and/or group(s) that are to lose the PASSTHRU privilege.

Nickname privileges

REVOKE [ALL <PRIVILEGES> | Privilege, ...] ON [Nickname] FROM [Forfeiter, ...] <BY ALL>
   

where:

Privilege	Identifies one or more nickname privileges that are to be given to one or more users and/or groups.
Nickname	Identifies by name the specific nickname that all privileges specified are to be associated with.
Forfeiter	Identifies the name of the user(s) and/or group(s) that are to lose the nickname privileges specified.

The BY ALL syntax is optional and is provided as a courtesy for administrators who are familiar with the syntax of the DB2 for OS/390 REVOKE SQL statement. Whether it is included or not, the results will always be the same—the privilege(s) specified will be revoked from all users and/or groups specified, regardless of who granted it originally.

In all cases, the value specified for the Forfeiter parameter can be any combination of the following:

USER [UserName]	Identifies a specific user that the privileges specified are to be taken from.
GROUP [GroupName]	Identifies a specific group that the privileges specified are to be taken from.
PUBLIC	Indicates that the specified privilege(s) are to be taken from the special group PUBLIC. (All users are a member of the group PUBLIC.)

REVOKE SQL statement examples

Now that we've seen the basic syntax for the various forms of the REVOKE SQL statement, let's take a look at some examples.

Example 1. A server has both a user and a group named Q045. Remove the ability to connect to the database named SAMPLE from the group Q045:

CONNECT TO SAMPLE
REVOKE CONNECT ON DATABASE FROM GROUP q045

Example 2. Revoke all table privileges available (except CONTROL privilege) for the table DEPARTMENT from the user USER1 and the group PUBLIC:

REVOKE ALL PRIVILEGES ON TABLE department FROM user1, PUBLIC

Example 3. Take away user USER1's ability to use a user-defined function named CALC_BONUS:

REVOKE EXECUTE ON FUNCTION calc_bonus FROM USER user1

Example 4. Take away user USER1's ability to modify information stored in the ADDRESS and HOME_PHONE columns of the table EMP_INFO, regardless of who granted it:

REVOKE UPDATE(address, home_phone) ON TABLE emp_info FROM user1 BY ALL