Specific privileges for extraction for Oracle v 12.1 and earlier

To extract Oracle tables from ERP you require a user with ‘Connect’ and ‘Select’ privileges on the Oracle database.

To assign minimum privileges :

1. Log onto the ERP backend database (using SQL Plus, TOAD or any other client) with any user who has admin privileges. This can be run from a windows or Unix environment.
2. Check if the ‘APPROVA’ user who will be used for extracting data already exists in the Oracle database by running the following query:

   SELECT * FROM DBA_USERS WHERE USERNAME='APPROVA';

3. Create the ‘APPROVA’ user by running the following command (If the APPROVA user is already present, skip this step and move to step 4):

   CREATE USER APPROVA IDENTIFIED BY APPROVA;

   Note:  ‘APPROVA’ is the preset password of the database user, APPROVA. You can choose any other appropriate password
4. Run the "GRANT CONNECT TO APPROVA;" command. This command grants connect privileges to the created user ‘APPROVA’.
5. Check if the ‘ROLE_APPROVA_ORCL’ role already exists in the Oracle database by running the following query:

   SELECT * FROM DBA_ROLES WHERE ROLE= 'ROLE_APPROVA_ORCL'.

6. Create the ‘'ROLE_APPROVA_ORCL” role by running the following command

   CREATE ROLE ROLE_APPROVA_ORCL; If the “ROLE_APPROVA_ORCL” role is present, skip this step.

7. Grant the role to APPROVA user

   GRANT ROLE_APPROVA_ORCL TO APPROVA;

8. Run the commands in the CDM_ORCL12_ORACLE_PERM.sql file (Oracle v12) to create synonyms and grant ‘select’ permission on the required database tables.