ALTER ROLE Statement

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.

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

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

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

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

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Requires the MANAGE ROLES system privilege granted with administrative rights.