| 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 [View full width] [View full width] 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 [View full width] [View full width] 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 | 