ALTER ROLE statement

Migrates a compatibility role to a user-defined role, and then drops the compatibility role.

Syntax

ALTER ROLE compatibility-role-name 
MIGRATE TO new-role-name [, new-sa-role-name, new-sso-role-name ]

Parameters

compatibility-role-name
Use this parameter to specify the name of the compatibility role you are migrating.
new-role-name
Use this parameter to specify the name of the new role you are creating.
new-sa-role-name
Use this parameter to specify the name of the new role to migrate the SYS_AUTH_SA_ROLE role to. This parameter is required when migrating SYS_AUTH_DBA_ROLE, which causes SYS_AUTH_SA_ROLE to be migrated automatically.
new-sso-role-name
Use this parameter to specify the name of the new role to migrate the SYS_AUTH_SSO_ROLE role to. This parameter is required when migrating SYS_AUTH_DBA_ROLE, which causes SYS_AUTH_SSO_ROLE to be migrated automatically.

Remarks

The name of the new role must not begin and end with 'SYS_' and '_ROLE', respectively. For example SYS_MyBackup_ROLE is not an acceptable name for a user-defined role, whereas MyBackup_ROLE and SYS_MyBackup are acceptable.

When you execute the ALTER ROLE statement, grantees of the compatibility role are granted the new role.

You can restore migrated compatibility roles that have been migrated and then dropped by executing a CREATE ROLE statement and specifying the compatibility role name. For example, CREATE ROLE SYS_AUTH_BACKUP_ROLE; restores the SYS_AUTH_BACKUP_ROLE compatibility role.

Initially, only users with full administration rights (DBAs) can administer the new role, but you can use the CREATE ROLE statement with the OR REPLACE clause to specify additional administrators.

Use the GRANT or REVOKE statements to grant system privileges to the role, or revoke system privileges from the role.

You can migrate the SYS_AUTH_SA_ROLE and SYS_AUTH_SSO_ROLE compatibility roles by migrating SYS_AUTH_DBA_ROLE compatibility, which causes SYS_AUTH_SA_ROLE and SYS_AUTH_SSO_ROLE to be migrated automatically. When migrating SYS_AUTH_DBA_ROLE, you must include the new-sa-role-name and new-sso-role-name parameters to give new names to migrated SYS_AUTH_SA_ROLE and SYS_AUTH_SSO_ROLE roles.

Privileges

You must have the MANAGE ROLES system privilege and administrative rights on the compatibility role you are migrating.

Side effects

None

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

The following statement migrates all users and underlying system privileges granted to SYS_AUTH_BACKUP_ROLE role to a new role, custom_Backup_ROLE, and then drops SYS_AUTH_BACKUP_ROLE from the database.

ALTER ROLE SYS_AUTH_BACKUP_ROLE
MIGRATE TO custom_Backup_ROLE;

The following statement migrates all users, underlying system privileges, and roles granted to SYS_AUTH_DBA_ROLE compatibility role to a new role, custom_DBA. It then automatically migrates all users, underlying system privileges, and roles granted to SYS_AUTH_SA_ROLE and SYS_AUTH_SSO_ROLE to new roles called custom_SA and custom_SSO, respectively. Finally, it drops SYS_AUTH_DBA_ROLE, SYS_AUTH_SA_ROLE, and SYS_AUTH_SSO_ROLE from the database.

ALTER ROLE SYS_AUTH_DBA_ROLE 
MIGRATE TO custom_DBA, custom_SA, custom_SSO;