Oracle Cloud Infrastructure Documentation

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:

  • Autonomous AI Database is fully managed: Many database management operations performed using these privileges and roles are handled automatically, and granting them could compromise the security of the Autonomous AI Database.

  • Replacement and enhancement with new roles: Certain older roles are replaced or extended with Autonomous AI Database–specific roles that provide equivalent capabilities.

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

  • Ignoring unsupported statements: Statements referring to roles or privileges not supported in Autonomous AI Database are ignored.

  • Mapping to supported equivalents: In many cases, unsupported roles are replaced with their corresponding Autonomous AI Database equivalents, such as replacing the DBA role with the PDB_DBA role.

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:

  • Seamless migration: Automatic adjustment of unsupported roles and privileges reduces migration complexity and helps prevent failures due to unsupported Grant or Revoke operations.

  • Reduced manual effort:Eliminates the need for manual review and editing of SQL statements, enabling faster, error-free migrations.

  • Operational continuity: By suppressing errors and mapping roles, Autonomous AI Database ensures business operations are not interrupted by privilege-related issues.

  • Enhanced transparency: All automatic adjustments are auditable through the DBA_CONVERTED_STATEMENTS view, providing clear insight into changes made during migration.

Topics

  • 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.
  • Limitations
    Lists the roles and privileges that are not supported when migrating from other Oracle databases to Autonomous AI Database.

Parent topic: Notes for Users Migrating from Other Oracle Databases

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’s 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.

  • original_sql_text is the original SQL statement (up to the first 1000 characters).

  • converted_sql_text is the converted SQL statement (up to the first 1000 characters).

  • action_taken indicates whether the statement was IGNORED or REPLACED.

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

  • SYSDBA

  • SYSOPER

  • GRANT ANY PRIVILEGE

  • CREATE LIBRARY

  • CREATE ANY LIBRARY

  • EXPORT FULL DATABASE

  • IMPORT FULL DATABASE

  • CREATE EXTERNAL JOB

  • SYSBACKUP

  • SYSDG

  • SYSKM

  • ADMINISTER KEY MANAGEMENT

  • FLASHBACK ARCHIVE ADMINISTER

  • INHERIT ANY REMOTE PRIVILEGE

  • CREATE CREDENTIAL

  • CREATE ANY CREDENTIAL

  • SYSRAC

  • TEXT DATASTORE ACCESS

  • ENABLE DIAGNOSTICS

  • CREATE TRUE CACHE

Roles

  • CDB_DBA

  • EM_EXPRESS_ALL

  • EM_EXPRESS_BASIC

  • XDB_SET_INVOKER

  • XDB_WEBSERVICES

  • XDB_WEBSERVICES_WITH_PUBLIC

  • XDB_WEBSERVICES_OVER_HTTP

  • EXECUTE_CATALOG_ROLE

  • SCHEDULER_ADMIN

  • OEM_MONITOR

  • GDS_CATALOG_SELECT

  • HS_ADMIN_EXECUTE_ROLE

  • DBMS_MDX_INTERNAL

  • EJBCLIENT

  • JMXSERVER

  • GGSYS_ROLE

  • XDBADMIN