ALTER ROLE Statement

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)