Show TOC

ALTER ROLE StatementLocate this document in the navigation structure

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

Note You cannot use the ALTER ROLE statement to migrate SYS_AUTH_SA_ROLE or SYS_AUTH_SSO_ROLE. These roles are automatically migrated when SYS_AUTH_DBA_ROLE is migrated.

Quick Links:

Go to Parameters

Go to Examples

Go to Usage

Go to Standards

Go to Permissions

Syntax

Syntax 1 – To migrate SYS_AUTH_DBA_ROLE

ALTER ROLE <predefined_sys_role_name>
   MIGRATE TO <new_role_name [, new_sa_role_name, new_sso_role_name]>

Syntax 2 – To migrate all other compatibility roles

ALTER ROLE <predefined_sys_role_name> 
   MIGRATE TO <new_role_name>
Parameters

(back to top)

  • predefined_sys_role_name the name of a compatibility role that still exists (has not already been dropped) in the database.
  • new_role_name the name of the new role cannot begin with the prefix SYS_ or end with the suffix _ROLE.
  • new_sa_role_name required only when migrating SYS_AUTH_DBA_ROLE. The new role to which the underlying system privileges of SYS_AUTH_SA_ROLE are to be migrated to cannot already exist in the database, and the new role name cannot begin with the prefix SYS_ or end with the suffix _ROLE.
  • new_sso_role_name required only when migrating SYS_AUTH_DBA_ROLE. The new role to which the underlying system privileges of SYS_AUTH_SSO_ROLE are to be migrated to cannot already exist in the database, and the new role name cannot begin with the prefix SYS_ or end with the suffix _ROLE.
Examples

(back to top)

  • Example 1 migrates SYS_AUTH_DBA_ROLE to the new roles Custom_DBA, Custom_SA, and Custom_SSO respectively. It then automatically migrates all users, underlying system privileges, and roles granted to SYS_AUTH_DBA_ROLE to the applicable new roles. Finally, it drops SYS_AUTH_DBA_ROLE, SYS_AUTH_SA_ROLE, and SYS_AUTH_SSO_ROLE.
    ALTER ROLE SYS_AUTH_DBA_ROLE
    MIGRATE TO Custom_DBA, Custom_SA, Custom_SSO
    
  • Example 2 migrates SYS_AUTH_OPERATOR_ROLE role to the new role Operator_role. It then automatically migrates all users, underlying system privileges, and roles granted to SYS_AUTH_OPERATOR_ROLE to the new role and drops SYS_AUTH_OPERATOR_ROLE.
    ALTER ROLE SYS_AUTH_OPERATOR_ROLE
    MIGRATE TO Operator_role
    
Usage

(back to top)

During the migration process:

  • A new user-defined role is created.
  • All of the system privileges currently granted to the migrating predefined role are automatically granted to the new user-defined role.
  • All users and roles currently granted to the migrating predefined role are automatically granted to the new user-defined role.
  • The compatibility role is dropped.

Since no role administrator was specified during the migration process, only global role administrators can manage the new role. Use the CREATE ROLE statement to add role administrators with appropriate administrative rights to the role.

Standards

(back to top)

ANSI SQL–Compliance level: Transact-SQL extension.

Permissions

(back to top)

Requires the MANAGE ROLES system privilege granted with administrative rights.