DB2 10.5 for Linux, UNIX, and Windows

Granting and revoking privileges on modules

Granting and revoking privileges on modules is a task that you would perform when you want to allow or disallow users of the database to be able to reference objects defined within the module as part of a security practice.

Before you begin

Before proceeding with this task:

Review the GRANT (Module privileges) statement syntax and the Revoke (Module privileges) statement syntax.
The module must exist.
Ensure that you have the authority to execute either the GRANT or REVOKE statement.
Identify the authorization ID of the user, group, or role to be granted or revoked privileges.

About this task

The EXECUTE privilege on a module can be granted or revoked. The EXECUTE privilege enables users to perform many tasks including: execute published routines defined in the module, read and write to published global variables, reference any published user-defined types.

Restrictions

For restrictions:

See: Restrictions on modules

Procedure

Formulate a GRANT or REVOKE statement:
1. Specify the GRANT clause to grant privileges or the REVOKE clause to revoke privileges.
2. Specify the EXECUTE ON MODULE clause to specify the privilege.
3. Specify the name of the module.
4. Specify the TO clause if granting the privilege or specify the FROM clause if revoking the privilege.
5. Specify the authorization ID of the user for whom the privileges are being changed.
Execute the statement.

Results

If the statement executes successfully, the privileges on the module will be updated.

Example

The following is an example of an SQL statement that grants the EXECUTE privilege on module inventory to the authorization ID jones:

GRANT EXECUTE ON MODULE inventory TO jones@

The following is an example of an SQL statement that revokes the EXECUTE privilege on module inventory from the authorization ID macdonald:

REVOKE EXECUTE ON MODULE inventory FROM macdonald@

What to do next

Once you have successfully granted or revoked privileges on the module, you can validate the changes were made by verifying that an attempt to execute a routine in the module works as expected.