Configuring insight specific privileges

This section covers the following topics:

  • Minimum privileges scripts for Oracle 12
  • Minimum privileges scripts for 8i database

To extract data from Oracle using Configuration Insights, you require a user who is assigned 'Connect' and 'Select' privileges on the Oracle database.

Create an 'APPROVA' user with 'Connect' and 'Select' privileges. The example below is for System Configuration Insight. Follow the same steps for all Configuration Insights for Oracle:

  1. Log into the Oracle database with the appropriate user name and password. .
  2. Check whether the APPROVA user already exists in the Oracle database by running the following query: SELECT * FROM DBA_USERS WHERE USERNAME='APPROVA';
    • If the APPROVA user is already present and being used by another application, select 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 grants connect privileges to the user 'APPROVA'
  4. Run the command "CREATE ROLE APPROVA_ORACLESYSCI;" This command creates the APPROVA_ORACLESYSCI role in the Oracle database assuming that such a role does not already exist. If this role is already in the Oracle database this command fails. In this case:
    • If the APPROVA user is already present and being used by another application, select 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.
    • If the APPROVA user is already present and being used by another application, select 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.
    • Delete the role by running the command "DROP ROLE APPROVA_ORACLESYSCI".Before deleting the role, ensure that it is not assigned to any other user.
    • Recreate the role by running the command "CREATE ROLE APPROVA_ORACLESYSCI;"

      The following are the role names for Configuration Insights:

      Financial Close APPROVA_ORACLEFCLPCI
      Order to Cash APPROVA_ORACLEO2CPCI
      Procure-to Pay APPROVA_ORACLEP2PPCI
      Payroll APPROVA_ORACLEPAYPCI
      System Configuration APPROVA_ORACLESYSCI
      Note:  If the user and role already exists and you want to just modify the role, that is, add or delete access to tables, begin directly at step 5.
  5. From the SQL prompt, run the following scripts:
    • CREATESYNONYM-SYS.sql. This script creates a text file with commands to generate PUBLIC SYNONYMS.
    • GRANTROLE-SYS.sql. 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 doesn't work while creating public synonyms.

      In this case, modify script 1 as follows:

      • SELECT 'CREATE 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 Oracle\Minimum Permissions folder.
  6. From the SQL prompt, run the following queries to add objects (tables) that you want to access in IRC:
    • "@ C:\CREATE_SYS_SYNONYM_TABLE.SQL"
      Note:  If there is more than one owner in the file C:\CREATE_SYNONYM_TABLE_CI.SQL, 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_SYS_TABLE.SQL".
  7. From the SQL prompt, run command "GRANT APPROVA_ORACLESYSCI TO APPROVA;"
  8. Use the following query to check whether a role has access to the given table: SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE LIKE '%APPROVA%';