Minimum required privileges for Oracle backend database

To extract data from PeopleSoft using the System Configuration Insight, 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_SYSCI.
    This command creates ROLE_APPROVA_PSFTHRMS_SYSCI 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_SYSCI.
    • Recreate the role by running the command CREATE ROLE APPROVA_PSFTHRMS_SYSCI.
      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_SYSCI then the role name has to be replaced in GRANT_ROLE_PSFTHRMSSYSCI.sq.

      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. From the SQL prompt run the command GRANT ROLE_APPROVA_PSFTHRMS_SYSCI TO APPROVA_HRMS. This command grants the role to the user.
  6. From SQL prompt run the following scripts.
    • CREATE_SYNONYM_PSFTHRMSSYSCI.sql
    • GRANT_ROLE_PSFTHRMSSYSCI.sql
    Note: Minimum permission scripts are shipped and can be obtained from the A 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_PSFTHRMSSYSCI.sql

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

    • @C:\GRANT_SELECT_TABLE_PSFTHRMSSYSCI.sql

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