Migrating a compatibility role to a user-defined role (SQL)

Migrate a compatibility role to a user-defined role, and then grant the new role to other roles and users. When you migrate a compatibility role, all grantees of the compatibility role are automatically granted the user-defined role, and the compatibility role is deleted.

Prerequisites

You must have the MANAGE ROLES system privilege and administrative rights on the role being migrated.

Context and remarks

Compatibility roles are like starter roles. You cannot modify the underlying system privileges of compatibility roles. However, you can migrate them to user-defined roles, and then modify the privileges.

The name you assign to the new role cannot begin with the prefix SYS_ and end with the suffix _ROLE. For example, SYS_MyBackup_ROLE cannot be the name of a user-defined role. The name you give the new role is permanent; you cannot alter it later.

If you migrate a role for which system privilege inheritance has been disabled (for example, SYS_AUTH_VALIDATE_ROLE), the privileges of the newly created user-defined role will be granted to all grantees in the inheritance tree.

The SYS_AUTH_SA_ROLE and SYS_AUTH_SSO_ROLE system roles cannot be migrated individually. However, when the SYS_AUTH_DBA_ROLE system role is migrated, the SYS_AUTH_SA_ROLE and SYS_AUTH_SSO_ROLE system roles are automatically included in the new user-defined role.

Task

To migrate a compatibility role, execute an ALTER ROLE statement similar to the following:
ALTER ROLE compatibility-role MIGRATE TO new-role-name;
If you are migrating the SYS_AUTH_DBA_ROLE system role, remember to include names for the SYS_AUTH_SA_ROLE and SYS_AUTH_SSO_ROLE system roles that get automatically migrated (that is, MIGRATE TO new-role-name, new-sa-role-name, new-sso-role-name).

Results

All grantees of the compatibility role are automatically granted the user-defined role. The compatibility role is deleted.

Next

Modify the system privileges of the new user-defined role and grant the role to other users.

Although compatibility roles are dropped automatically after being migrated, you can restore them for future purposes using the CREATE ROLE statement. See Restoring a compatibility role (SQL).

Example

The following statement migrates the SYS_AUTH_BACKUP_ROLE compatibility role to the new user-defined role, my_BACKUP. All users that were previously granted the SYS_AUTH_BACKUP_ROLE compatibility role are now granted the my_BACKUP role, including whatever administrative privileges they had over the compatibility role.

ALTER ROLE SYS_AUTH_BACKUP_ROLE MIGRATE TO my_BACKUP;

The following statement migrates the SYS_AUTH_DBA_ROLE, SYS_AUTH_SA_ROLE, and SYS_AUTH_SSO_ROLE system roles to the user-defined roles my_DBA, my_DBA_Administration and my_DBA_Security, respectively. All users, underlying system privileges, and roles granted to the original roles are automatically migrated to the new roles. Finally, the SYS_AUTH_DBA_ROLE, SYS_AUTH_SA_ROLE, and SYS_AUTH_SSO_ROLE system roles are dropped.

ALTER ROLE SYS_AUTH_DBA_ROLE
MIGRATE TO my_DBA, my_DBA_Administration, my_DBA_Security;

Migrating a compatibility role to a user-defined role (SQL)

Task

See also