To create a user with the required minimum privileges, proceed as follows:
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. .
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'.
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.
Run the GRANT CONNECT TO APPROVA; command. This command grants connect privileges to the created user APPROVA.
Check if the ROLE_APPROVA_PSFT role already exists in the Oracle database by running the following query:
SELECT * FROM DBA_ROLES WHERE ROLE= 'ROLE_APPROVA_PSFT
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.
Grant the role to APPROVA user.
GRANT ROLE_APPROVA_ORCL TO APPROVA
Run the commands in the CDM_PSFT_ORACLE_PERM.sql file to create synonyms and grant select permission on the required database tables.