Creating user with minimum privileges for version 12.2

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 'm.
  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_ORCLAI;" command. This command removes ROLE_APPROVA_ORCLAI role from Oracle database. If this role is not in the Oracle database the command fails.
  6. Run "CREATE ROLE ROLE_APPROVA_ORCLAI;" command. This command creates ROLE_APPROVA_ORCLAI 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_ORCLAI TO APPROVA;"
  8. From SQL prompt, run these scripts :
    • ORCLAI_CREATE_SYNONYM_122.sql script - This script creates a text file with commands to generate PRIVATE SYNONYMS.
    • ORCLAI_GRANT_SELECT_122.sql script - This script creates a text file with commands to grant SELECT access to ROLE.
  9. From SQL prompt run the following command:

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

    Note:  If there is more than 1 owner in "C:\CREATE_SYNONYM_TABLE_ORCLAI.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_ORCLAI.SQL". This sql file has set of commands that grant SELECT access to ROLE.