Some system roles are indirectly granted the system privileges necessary to execute privileged tasks through membership in compatibility roles.
You cannot revoke the underlying system privileges of a compatibility role; you must first migrate it to a user-defined role. Only then can you revoke individual underlying system privileges from the new role and grant them to other user-defined roles per the organization's security requirements. This enforces separation of duties.
You can migrate compatibility roles automatically or manually. The method of migration can impact the ability of a system role to continue performing authorized tasks.
Regardless of the migration method used, once a compatibility role is dropped, if you revoke a system privilege from the new user-defined role and grant it to another user-defined role, you must do one of the following to ensure that system roles retain all the system privileges required to execute applicable privileged tasks:| System Role | Compatibility Role |
|---|---|
| dbo | SYS_AUTH_DBA_ROLE SYS_AUTH_RESOURCE_ROLE |
| SYS_RUN_REPLICATION_ROLE | SYS_AUTH_DBA_ROLE |
The ALTER ROLE statement creates a new user-defined role, automatically grants all underlying system privileges of the compatibility role to the new user-defined role, makes each member of the compatibility role a member of the new user-defined role, then drops the compatibility role.
Automatic migration assumes that the destination user-defined role does not already exist and that all system privileges are migrated to the same new user-defined role.
Use the CREATE ROLE statement to create a new user-defined role. Use the GRANT statement to grant each underlying system privilege to one or more users or roles. Use the DROP statement to drop the compatibility role once all underlying system privileges are granted to at least one other user or role.
Members of the migrated compatibility role are not automatically granted membership in the new user-defined role. As a result, members of some system roles may no longer be able to perform the expected privileged tasks once the compatibility role is dropped. You must grant membership in the new user-defined role to the affected system roles or directly grant the required system privileges to affected members.