Configuring roles and privileges for a role (SQL)

For any role, you can: grant roles or privileges to it, revoke roles or privileges from it, and set the administrative rights the role has over the roles and privileges it has been granted.

Prerequisites

If you are granting or revoking a system role you must have the MANAGE ROLES system privilege.

If you are granting or revoking a compatibility role, you must have administration rights for the role.

If you are granting or revoking SYS_RUN_REPLICATION_ROLE, you must have the SYS_REPLICATION_ADMIN_ROLE system role.

Task

Connect to the database.

Execute statements similar to the following, according to the change you want to make:

Option

Action

Grant a privilege

GRANT privilege-name TO target-role-name;

Revoke a privilege

REVOKE privilege-name FROM target-role-name;

Grant a role

GRANT ROLE role-name TO target-role-name;

Revoke a role

REVOKE ROLE role-name FROM target-role-name;

Results

The role is configured.

Example

To grant the CREATE ANY OBJECT system privilege to the role RoleA without giving RoleA administrative rights, execute the following statement:

GRANT CREATE ANY OBJECT TO RoleA;

To grant RoleA the CREATE ANY OBJECT system privilege along with the ability to grant or revoke the system privilege to and from other users and roles, execute the following statement:

GRANT CREATE ANY OBJECT TO RoleA WITH ADMIN OPTION;

To grant RoleB along with its administrative rights to user Jane, execute the following statement:

GRANT ROLE RoleB TO Jane WITH ADMIN OPTION;

To grant user John the administrative rights to RoleB, but the inability to use RoleB, execute the following statement:

GRANT ROLE RoleB TO John WITH ADMIN ONLY OPTION;

Configuring roles and privileges for a role (SQL)

Task

See also