Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.
Migrates a compatibility role to a user-defined role, and then drops the compatibility role.
ALTER ROLE compatibility-role-name MIGRATE TO new-role-name [, new-sa-role-name, new-sso-role-name ]
Use this parameter to specify the name of the compatibility role you are migrating.
Use this parameter to specify the name of the new role you are creating.
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.
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.
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.
You must have the MANAGE ROLES system privilege and administrative rights on the compatibility role you are migrating.
Not in the standard.
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;