# Oracle Database Account

Sentinel server requires various PeopleSoft tables for Security, Reporting and Analytics .&#x20;

Each PeopleSoft environment should provide Oracle Database account for Sentinel. The easiest way is provide `SYSADM` account for any development environments.&#x20;

Production environment should have dedicated sentinel account with fine grained access.

### Production Account&#x20;

&#x20;For example, account for Sentinel will have name - `snt_user`

Create it user with:

```sql
CREATE USER snt_user identified by "YourStrongPassword";
```

&#x20;Grant ability to connect to database:

```sql
GRANT CREATE SESSION to snt_user;
GRANT ALTER SESSION to snt_user;
```

### Read Access

Login as `PSADMIN` user and grant read access to all tables and views:

<pre class="language-sql"><code class="lang-sql"><strong>GRANT SELECT_CATALOG_ROLE to snt_user;
</strong>
GRANT SELECT ON SYSADM.PSTREENODE TO snt_user;
GRANT SELECT ON SYSADM.PS_ROLEXLATOPR TO snt_user;
GRANT SELECT ON SYSADM.PSVERSION TO snt_user;
GRANT SELECT ON SYSADM.PSOPERATION TO snt_user;
GRANT SELECT ON SYSADM.PSPRSMHPASGPGLT TO snt_user;
GRANT SELECT ON SYSADM.PSRECDEFN TO snt_user;
GRANT SELECT ON SYSADM.PSPGEACCESSDESC TO snt_user;
GRANT SELECT ON SYSADM.PS_EOCC_HDR_EXCROL TO snt_user;
GRANT SELECT ON SYSADM.PSPNLGROUP TO snt_user;
GRANT SELECT ON SYSADM.PSAUTHSIGNON TO snt_user;
GRANT SELECT ON SYSADM.PSAUTHBUSCOMP TO snt_user;
GRANT SELECT ON SYSADM.PSMENUITEM TO snt_user;
GRANT SELECT ON SYSADM.PSUSEREMAIL TO snt_user;
GRANT SELECT ON SYSADM.PSACCESSPROFILE TO snt_user;
GRANT SELECT ON SYSADM.PS_SCRTY_QUERY TO snt_user;
GRANT SELECT ON SYSADM.PSROLECLASS TO snt_user;
GRANT SELECT ON SYSADM.PSPNLFIELD TO snt_user;
GRANT SELECT ON SYSADM.PSPRSMDEFN TO snt_user;
GRANT SELECT ON SYSADM.PS_PRCSDEFNPNL TO snt_user;
GRANT SELECT ON SYSADM.PS_PRCSJOBGRP TO snt_user;
GRANT SELECT ON SYSADM.PSDBFIELD TO snt_user;
GRANT SELECT ON SYSADM.PSQRYACCLSTRECS TO snt_user;
GRANT SELECT ON SYSADM.PSRECFIELDDB TO snt_user;
GRANT SELECT ON SYSADM.PSRECFIELD TO snt_user;
GRANT SELECT ON SYSADM.PSOPRALIAS TO snt_user;
GRANT SELECT ON SYSADM.PSPNLGRPDEFN TO snt_user;
GRANT SELECT ON SYSADM.PSOPRDEFN TO snt_user;
GRANT SELECT ON SYSADM.PS_EOCC_CONFIG_HDR TO snt_user;
GRANT SELECT ON SYSADM.PSBCDEFN TO snt_user;
GRANT SELECT ON SYSADM.PS_EOCC_EX_USR_LST TO snt_user;
GRANT SELECT ON SYSADM.PSPRSMSYSATTRVL TO snt_user;
GRANT SELECT ON SYSADM.PS_INSTALLATION_HR TO snt_user;
GRANT SELECT ON SYSADM.PSSTATUS TO snt_user;
GRANT SELECT ON SYSADM.PSAUTHWS TO snt_user;
GRANT SELECT ON SYSADM.PS_PRCSDEFNGRP TO snt_user;
GRANT SELECT ON SYSADM.PSROLEDEFN TO snt_user;
GRANT SELECT ON SYSADM.PSQRYDEFN TO snt_user;
GRANT SELECT ON SYSADM.PSROLEUSER TO snt_user;
GRANT SELECT ON SYSADM.PS_EOCC_USER_LST TO snt_user;
GRANT SELECT ON SYSADM.PSPRSMPERM TO snt_user;
GRANT SELECT ON SYSADM.PSAUTHITEM TO snt_user;
GRANT SELECT ON SYSADM.PSCLASSDEFN TO snt_user;
GRANT SELECT ON SYSADM.PSRELEASE TO snt_user;
GRANT SELECT ON SYSADM.PSPTSCRTY_ADS_P TO snt_user;
GRANT SELECT ON SYSADM.PS_MAINTENANCE_LOG TO snt_user;
GRANT SELECT ON SYSADM.PSPCMPROG TO snt_user;
GRANT SELECT ON SYSADM.PSPTPN_MSG_TBL TO snt_user;
GRANT SELECT ON SYSADM.PSMENUDEFN TO snt_user;
GRANT SELECT ON SYSADM.PS_SCRTY_ACC_GRP TO snt_user;
GRANT SELECT ON SYSADM.PS_JOB TO snt_user;
GRANT SELECT ON SYSADM.PSAUTHPRCS TO snt_user;
GRANT SELECT ON SYSADM.PSQRYFIELD TO snt_user;
GRANT SELECT ON SYSADM.PSPRCSPRFL TO snt_user;
GRANT SELECT ON SYSADM.PSUSERATTR TO snt_user;
GRANT SELECT ON SYSADM.PS_ARCH_SECURITY TO snt_user;
GRANT SELECT ON SYSADM.PSROLECANGRANT TO snt_user;
</code></pre>

