Changes in Inheritance Behavior for Some Authorities That Became Compatibility Roles

In pre-16.0 databases, if you granted the DBA, REMOTE DBA, BACKUP, RESOURCE, and VALIDATE authorities to a group, the underlying permissions were not inherited by members of the group.

Now, however, the default behavior when granting one of these roles (now called SYS_AUTH_DBA_ROLE, SYS_RUN_REPLICATION_ROLE, SYS_AUTH_BACKUP_ROLE, SYS_AUTH_RESOURCE_ROLE, and SYS_AUTH_VALIDATE_ROLE) to a user-defined role is to allow those who have been granted the user-defined role to inherit the underlying system privileges of the role.

Suppose you have a user, userA. You grant userA the ALTER ANY OBJECT system privilege. You then decide to extend userA to become a role, and then grant userA to userB. Now you want to grant the SYS_AUTH_DBA_ROLE role to userA, but you don't want userB to inherit all the privileges that the SYS_AUTH_DBA_ROLE role gives. You would therefore grant the SYS_AUTH_DBA_ROLE role as follows:

GRANT ROLE SYS_AUTH_DBA_ROLE TO userA WITH NO SYSTEM PRIVILEGE INHERITANCE;

In this scenario, userB inherits only the ALTER ANY OBJECT system privilege from userA.

To retain the non-inheritance behavior of these roles after upgrading, include the WITH NO SYSTEM PRIVILEGE INHERITANCE clause in the GRANT ROLE statement. Likewise, if you have applications that you are changing to use the new GRANT syntax, you must specify this clause as well. This clause is only for use with these specific roles.

Note: The WITH NO SYSTEM PRIVILEGE INHERITANCE clause is only supported with these specific roles; any other use results in an error.