Data Update & Data Mover Scripts
Data Mover is a PeopleSoft-delivered tool designed for loading data to a new table or copying data between environments. It can be SQL or metadata-based and operate on multiple tables. Additionally, it is database-independent, runs under SYSADM, has no granular security, is used to migrate data from database to database, and allows administrators/developers to perform any type of function on a PeopleSoft database. Its main benefit is ease of use when migrating lots of data/rows, especially in comparison to the Data Update process. Data Mover Scripts should not be executed outside of PHIRE.

Data Updates are modifications made directly into the database utilizing SQL scripts consisting of SQL updates, inserts, or deletes. The modifications being made are applied to a single environment at one time (there is no “copy” performed from one environment to another as in a data mover).

Audit Details
The goal of this audit is to ensure that there is no malicious or fraudulent activities occurring in the SFS production database. SFS Security will not be auditing non-production databases, the process, or the content within a change request. Audits will be completed by SFS Security biannually. They will be responsible for the following activities:

Tables used for audit:
DBAUSR.DBA_AUDIT_TRAIL
AUDLOG_SYSADM_LOGONS

Ignored actions:
LOGON, LOGOFF & LOGOFF BY CLEANUP, GRANT OBJECT, SELECT, EXPLAIN PLANS

Ignored database users:
SYSADM, PEOPLE, WISDMLDR

Authorized Personnel (Subject to Change)
phire_sql (SFS PHIRE SQL Account)
Nina Boss
Enjia Li
Jennifer Schienle
Patrick Zweifel

Authorized Approvers (Subject to Change)
Laura Parman
Stacey Van Wormer
Nicki Burton
Linda Diring
Jon Ahola
Scott Larson
Kirk Anderson
Sharon Schwartz

Step 1 -> Run appropriate scripts

Data Update Audit Script:
SELECT SUBSTR (UPPER (SQL_TEXT), INSTR (UPPER (SQL_TEXT), 'SFS', 1), 9)
          Jira_ID,
       TRUNC (timestamp) Date_of_Change,
       OS_USERNAME,
       USERNAME,
       TIMESTAMP,
       OWNER,
       ACTION_NAME,
       OBJ_NAME,
       SQL_TEXT
  FROM DBAUSR.DBA_AUDIT_TRAIL
 WHERE     TIMESTAMP >= '17 AUG 2015'
       AND TIMESTAMP < '1 OCT 2015'
       AND ACTION_NAME NOT IN ('LOGON',
                               'LOGOFF',
                               'GRANT OBJECT',
                               'SELECT',
                               'LOGOFF BY CLEANUP')
       AND USERNAME NOT IN ('SYSADM', 'PEOPLE', 'OPS$ORACLE')
       AND OBJ_NAME NOT IN ('GRANT_TO_Q_PSADMIN_LOG')
       AND ACTION_NAME <> 'EXPLAIN'
       AND OWNER <> 'WISDMLDR'
       AND RETURNCODE = 0
UNION
SELECT SUBSTR (UPPER (SQL_TEXT), INSTR (SQL_TEXT, '^', 2500), 0) JIRA_ID,
       TRUNC (timestamp) Date_of_Change,
       OS_USERNAME,
       USERNAME,
       TIMESTAMP,
       OWNER,
       ACTION_NAME,
       OBJ_NAME,
       SQL_TEXT
  FROM DBAUSR.DBA_AUDIT_TRAIL
 WHERE     TIMESTAMP >= '17 AUG 2015'
       AND TIMESTAMP < '1 OCT 2015'
       AND ACTION_NAME NOT IN ('LOGON',
                               'LOGOFF',
                               'GRANT OBJECT',
                               'SELECT',
                               'LOGOFF BY CLEANUP')
       AND USERNAME NOT IN ('SYSADM', 'PEOPLE', 'OPS$ORACLE')
       AND OBJ_NAME NOT IN ('GRANT_TO_Q_PSADMIN_LOG')
       AND ACTION_NAME <> 'EXPLAIN'
       AND OWNER <> 'WISDMLDR'
       AND RETURNCODE = 0;

Failure Criteria
1. An unauthorized staff member executes a change in SFS production
2. A PHIRE change request is not associated with a legitimate JIRA. The JIRA contains all of the approvals. Although JIRA # is a mandatory field, Security will ensure that the CR is correctly linked with the listed JIRA.
3. A PHIRE change request is associated with a JIRA, but the request was not approved

Non-Failure Criteria
The focus of this audit is not to scrutinize the process, but to verify only authorized are executed. Below are tasks that will be reviewed as part of the audit, but not scrutinized. With the presence of PHIRE, the probability of these missteps occurring are significantly reduced. If the security staff member conducting the audit comes across any of the following items, they will note the problematic steps but the audit will not fail. Instead, they will work with the necessary individuals to remediate the issue and use it as an educational opportunity for those individuals or groups.
1. If a SQL script doesn’t contain the JIRA number and/or PHIRE CR number
2. If a JIRA request was executed in the wrong environment
3. If a JIRA request is not closed after completion
4. If a JIRA is canceled prior to execution
5. If a JIRA request is not appropriately approved

a. Unauthorized individual approves a request
b. Approver explicitly permits code to run in JIRA instead of changing approval flag to “yes”
c. JIRA request is closed before the final sign off
d. JIRA request doesn’t have a final sign off and is still open
e. JIRA request was approved to execute after it was already executed
f. JIRA request is not re-approved after changes are made to the request

Post-Audit Tasks
Following the audit, SFS Security will perform the following:
1. Each quarter, audit details will be organized and provides to SFS leadership. Copies will be kept to provide to LAB.

a. SQL Query Utilized
b. SQL Outputs
c. Auditor Remarks
d. Quarterly Summary