Manage Roles and Privileges when Migrating to Autonomous AI Database

Describes how roles and privileges are managed when migrating to Autonomous AI Database, including details on unsupported roles, privilege conversion, and automated handling during migration.

When migrating from other Oracle databases to Autonomous AI Database, you may notice differences in how roles and system privileges are supported. Some roles and privileges common in traditional Oracle databases are not available in Autonomous AI Database. This is primarily because:

To ensure a seamless migration experience, Autonomous AI Database automatically manages unsupported roles and privileges by either:

These conversions are performed transparently and you can review all automatically converted statements in the DBA_CONVERTED_STATEMENTS view after migration. The table shows a list of roles from other Oracle databases and their mapped equivalents in Autonomous AI Database:

Source Database Role Mapped Role in Autonomous AI Database
DBA PDB_DBA
DATAPUMP_EXP_FULL_DATABASE DATAPUMP_CLOUD_EXP
DATAPUMP_IMP_FULL_DATABASE DATAPUMP_CLOUD_IMP
EXP_FULL_DATABASE DATAPUMP_CLOUD_EXP
IMP_FULL_DATABASE DATAPUMP_CLOUD_IMP

See Limitations for a list of roles and privileges that are not supported on Autonomous AI Database and, therefore, cannot be resolved during migration or import operations.

The following are the key benefits of automatic role and privilege handling during migration to Autonomous AI Database:

Query and Manage the Converted Statements

You can query the sys.converted_stmts$ view to review original and converted SQL statements, along with the action taken during migration.

You can review which Grant and Revoke statements were modified or ignored during migration by querying the sys.converted_stmts$ view. This view displays the original SQL statement, the converted statement, if applicable, and the action taken, allowing you to audit changes and ensure alignment with Autonomous AI Database supported roles and privileges.

The following examples show how to query and interpret this information:

Example 1:

SELECT
    DBMS_LOB.SUBSTR(original_sql_text, 1000, 1) AS original_sql_text,
    DBMS_LOB.SUBSTR(converted_sql_text, 1000, 1) AS converted_sql_text,
    action_taken
FROM
    sys.converted_stmts$;

Sample output shows that the original SQL statement was replaced with a new statement containing only the supported roles and privileges in Autonomous AI Database.

ORIGINAL_SQL_TEXT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
grant DBA, DATAPUMP_EXP_FULL_DATABASE, DATAPUMP_IMP_FULL_DATABASE, CDB_DBA, EM_EXPRESS_ALL, CREATE ANY LIBRARY, SYSDBA, ALTER SESSION, ALTER SYSTEM to usr1

CONVERTED_SQL_TEXT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
GRANT ALTER SYSTEM, ALTER SESSION, UNLIMITED TABLESPACE, DATAPUMP_CLOUD_EXP, DATAPUMP_CLOUD_IMP, PDB_DBA TO USR1

ACTION_TAKEN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
REPLACED

The DATAPUMP_EXP_FULL_DATABASE and DATAPUMP_IMP_FULL_DATABASE roles are not applicable in Autonomous AI Database. These roles are automatically mapped to the DATAPUMP_CLOUD_EXP and DATAPUMP_CLOUD_IMP roles, respectively.

Example 2:

SELECT
    DBMS_LOB.SUBSTR(original_sql_text, 1000, 1) AS original_sql_text,
    DBMS_LOB.SUBSTR(converted_sql_text, 1000, 1) AS converted_sql_text,
    action_taken
FROM
    sys.converted_stmts$;

Sample output shows that the original SQL statement was ignored because none of the specified roles or privileges are supported in Autonomous AI Database.

ORIGINAL_SQL_TEXT
--------------------------------------------------------------------------------
grant CREATE LIBRARY, CDB_DBA, EM_EXPRESS_ALL, XDB_WEBSERVICES from usr1

CONVERTED_SQL_TEXT
--------------------------------------------------------------------------------
(null)

ACTION_TAKEN
--------------------------------------------------------------------------------
IGNORED

The output shows that the original SQL statement requesting privileges such as CREATE LIBRARY, EM_EXPRESS_ALL, and XDB_WEBSERVICES was ignored. These roles and privileges are not supported in Autonomous AI Database. As a result, the corresponding converted SQL is null, and the action taken is marked as IGNORED.

See PURGE_CONVERTED_STMTS View for more information.

After reviewing the converted statements in the sys.converted_stmts$ view, you can remove all statements or only those older than a specified retention period by running the purge_converted_stmts procedure as needed. For example:

BEGIN
  DBMS_CLOUD_ADMIN.PURGE_CONVERTED_STMTS(
    retention_date => SYSTIMESTAMP - INTERVAL '1' DAY
  );
END;
/

This removes entries from the sys.converted_stmts$ view that are older than one day, based on the current timestamp.

See PURGE_CONVERTED_STMTS for more information.

Limitations

Lists the roles and privileges that are not supported when migrating from other Oracle databases to Autonomous AI Database.

Privileges:

Roles: