How Do I Set an Independent Oracle Account That Has the Least Privilege and Uses DRS?

To perform a full migration for an Oracle database, you must grant the CREATE SESSION, SELECT ANY TRANSACTION, SELECT ANY TABLE, and the SELECT ANY DICTIONARY permissions to the user. If you need to perform an incremental migration, the user must have the log parsing permission. If the destination database is a PostgreSQL database, the SELECT ANY SEQUENCE permission is also required. This section describes how to set an independent Oracle account that has the least privilege and uses DRS.

• Full migration
  1. Create a user for migration. User1 is used as an example.

     Example command: CREATE USER User1 IDENTIFIED BY pwd

     

     User1 indicates the username and pwd indicates the password.

  2. Run the following statement as user sys or as user who has the DBA permission to grant the required permissions to User1:

     Example command: GRANT CREATE SESSION, SELECT ANY TRANSACTION, SELECT ANY TABLE, SELECT ANY DICTIONARY TO User1

• Full+incremental migration
  1. Create a user for migration. User1 is used as an example.

     Example command: CREATE USER User1 IDENTIFIED BY pwd

  2. Run the following statement as user sys or as user who has the DBA permission to grant the required permissions to User1:

     Example command: GRANT CREATE SESSION, SELECT ANY TRANSACTION, SELECT ANY TABLE, SELECT ANY DICTIONARY TO User1

  3. Run the following statement as user sys or as user who has the DBA permission to grant the log parsing permission to User1:
     • If Oracle version is earlier than 12c, run the following statement:

       GRANT EXECUTE_CATALOG_ROLE TO User1

     • If Oracle version is later than 12c, run the following statement:

       Example command: GRANT EXECUTE_CATALOG_ROLE TO User1

       Example command: GRANT LOGMINING TO User1