GrantSystemPriv Procedure

This procedure grants system-level privileges (not restricted to a particular workspace) to users and roles. The grant_option parameter enables the grantee to then grant the specified privileges to other users and roles.

Syntax

DBMS_WM.GrantSystemPriv(
   priv_types       IN VARCHAR2,
   grantee          IN VARCHAR2
   [, grant_option  IN VARCHAR2 DEFAULT 'NO']
   [, auto_commit   IN BOOLEAN DEFAULT TRUE]);

Parameters

Table 70-19 GrantSystemPriv Procedure Parameters  

Parameter	Description
priv_types	A string of one or more keywords representing privileges. Use commas to separate privilege keywords. The available keywords are ACCESS_ANY_WORKSPACE, MERGE_ANY_WORKSPACE, CREATE_ANY_WORKSPACE, REMOVE_ANY_WORKSPACE, and ROLLBACK_ANY_WORKSPACE.
grantee	Name of the user (can be the PUBLIC user group) or role to which to grant priv_types.
grant_option	Specify YES to enable the grant option for grantee, or NO (the default) to disable the grant option for grantee. The grant option allows grantee to grant the privileges specified in priv_types to other users and roles.
auto_commit	A boolean value (TRUE or FALSE). TRUE (the default) causes the operation to be executed as an autonomous regular transaction that will be committed when it finishes. FALSE causes the operation to be executed as part of the caller's open regular transaction (if one exists). If there is no open regular transaction, the operation is executed in a new regular transaction. In either case, the caller is responsible for committing the transaction.

Usage Notes

Contrast this procedure with GrantWorkspacePriv Procedure, which grants workspace-level Workspace Manager privileges with keywords that do not contain ANY and which has a workspace parameter.

If a user gets a privilege from more than one source and if any of those sources has the grant option for that privilege, the user has the grant option for the privilege. For example, assume that user SCOTT has been granted the ACCESS_ANY_WORKSPACE privilege with grant_option as NO, but that the PUBLIC user group has been granted the ACCESS_ANY_WORKSPACE privilege with grant_option as YES. Because user SCOTT is a member of PUBLIC, user SCOTT has the ACCESS_ANY_WORKSPACE privilege with the grant option.

The WM_ADMIN_ROLE role has all Workspace Manager privileges with the grant option. The WM_ADMIN_ROLE role is automatically given to the DBA role.

The ACCESS_WORKSPACE or ACCESS_ANY_WORKSPACE privilege is needed for all other Workspace Manager privileges.

To revoke system-level privileges, use the RevokeSystemPriv Procedure.

An exception is raised if one or more of the following apply:

• grantee is not a valid user or role in the database.

• You do not have the privilege to grant priv_types.

Examples

The following example enables user Smith to access any workspace in the database, but does not allow Smith to grant the ACCESS_ANY_WORKSPACE privilege to other users.

EXECUTE DBMS_WM.GrantSystemPriv ('ACCESS_ANY_WORKSPACE', 'Smith', 'NO');