Granting Authorities and Privileges
There are three different ways that users (and in some cases, groups) can obtain database-level authorities and database/object privileges. They are:
-
Implicitly: When a user creates a database, that user implicitly receives Database Administrator authority for that database, along with most database privileges available. Likewise, when a user creates a database object, that user implicitly receives 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 table space, the user will implicitly receive the USE privilege for that table space as well). It's important to remember 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 additional privileges 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 anyone who has the authority to do so. To grant privileges explicitly on most database objects, a user must have System Administrator authority, Database Administrator authority, or CONTROL privilege on that object. Alternately, a user can explicitly grant any privilege that user was assigned with the WITH GRANT OPTION specified. To grant CONTROL privilege for any object, a user must have System Administrator authority or Database Administrator authority; to grant Database Administrator authority or Security Administrator authority, a user must have System Administrator authority.
Granting and Revoking Authorities and Privileges from the Control Center
One way to explicitly grant and revoke database-level authorities, as well as many of the object privileges available, 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-13 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-14 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 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, the user 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(s) 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 the group is 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. In situations where no privileges dialog exists (and in situations where you elect not to use the privileges dialogs available), 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 subsections show the syntax used to grant each database-level authority and database/object privilege available.
Database-level Authorities and Privileges
GRANT [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. The following values are valid for this parameter: DBADM, SECADM, CONNECT, CONNECT_QUIESCE, IMPLICIT_SCHEMA, CREATETAB, BINDADD, CREATE_EXTERNAL_ROUTINE, CREATE_NOT_FENCED_ROUTINE, and LOAD. | ||
| Recipient | Identifies the name of the user(s) and/or group(s) that are to receive the database privileges specified. 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 group PUBLIC. (All users are a member of the group PUBLIC.) | ||
Table Space Privileges
GRANT USE OF TABLESPACE [TablespaceName] TO [Recipient, ...] <WITH GRANT OPTION>
where:
| TablespaceName | Identifies by name the table space 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. Again, the value specified for the Recipient parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC. |
If the WITH GRANT OPTION clause is specified, each Recipient is given the ability to grant the privilege received to others.
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. The following values are valid for this parameter: CREATIN, ALTERIN, and DROPIN. |
| SchemaName | Identifies by name the 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. The value specified for the Recipient parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC. |
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. The following values are valid for this parameter: CONTROL, ALTER, SELECT, INSERT, UPDATE, DELETE, INDEX, and REFERENCES. (CONTROL privilege is not recognized by DB2 for iSeries and DB2 for zSeries.) |
| ColumnName | Identifies by name one or more specific columns that UPDATE or REFERENCES privileges are to be associated with. This option is only used when Privilege contains the value UPDATE or REFERENCES. |
| TableName | Identifies by name the 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. The value specified for the Recipient parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC. |
It is important to note that only users who hold System Administrator or Database Administrator authority are allowed to grant CONTROL privilege for a table. For this reason, when the ALL PRIVILEGES clause is specified, all table privileges except CONTROL privilege are granted to each Recipient; CONTROL privilege must be granted separately.
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. The following values are valid for this parameter: CONTROL, SELECT, INSERT, UPDATE, and DELETE. (CONTROL privilege is not recognized by DB2 for iSeries and DB2 for zSeries.) |
| ColumnName | Identifies by name one or more specific columns that UPDATE privileges are to be associated with. This option is only used when Privilege contains the value UPDATE. |
| ViewName | Identifies by name the 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. The value specified for the Recipient parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC. |
Again, only users who hold System Administrator or Database Administrator authority are allowed to grant CONTROL privilege for a view. Therefore, when the ALL PRIVILEGES clause is specified, all view privileges except CONTROL privilege are granted to each Recipient; CONTROL privilege must be granted separately.
Index Privileges
GRANT CONTROL ON INDEX [IndexName] TO [Recipient, ...]
where:
| IndexName | Identifies by name the index that the CONTROL privilege is to be associated with. (CONTROL privilege is not recognized by DB2 for iSeries and DB2 for zSeries.) |
| Recipient | Identifies the name of the user(s) and/or group(s) that are to receive the CONTROL privilege. The value specified for the Recipient parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC. |
Sequence Privileges
GRANT [Privilege, ...] ON SEQUENCE [SequenceName] TO [Recipient, ...] <WITH GRANT OPTION>
where:
| Privilege | Identifies one or more sequence privileges that are to be given to one or more users and/or groups. The following values are valid for this parameter: USAGE and ALTER. |
| SequenceName | Identifies by name the sequence that all sequence privileges specified are to be associated with. |
| Recipient | Identifies the name of the user(s) and/or group(s) that are to receive the sequence privileges specified. Again, the value specified for the Recipient parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC. |
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 (user-defined function, method, or stored procedure) 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. The value specified for the Recipient parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC. |
On DB2 for iSeries and DB2 for zSeries, the command for granting routine privileges is:
GRANT EXECUTE ON [FunctionName]([ParameterType],...)| SPECIFIC FUNCTION [SpecificName] | PROCEDURE [ProcedureName] TO [Recipient, ...] <WITH GRANT OPTION>
where:
| FunctionName | Identifies by name the user-defined function that the EXECUTE privilege is to be associated with. |
| ParameterType | Identifies one or more parameter data types used by the function. |
| SpecificName | Identifies by specific name the user-defined function that the EXECUTE privilege is to be associated with. |
| Recipient | Identifies the name of the user(s) and/or group(s) that are to receive the EXECUTE privilege. The value specified for the Recipient parameter can be any combination of the following: [UserName] and PUBLIC. |
Package Privileges
GRANT [Privilege, ...] ON PACKAGE <SchemaName.>[PackageID] TO [Recipient, ...] <WITH GRANT OPTION>
where:
| Privilege | Identifies one or more pckage privileges that are to be given to one or more users and/or groups. The following values are valid for this parameter: CONTROL, BIND, and EXECUTE. (CONTROL privilege is not recognized by DB2 for iSeries and DB2 for zSeries.) |
| SchemaName | Identifies by name the schema in which the specified package is found. |
| PackageName | Identifies by name the 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. The value specified for the Recipient parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC. (DB2 for iSeries and DB2 for zSeries allow users to grant package privileges to themselves; DB2 for Linux, UNIX, and Windows does not.) |
Server Privileges
GRANT PASSTHRU ON SERVER [ServerName] TO [Recipient, ...]
where:
| ServerName | Identifies by name the 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. The value specified for the Recipient parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC. |
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. The following values are valid for this parameter: CONTROL, ALTER, SELECT, INSERT, UPDATE, DELETE, INDEX, and REFERENCES. (CONTROL privilege is not recognized by DB2 for iSeries and DB2 for zSeries.) |
| ColumnName | Identifies by name one or more specific columns that UPDATE or REFERENCES privileges are to be associated with. This option is only used when Privilege contains the value UPDATE or REFERENCES. |
| Nickname | Identifies by name the 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. The value specified for the Recipient parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC. |
Only users who hold System Administrator or Database Administrator authority are allowed to grant CONTROL privilege for a nickname. Therefore, when the ALL PRIVILEGES clause is specified, all nickname privileges except CONTROL privilege are granted to each Recipient; CONTROL privilege must be granted separately.
Distinct Type Privileges (DB2 for iSeries and DB2 for zSeries Only)
GRANT USAGE ON DISTINCT TYPE [TypeName] TO [ Recipient, ...] <WITH GRANT OPTION>
where:
| TypeName | Identifies by name the distinct type that the USAGE privilege is to be associated with. |
| Recipient | Identifies the name of the user(s) and/or group(s) that are to receive the distinct type privileges specified. The value specified for the Recipient parameter can be any combination of the following: [UserName] and PUBLIC. |
It is important to note that this statement is not supported by DB2 for Linux, UNIX, and Windows. Instead, with DB2 for Linux, UNIX, and Windows the group PUBLIC is implicitly granted USAGE privilege on all distinct types.
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 for the table PAYROLL.EMPLOYEE (except CONTROL privilege) 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 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 privileges needed to query the table INVENTORY, along with the ability to give these privileges 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 a package named GET_INVENTORY:
_________________________________________________________ GRANT EXECUTE ON PACKAGE get_inventory TO USER user1 |
| Example 6: | Give user USER1 the ability to use a stored procedure named PAYROLL.CALC_SALARY in a query:
__________________________________________________________ GRANT EXECUTE ON PROCEDURE payroll.calc_salary TO user1 |
| Example 7: | Give user USER1 and group GROUP1 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, GROUP group1 |
| 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, as with 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 [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. The following values are valid for this parameter: DBADM, SECADM, CONNECT, CONNECT_QUIESCE, IMPLICIT_SCHEMA, CREATETAB, BINDADD, CREATE_EXTERNAL_ROUTINE, CREATE_NOT_FENCED_ROUTINE, and LOAD. | |
| Forfeiter | Identifies the name of the user(s) and/or group(s) that are to lose the database privileges specified. 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 group PUBLIC. (All users are members of the group PUBLIC.) | |
The BY ALL clause 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, all privileges specified will be revoked from all users and/or groups specified.
Table Space Privileges
REVOKE USE OF TABLESPACE [TablespaceName] FROM [Forfeiter, ...] <BY ALL>
where:
| TablespaceName | Identifies by name the table space 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. Again, the value specified for the Forfeiter parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC. |
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. The following values are valid for this parameter: CREATIN, ALTERIN, and DROPIN. |
| SchemaName | Identifies by name the 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. The value specified for the Forfeiter parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC. |
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. The following values are valid for this parameter: CONTROL, ALTER, SELECT, INSERT, UPDATE, DELETE, INDEX, and REFERENCES. (CONTROL privilege is not recognized by DB2 for iSeries and DB2 for zSeries.) |
| TableName | Identifies by name the 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. The value specified for the Forfeiter parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC. |
It is important to note that only users who hold System Administrator or Database Administrator authority are allowed to revoke CONTROL privilege for a table. For this reason, when the ALL PRIVILEGES clause is specified, all table privileges except CONTROL privilege are revoked from each Forfeiter; CONTROL privilege must be revoked separately.
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. The following values are valid for this parameter: CONTROL, SELECT, INSERT, UPDATE, and DELETE. (CONTROL privilege is not recognized by DB2 for iSeries and DB2 for zSeries.) |
| ViewName | Identifies by name the 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. The value specified for the Forfeiter parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC. |
Again, only users who hold System Administrator or Database Administrator authority are allowed to revoke CONTROL privilege for a table. For this reason, when the ALL PRIVILEGES clause is specified, all table privileges except CONTROL privilege are revoked from each Forfeiter; CONTROL privilege must be revoked separately.
Index Privileges
REVOKE CONTROL ON INDEX [IndexName] FROM [Forfeiter, ...] <BY ALL>
where:
| IndexName | Identifies by name the index that the CONTROL privilege is to be associated with. (CONTROL privilege is not recognized by DB2 for iSeries and DB2 for zSeries.) |
| Forfeiter | Identifies the name of the user(s) and/or group(s) that are to lose the CONTROL privilege. The value specified for the Forfeiter parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC. |
Sequence Privileges
REVOKE [Privilege, ...] ON SEQUENCE [SequenceName] FROM [Forfeiter, ...] <BY ALL>
where:
| Privilege | Identifies one or more sequence privileges that are to be taken from one or more users and/or groups. The following values are valid for this parameter: USAGE and ALTER. |
| SequenceName | Identifies by name the sequence that all sequence privileges specified are to be associated with. |
| Forfeiter | Identifies the name of the user(s) and/or group(s) that are to lose the sequence privileges specified. The value specified for the Forfeiter parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC. |
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 (user-defined function, method, or stored procedure) 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 from the Forfeiter. |
| Forfeiter | Identifies the name of the user(s) and/or group(s) that are to lose the routine privileges specified. The value specified for the Forfeiter parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC. |
The RESTRICT clause guarantees EXECUTE privilege will not be revoked if the routine specified is used in a view, trigger, constraint, index, SQL function, SQL method, transform group, or is referenced as the source of a sourced function. Additionally, EXECUTE privilege will not be revoked if the loss of the privilege would prohibit the routine definer from executing the routine (i.e., if the user who created the routine is identified as a Forfeiter).
On DB2 for iSeries and DB2 for zSeries, the command for revoking routine privileges is:
REVOKE EXECUTE ON [FunctionName]([ParameterType], ...)| SPECIFIC FUNCTION [SpecificName] | PROCEDURE [ProcedureName] TO [Forfeiter, ...]
where:
| FunctionName | Identifies by name the user-defined function that the EXECUTE privilege is to be associated with. |
| ParameterType | Identifies one or more parameter data types used by the function. |
| SpecificName | Identifies by specific name the user-defined function that the EXECUTE privilege is to be associated with. |
| Forfeiter | Identifies the name of the user(s) and/or group(s) that are to loose the EXECUTE privilege. The value specified for the Forfeiter parameter can be any combination of the following: [UserName] and PUBLIC. |
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. The following values are valid for this parameter: CONTROL, BIND, and EXECUTE. (CONTROL privilege is not recognized by DB2 for iSeries and DB2 for zSeries.) |
| 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. The value specified for the Forfeiter parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC. |
Server Privileges
REVOKE PASSTHRU ON SERVER [ServerName] FROM [Forfeiter, ...] <BY ALL>
where:
| ServerName | Identifies by name the 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. The value specified for the Forfeiter parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC. |
Nickname Privileges
REVOKE [ALL <PRIVILEGES> | Privilege, ...] ON [Nickname] FROM [Forfeiter, ...] <BY ALL>
where:
| Privilege | Identifies one or more nickname privileges that are to be taken from one or more users and/or groups. The following values are valid for this parameter: CONTROL, ALTER, SELECT, INSERT, UPDATE, DELETE, INDEX, and REFERENCES. (CONTROL privilege is not recognized by DB2 for iSeries and DB2 for zSeries.) |
| Nickname | Identifies by name the 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 value specified for the Forfeiter parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC. |
Only users who hold System Administrator or Database Administrator authority are allowed to revoke CONTROL privilege for a nickname. For this reason, when the ALL PRIVILEGES clause is specified, all nickname privileges except CONTROL privilege are revoked from each Forfeiter; CONTROL privilege must be revoked separately.
Distinct Type Privileges (DB2 for iSeries and DB2 for zSeries Only)
REVOKE USAGE ON DISTINCT TYPE [TypeName] FROM [Forfeiter, ...]
where:
| TypeName | Identifies by name the distinct type that the USAGE privilege is to be associated with. |
| Forfeiter | Identifies the name of the user(s) and/or group(s) that are to lose the distinct type privileges specified. The value specified for the Forfeiter parameter can be any combination of the following: [UserName] and PUBLIC. |
It is important to note that this statement is not supported by DB2 for Linux, UNIX, and Windows. Instead, with DB2 for Linux, UNIX, and Windows the group PUBLIC is implicitly granted USAGE privilege on all distinct types, and that privilege cannot be revoked.
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 for the table DEPARTMENT (except CONTROL privilege) 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 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:
________________________________________________________________ REVOKE UPDATE(address, home_phone) ON TABLE emp_info FROM user1 BY ALL | |||
| Example 5: | Take away USER1's ability to read data stored in a table named INVENTORY:
________________________________________________________ REVOKE SELECT ON TABLE inventory FROM user1 | |||
| Example 6: | Prevent users in the group PUBLIC from adding or changing data stored in a table named EMPLOYEE:
________________________________________________________________ REVOKE INSERT, UPDATE ON TABLE employee FROM PUBLIC |
Requirements for Granting and Revoking Authorities and Privileges
Not only do authorization levels and privileges control what a user can and cannot do; they also control what authorities and privileges a user is allowed to grant and revoke. A list of the authorities and privileges a user who has been given a specific authority level or privilege is allowed to grant and revoke can be seen in Table 3-1.
| If a User Holds … | The User Can Grant … | The User Can Revoke … |
|---|---|---|
| System Administrator (SYSADM) authority | System Control (SYSCTRL) authority | System Control (SYSCTRL) authority |
| System Maintenance (SYSMAINT) authority | System Maintenance (SYSMAINT) authority | |
| System Monitor (SYSMON) authority | System Monitor (SYSMON) authority | |
| Database Administrator (DBADM) authority | Database Administrator (DBADM) authority | |
| Security Administrator (SECADM) authority | Security Administrator (SECADM) authority | |
| Load (LOAD) authority | Load (LOAD) authority | |
| Any database privilege, including CONTROL privilege | Any database privilege, including CONTROL privilege | |
| Any object privilege, including CONTROL privilege | Any object privilege, including CONTROL privilege | |
| System Control (SYSCTRL) authority | The USE table space privilege | The USE table space privilege |
| System Maintenance (SYSMAINT) authority | No authorities or privileges | No authorities or privileges |
| System Monitor (SYSMON) authority | No authorities or privileges | No authorities or privileges |
| Database Administrator (DBADM) authority | Any database privilege, including CONTROL privilege Any object privilege, including CONTROL privilege | Any database privilege, including CONTROL privilege Any object privilege, including CONTROL privilege |
| Security Administrator (SECADM) authority | No authorities or privileges; only security labels | No authorities or privileges; only security labels |
| Load (LOAD) authority | No authorities or privileges | No authorities or privileges |
| CONTROL privilege on an object (but no other authority) | All privileges available (with the exception of the CONTROL privilege) for the object the user holds CONTROL privilege on | All privileges available (with the exception of the CONTROL privilege) for the object the user holds CONTROL privilege on |
| A privilege on an object that was assigned with the WITH GRANT OPTION option specified | The same object privilege that was assigned with the WITH GRANT OPTION option specified. | No authorities or privileges |
Open table as spreadsheet 
EAN: 2147483647
Pages: 93