Oracle: Setting Up Source User Privileges

SQLWays utility uses select statements to all_*, dba_* and sys.* tables and views in order to extract information about the source DDL for all the objects that need to be converted. So, the following privileges are required to support the export for Oracle objects:

• CREATE SESSION
• SELECT ANY TABLE
• GRANT SELECT ON sys.procedure$
• GRANT SELECT ON dba_users
• GRANT SELECT ON dba_part_key_columns
• GRANT SELECT ON dba_synonyms
• GRANT ALL [PRIVILEGES] ON OWNER.OBJECT_NAME for each object that needs to be migrated

NOTE: You can put a select statement into LOOP with EXECUTE IMMEDIATE, that will generate and execute grants for the required objects.

BEGIN
        FOR cur IN (SELECT object_type, 'grant all on '||owner||'.'||object_name||' to <YOUR_USER_NAME>' as exec_stmt FROM all_objects
                    WHERE object_type NOT IN ('INDEX','LOB','TABLE PARTITION','CLUSTER','CONSUMER GROUP','CONTEXT','DESTINATION','DIRECTORY',
                                              'EDITION','EVALUATION CONTEXT','JAVA CLASS','INDEX PARTITION','JOB','JOB CLASS','JAVA DATA',
                                              'JAVA RESOURCE','XML SCHEMA','LIBRARY','LOB PARTITION','OPERATOR','WINDOW')
                    AND owner IN ('<YOUR_LIST_OF_SCHEMA_NAMES>')
                    ) LOOP
           EXECUTE IMMEDIATE cur.exec_stmt;
        END LOOP;
     END;

OR

You can just initiate GRANT DBA TO user_name.

The minimum privileges required to collect information about the DDL of objects are the following:

• GRANT CREATE SESSION TO YOUR_ORACLE_USER;
• GRANT SELECT ANY DICTIONARY TO YOUR_ORACLE_USER;

Please note that in this case it is necessary to select “DBA” option on the Source Options page. To select “DBA” option click on the “Advanced…” button as shown in the screenshot below:

If you have any other questions, please contact us: support@ispirer.com