Oracle backend

To create a user with the required minimum privileges, proceed as follows:

  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_PSFT role already exists in the Oracle database by running the following query:
    SELECT * FROM DBA_ROLES WHERE ROLE= 'ROLE_APPROVA_PSFT
  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_PSFT_ORACLE_PERM.sql file to create synonyms and grant select permission on the required database tables.