Specific privileges for extraction for Oracle V 12.2

Working with the Configuration Insight for Oracle, requires 'Connect' and 'Select' privileges on the Oracle database.

To create an 'Approva' user who will have these privileges, proceed as follows:

  1. Login to Oracle database with appropriate user name and password (for example, User Name as 'system' and Password as 'manager').
  2. Check whether APPROVA user is already in Oracle database or not 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, you need to choose different user name and change this script with 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, CREATESYNONYM TO APPROVA;" command.
  4. ALTER USER APPROVA ENABLE EDITIONS;
  5. Run the "DROP ROLE ROLE_APPROVA_ORACLESYSCI;" command. This command removes ROLE_APPROVA_ORACLESYSCI role from Oracle database. If this role is not in the Oracle database the command fails.
  6. Run "CREATE ROLE ROLE_APPROVA_ORACLESYSCI;" command. This command creates ROLE_APPROVA_ORACLESYSCI role in Oracle database. If this role is already in Oracle database this command fails.
    Note:  You do not need to repeat steps 1 to 5 while adding / modifying new table access roles for this user.
  7. From SQL prompt run command "GRANT ROLE_APPROVA_ORACLESYSCI TO APPROVA;"
  8. From SQL prompt, run the scripts and add objects that you want to access in BizRights.
  9. From SQL prompt run the following command:

    "@C:\CREATE_SYNONYM_TABLE_ORACLESYSCI.SQL". This sql file has set of commands, which creates PRIVATE SYNONYMS.

    Note:  If there is more than 1 owner in "C:\CREATE_SYNONYM_TABLE_ORACLESYSCI.SQL" file then the duplicate entry other than owner as 'APPS' OR 'APPLSYS' has to be deleted.

  10. At SQL prompt run command "@C:\GRANT_SELECT_TABLE_ORACLESYSCI.SQL". This sql file has set of commands that grant SELECT access to ROLE.