Creating user for Oracle version 12.1 and earlier with minimum privileges

To extract data, you need to create a user who is assigned ‘Connect’ and ‘Select’ privileges on the Oracle database.

To create a IRC user who will have these privileges, proceed as:

  1. Log on to the Oracle database with the appropriate user name and password.
  2. Check whether the APPROVA user is already present in the Oracle database, by running this query: SELECT * FROM DBA_USERS WHERE USERNAME='APPROVA';
    • If the APPROVA user is already present in the Oracle database and is being used by another application, you need to choose a different user name and change this script to reflect that user name.
    • If the APPROVA user is not present, run the “CREATE USER APPROVA IDENTIFIED BY APPROVA“; command from the SQL prompt.
    Note: ‘APPROVA’ is the preset password of the database user, ‘APPROVA’. You can choose any other appropriate password.
  3. Run the “GRANT CONNECT TO APPROVA”; command. This command connects the user to the database.
  4. Run the "DROP ROLE ROLE_APPROVA_ORCLAI”; command. This command removes the ROLE_APPROVA_ORCLAI role from the Oracle database. If this role is not present in the Oracle database, the command fails.
  5. Run the "CREATE ROLE ROLE_APPROVA_ORCLAI”; command. This command creates the ROLE_APPROVA_ORCLAI role in the Oracle database. If this role is already present in the Oracle database, this command fails.
    Note:  You do not need to repeat steps 1 to 5 while adding or modifying new table access roles for this user
  6. From the SQL prompt, run the command,” GRANT ROLE_APPROVA_ORCLAI TO APPROVA.”
  7. From the SQL prompt, run the following scripts for your specific ERP version:
    • ORCLAI_CREATE_SYNONYM script. This script creates a text file with commands to generate PUBLIC SYNONYMS.
    • ORCLAI_GRANT_SELECT script. This script creates a text file with commands to grant SELECT access to ROLE.
      Note: If the Oracle backend database is 8i and ERP Version as 11.5.9 or less, then the ‘REPLACE’ command does not work while creating public synonyms.

      In this case, modify as :

      • SELECT 'CREATE OR REPLACE PUBLIC SYNONYM ' || TABLE_NAME || ' FOR ' || OWNER|| '.' ||TABLE_NAME|| ';'
      • After executing Script1 if there are any errors stating that the “SYNONYM Exists”, then just execute Script 2.
      • Minimum permission scripts are shipped and can be obtained from the IRC Adapters folder under the Oracle\MinimumPermissions folder.
  8. From the SQL prompt run the following queries to add objects (tables) that you want to access in IRC:
    • "@C: \CREATE_SYNONYM_TABLE_ORCLAI.SQL". This sql file has a set of commands that create PUBLIC SYNONYMS.
      Note:  If there is more than one owner in the file C:\CREATE_SYNONYM_TABLE_ORCLAI.SQL then all the duplicate entries (that is, other than the owner for the table) must be deleted. The table owner can be found by executing the query SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME = ‘table name’>.
    • "@C:\GRANT_SELECT_TABLE_ORCLAI.SQL". This sql file has a set of commands that grant SELECT access to ROLE.