Minimum required privileges for Oracle backend database

To extract data from the Authorizations Insight for PeopleSoft HRMS, you require a user who will be assigned Connect and Select privileges on the Oracle backend database.

To create an APPROVA_HR user with these privileges:

  1. Log into Oracle backend database with the appropriate user name and password.
  2. Run the following query to check whether the APPROVA_HR user already exists in the Oracle backend database: 
    Select * from dba_users where username =’APPROVA_HR'
    • If the APPROVA_HR 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_HR
      user is not present, run the following query at command prompt: 
      CREATE USER APPROVA_HR IDENTIFIED BY APPROVA
    Note: APPROVA is the preset password of the database user, APPROVA_FI. You can select any other appropriate password.
  3. Run the command GRANT CONNECT TO APPROVA_HR. This command grants connect privileges to the created user APPROVA_HR.
  4. Run the command CREATE ROLE ROLE_APPROVA_PSFTHRAI. This command creates the role ROLE_APPROVA_PSFTHRAI in the Oracle backend database.
    If this role is already there in Oracle database, this command will fail. In this case:
    • Delete the role by running the command DROP ROLE ROLE_APPROVA_PSFTHRAI.
    • Recreate the role by running the command CREATE ROLE ROLE_APPROVA_PSFTHRAI.
    Note: Before deleting a role, ensure that the role is not assigned to some other user.

    If the user and role already exist and you want to just modify the role, that is, add or delete access to tables, begin directly at step 5.

  5. Run the command: 
    GRANT ROLE_APPROVA_PSFTHRAI TO APPROVA_HR

    This command grants the role to the user.

  6. Run the following scripts:
    • CREATE_SYNONYM_PSFTHRAI.sql. This script creates a text file with commands to generate PUBLIC SYNONYMS.
    • GRANT_ROLE_PSFTHRAI.sql. This script creates a text file with commands to grant SELECT access to ROLE.
    Note: Minimum permission scripts are shipped and can be obtained from the IRC Adapters CD under PeopleSoft\MinimumPermissions folder.
  7. From SQL prompt, run the following commands to add objects (tables) that you want to access in IRC:
    • @C:\CREATE_SYNONYM_TABLE_PSFTHRAI.sql: This sql file has set of commands, which creates PUBLIC SYNONYMS.
    • @C:\GRANT_SELECT_TABLE_PSFTHRAI.sql“. This sql file has set of commands that grant SELECT access to ROLE.