Setting Up Fine Grain Audit Policy

This section provides details for setting up fine grain audit policy for the following schemas:

• Schema for Cohort Data Mart (CDM)
• Schema for Omics Data Bank (ODB)

Oracle recommends that only the CDM and Oracle Database schemas have audit policies. There is no need to log unwarranted access to the application schema. The package used to create each policy is the DBMS_FGA package. This package lets you create specific policies for each table. Oracle recommends that the policy names match each table name that is to be audited. This allows easy identification of audit policies for each table. The audit policies must be defined for INSERT, DELETE, or UPDATE operations.

If you plan to move PHI data in Oracle Healthcare Foundation Schema, then Oracle recommends that you enable auditing for Select operations. Also, the columns that are audited Oracle be left NULL to audit all columns that are accessed. The default value for any column change must be left as is. The mode used to record information must be set to DB + extended or XML extended to log the exact SQL statement and bind variables. This is important to determine which data may be affected. Refer to the Oracle database documentation, for a detailed description of the DBMS_FGA package.

There are initialization parameters to specify where the audit logs are stored. Oracle recommends that the audit logs be stored in a separate tablespace and preferably on a different disk so as to not interfere with other database operations which may need high throughput of the disks with real data. Information about parameters for audit log storage can also be found in the Oracle Database documentation.

Setting up an audit policy to log all login operations to the database is always a good idea in production databases. Oracle recommends that you set a general audit mode to audit each login to the database as the actual database administrator password could be compromised and you may want to disable audit policies.

There is a list of tables in the Oracle Database schema that do not need any audit policy. These tables are used as staging tables to move data to the final tables. All of these tables have names suffixed with _STG.

Here is an example of the SQL to set up an audit policy:

beginDBMS_FGA.ADD_POLICY(object_schema=>'ODB',object_name=>'W_EHA_GENE',policy_name=>'W_EHA_GENE',enable=>true,statement_types=>'INSERT,UPDATE,DELETE');end;

For more information on setting up the audit policy, see the Oracle Database documentation at https://docs.oracle.com/database/121/.