Implications of Migrating Compatibility Roles on System Roles

The underlying system privileges of a compatibility role cannot be revoked. It must first be migrated to a user-defined role. Only then can the underlying system privileges be individually revoked from the new role and granted to other user-defined roles per the organization's security requirements and to enforce separation of duties.

Compatibility roles can be migrated automatically or manually. Depending on how migration is done can impact a system role's ability to continue performing authorized tasks.

Automatic Migration

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, and then drops the compatibility role.

Automatic migration assumes that the destination user-defined role does not already exist and all system privileges are migrated to the same new user-defined role.

Manual Migration

The CREATE ROLE and GRANT statements let you create new user-defined roles, if needed, and then grant each underlying system privilege to one or more users or roles. Once all underlying system privileges have been granted to at least one other user or role, you can drop the compatibility role.

Members of the compatibility role are not automatically granted membership in the new user-defined role. As a result, when the compatibility role is ultimately dropped, some system roles may no longer be able to perform expected privileged tasks. The affected system role must be granted membership in the new user-defined role or be directly granted the required system privileges in order to be able to continue performing their expected privileged tasks.

Members of the compatibility role are not automatically granted membership in the new user-defined role. As a result, when the compatibility role is ultimately dropped, some system roles may no longer be able to perform expected privileged tasks. The affected system role must be granted membership in the new user-defined role or be directly granted the required system privileges in order to be able to continue performing their expected privileged tasks.