Tables specific to `HRMS` systems

```sql
GRANT SELECT ON SYSADM.PS_SCRTY_TBL_DEPT TO snt_user;
```

### Write Access

Sentinel can simplify access changes for Users, Roles and Permission Lists. To be able to provide such functionality write access is required to following tables:

```sql
GRANT SELECT, INSERT, UPDATE, DELETE ON PS_ARCH_SECURITY TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON PS_ROLEXLATOPR   TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON PS_SCRTY_QUERY   TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON PSAUTHBUSCOMP    TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON PSAUTHITEM       TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON PSAUTHPRCS       TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON PSAUTHSIGNON     TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON PSAUTHWS         TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON PSCLASSDEFN      TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON PSOPRALIAS       TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON PSOPRDEFN        TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON PSPRCSPRFL       TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON PSPRSMPERM       TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON PSPTSCRTY_ADS_P  TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON PSROLECLASS      TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON PSROLEDEFN       TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON PSROLEUSER       TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON PSUSERATTR       TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON PSUSEREMAIL      TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON PSVERSION        TO snt_user ; 
```

Please note, these tables are subject to change based on current/feature Sentinel functionality. Check environment build log for any access errors.&#x20;

Tables specific to `HRMS` systems

```sql
GRANT SELECT, INSERT, UPDATE, DELETE ON PS_HCSCM_AUTHROLE  TO snt_user ; 
GRANT SELECT, INSERT, UPDATE, DELETE ON PS_INSTALLATION_HR TO snt_user ; 
```

**SACR** security related tables for `Campus Solutions` only

```sql
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_OPR_COMM_SPDKEY  TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_OPR_DEF_TBL_CS   TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_OPR_GRP_3C_TBL.  TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_OPR_SPDKEY_FUNC  TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SAA_SCRTY_AARPT  TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SAD_TEST_SCTY    TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SCC_GE_SCRTY     TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SCC_SCRTY_RLCAT  TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SCC_SL_TRN_SCTY  TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SCC_STY_TBL_CMP  TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SCCPU_SRTY_TBL   TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SCRTY_ADM_ACTN   TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SCRTY_APPL_CTR   TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SCRTY_PROG_ACTN  TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SCRTY_RECR_CTR   TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SCRTY_TBL_ACAD   TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SCRTY_TBL_CAR    TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SCRTY_TBL_INST   TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SCRTY_TBL_MLSTN  TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SCRTY_TBL_PLAN   TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SCRTY_TBL_PROG   TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SCRTY_TBL_SRVC   TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SCRTY_TBL_STGP   TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SCRTY_TSCRPT     TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SEC_CC_OPR       TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SEC_CSHOFF_OPR   TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SEC_ISET_OPR     TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SEC_ITEM_OPDATA  TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SEC_ITEM_SU_OPR  TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SEC_ORIGIN_OPR   TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SEC_SETID_OPR    TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SEC_UNITSF_OPR   TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SEV_PRG_SP_SCTY  TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SEV_SCHLCD_SCTY  TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SSR_AIR_OPRSCTY  TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SSR_ANID_SCRTY   TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SSR_APT_ACT_SCR  TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SSR_SCRTY_EXAM   TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SSR_SCRTY_GRSTA  TO snt_user ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  PS_SSR_SCRTY_TSRPT  TO snt_user ;
```

### Job data

Sentinel uses `job source` query which gathers main job information for each employee. Settings located in Admin / Settings / Job Source in your sentinel installation. By default this query fetching data from `HRMS` environment. Based on organization needs, query can be customised to use different tables and any type of environment.&#x20;

Access to these tables should be granted for default query in `HRMS` environment.&#x20;

```sql
GRANT SELECT ON PS_CURRENT_JOB TO snt_user ;
GRANT SELECT ON PS_BUSUNIT_HR_VW TO snt_user ;
GRANT SELECT ON PS_REG_REGION_TBL TO snt_user ;
GRANT SELECT ON PS_NAMES TO snt_user ;
GRANT SELECT ON PS_DEPT_TBL TO snt_user ;
GRANT SELECT ON PS_JOBCODE_TBL TO snt_user ;
GRANT SELECT ON PS_POSITION_DATA TO snt_user ;
GRANT SELECT ON PS_COMPANY_TBL TO snt_user ;
GRANT SELECT ON PS_LOCATION_VW TO snt_user ;
GRANT SELECT ON PS_EMAIL_ADDRESSES TO snt_user ;

```



### Verify Access

To verify that the `snt_user` has been granted permission for the tables by querying the "DBA\_TAB\_PRIVS" data dictionary view. Use the following SQL query to display the permissions granted to `snt_user` for the tables:

```sql
SELECT owner, table_name, grantee, privilege
FROM dba_tab_privs
WHERE owner = 'SYSADM' AND grantee = 'snt_user';
```

