Granting a compatibility role (SQL)

Grant a compatibility role to a user, or user-extended role. You cannot grant a compatibility role to a user-defined role that is not a user-extended role.

Prerequisites

You must have administrative rights over the role you are granting.

Context and remarks

Normally, you grant compatibility roles to users or roles to maintain backwards compatibility.

When you grant a compatibility role to a role, the compatibility role's system privileges are available to both the role and its grantees. However, you can disable the inheritance of the system privileges for the following compatibility roles:

SYS_AUTH_DBA_ROLE
SYS_AUTH_RESOURCE_ROLE
SYS_AUTH_BACKUP_ROLE
SYS_AUTH_VALIDATE_ROLE

When you disable the inheritance of a compatibility role's system privileges, the system privileges are available only to the role, not to its grantees. Disabling system privilege inheritance for a compatibility role mimics the behavior of the non-inheritable authority in version 12 and earlier databases.

Task

Execute a GRANT ROLE statement:

Option

Example statement

Grant the ability to exercise the role along with the ability to grant it to and revoke it from other users or roles:

GRANT ROLE SYS_AUTH_DBA_ROLE TO user-or-role-name 
WITH ADMIN OPTION;

Grant the ability to grant the role or revoke it from other users or roles, but not exercise the role:

GRANT ROLE SYS_AUTH_DBA_ROLE TO user-or-role-name 
WITH ADMIN ONLY OPTION;

Grant the ability to exercise the role but not grant it to, nor revoke it from, other users or roles:

GRANT ROLE SYS_AUTH_DBA_ROLE TO user-or-role-name 
WITH NO ADMIN OPTION;

Grant the ability to exercise a compatibility role and disable system privilege inheritance of the role:

GRANT ROLE SYS_AUTH_DBA_ROLE TO role-name 
WITH NO SYSTEM PRIVILEGE INHERITANCE;

Results

The compatibility role is granted to the user or role.

Example

This example grants the SYS_AUTH_BACKUP_ROLE to role R_HumanResources.

GRANT ROLE SYS_AUTH_BACKUP_ROLE TO R_HumanResources 
WITH NO SYSTEM PRIVILEGE INHERITANCE;

Granting a compatibility role (SQL)

Task

See also