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_FI user who will have these privileges, proceed as:

Log into the Oracle backend database with the appropriate user name and password.
Run this query to check whether the APPROVA_FI user already exists in the Oracle backend database.
select * from dba_users where username =’APPROVA_FI';
- If the APPROVA_FI 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_FI user is not present, run the following query at the command prompt:
```
CREATE USER APPROVA_FI IDENTIFIED BY APPROVA
```
  Note: APPROVA is the preset password of the database user, APPROVA_FI. You can choose any other appropriate password
Run the command GRANT CONNECT TO APPROVA_FI. This command grants connect privileges to the created user APPROVA_FI.
Run the command CREATE ROLE ROLE_APPROVA_PSFTFISCM_SYSCI.
This command creates ROLE_APPROVA_PSFTFISCM_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 ROLE_APPROVA_PSFTFISCM_SYSCI.
- Recreate the role by running the command CREATE ROLE ROLE_APPROVA_PSFTFISCM_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_PSFTFISCM_SYSCI then the role name has to be replaced in GRANT_ROLE_PSFTFISYSCI.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.
From the SQL prompt run the command GRANT ROLE_APPROVA_PSFTFISCM_SYSCI TO APPROVA_FI. This command grants the role to the user.
From the SQL prompt, run the following scripts: CREATE_SYNONYM_PSFTFISYSCI.sql.
- This script creates a text file with commands to generate PUBLIC SYNONYMS. GRANT_ROLE_PSFTFISYSCI.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.
From the SQL prompt, run the following command to add objects (tables) that you want to access in IRC:
- @C:\CREATE_SYNONYM_TABLE_PSFTFISYSCI.sql
  This SQL file has set of commands, which creates PUBLIC SYNONYMS.
- @C:\GRANT_SELECT_TABLE_PSFTFISYSCI.sql
  This SQL file has set of commands that grant SELECT access to ROLE.