Minimum required privileges for Oracle backend database

To extract data, you require a user who will be assigned Connect and Select privileges on the Oracle backend database.

To create an APPROVA_HRMS user who will have these privileges, proceed as:

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

      If a role has a name other than ROLE_APPROVA_PSFTHRMS_P2PCI then the role name has to be replaced in GRANT_ROLE_PSFTHRMSP2PPCI.sql.

      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 APPROVA_PSFTHRMS_P2PCI TO APPROVA_HRMS. This command grants the role to the user.
  6. From SQL prompt run the following scripts.
    • PSFTHRMSP2PPCI_Grant.sql
    • PSFTHRMSP2PPCI_Synonym.sql

    These scripts generate the scripts for creating a role and granting select access to the role, as mentioned in point 7.

    Note: After you run the commands, the SQL file displays a statement showing the number of rows selected. Remove this statement manually for proper functioning of the commands.

    Minimum permission scripts are shipped and can be obtained from the IRC Adapters CD under PeopleSoft\MinimumPermissions folder.

  7. From the SQL prompt, run the following command to add objects (tables) that you want to access in IRC:
    • @C:\CREATE_SYNONYM_TABLE_PSFTHRMSP2PPCI.sql

      This SQL file has set of commands, which creates PUBLIC SYNONYMS.

    • @C:\GRANT_SELECT_TABLE_PSFTHRMSP2PPCI.sql

      This SQL file has set of commands that grant SELECT access to ROLE.