DATABASE SECURITY

AND AUDTING

CHAPTER 8
Application Data Auditing

Introduction:
This chapter covers Oracle 10g fundamentals, built-in oracle auditing capabilities.
It also define and implementation of fine-grained auditing and procedure. We also learn
about data manipulation auditing and application error auditing.

Learning objectives:
 Understand the difference between the auditing architecture of DML Action
Auditing Architecture and DML changes.
 Create and implement Oracle Trigger.
 Define and implement Oracle fine-grained auditing
 Create a DML statement audit trail for Oracle
 Generate a data manipulation history
 Implement a DML statement auditing using a repository
 Understand the importance and the implementation of application errors auditing
in Oracle
 Implement oracle PL/SQL procedure authorization.

Symbols:

Important points Note

`
8.1 DML Action Auditing Architecture
In this section, we introduce Data Manipulation Language (DML) statements from two
approaches. The first approach, we create an audit trail for DML activities that are
occurring on table. The action is recorded before the statement is applied to the table.

The other approach is to register all column values either before or after the DML
statement is applied to the table, as illustrated in figure

Companies with sensitive data often use auditing architecture for DML changes. DML
changes can be performed on two levels: row level and column level. For suppose, when
an UPDATE statement that modifies only one column is appled to a table, the DML
auditing mechanism can record all values for every column in the table. This is called
row-level auditing. If we are interested in recording the values of the modified columns,
we use column-level auditing. Oracle and other database management system refer to this
as fine-grained auditing.

Oracle Triggers
The main database objects are database triggers. This section discusses the
purpose and syntax of trigger. A formal definition of a trigger, a trigger is a stored
PL/SQL procedure that is executed automatically whenever a DML operation occurs or a
specific database event occurs. Oralce has six DML events, also know as trigger timings
for INSERT, UPDATE, and DELETE.

Triggers are mainly used for the following purpose:

Performing audits
Preventing invalid data from being inserted into the table
Implementing business rule
Generating value for columns

We need to learn the grammer for creating DML statement triggers. We can use the
CREATE TRIGGER statement on a table by following syntax present in the following
example.

Syntax:
CREATE [or REPLACE ] TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF }
{ INSERT [OR] | UPDATE [ OR] | DELETE }
{ OF col_name]
ON table_name
[FOR EACH ROW]
[REFERENCE {OLD [as] old | NEW [AS] new | PARENT [as} parent}]
WHEN (condition)
{ pl/sql_block | call_procedure_statement };

BEFORE: This indicates that the trigger executes before DML statement is applied on the
affected rows.
AFTER: This indicates that the trigger executes after DML statement is applied on the
affected rows.
INSTEAD OF: This is only applicable to DML statements. It indicates that the trigger
should be executed, instead of DML statement
OF: This specifies the columns that the trigger to fire when the column mentioned in this
OF clause is affected. If we omit the OF clause, the trigger fires when any column in the
table is affected by the statement.
ON: this specifies the table that the trigger affects.
FOR EACH ROW: This indicates that the trigger fires for each row affected by the DML
statement.
REFERNCING: when a trigger contains the FOR EACH ROW clause, oracle offers two
pseudo rows, NEW and OLD. These virtual rows contain the new values of each column
in the row and the old value for each column in the row.
WHEN: this specifies the criterion or condition that the trigger must meet to fire.

Example 1
CREATE OR REPLACE TRIGGER TRG_EMPLOYEES_BUR
BEFORE UPDATE ON APP_TBL2
FOR EACH ROW
DELARE

V_OPERATION VARCHAR2(20);

BEGIN
---- comment lines
 inserting , updating and deleting
 variables that are set to TRUE automatically
 by oracle, based on the action DML operation
 That fired the trigger. If an INSERT fires
 The trigger, then INSERTING is set TRUE and
 So forth for the UPDATE and DELETE.

IF INSERTING THEN
V_OPERATION := ‘INSERT’ ;
ELSEIF UPDATING THEN
V_OPERATION := ‘UPDATE’;
ELSE
V_OPERATION := ‘DELETE’;
….

END;
/

We can view all trigger created on a table by using the USER_TRIGGER data dictionary
view. The body of the trigger is contained in the TRIGGER_BODY column

Syntax:
SQL:> DESC USER_TRIGGER

Fine-grained Auditing (FGA) with Oracle

Oracle is a complete database system, especially when it comes to auditing.
Oracle provides column-level auditing, known as fine-auditing, which keeps an audit trail
of all DML activities. FGA is simply an internal mechanism that allows the database
administrator to set up auditing policies on tables. It allows administrators to generate
audit trail of DML activities to operating system files or database tables. FGA is capable
of auditing columns or tables using Oracle PL/SQL supplied package called
DBMS_FGA. The features not only record an audit trail of activities, it can also alert
administrators or perform an action when an auditable event occurs.
Suppose we have business requirements that specify the following
Generate an audit trail for all SELECT activities on the CUSTOMERS table.
Generate an audit trail when CREDIT_LIMIT is set to a value above $10,000.00
Generate an audit trail when a CUSTOMER row is deleted.
For practice, implement these policies by performing the following steps:
1. Work through a user other than SYSTEM or SYS that has privileges to CREATE
TABLE and EXECUTE on DBMS_FGA. Create a CUSTOMERS table.
2. populate the customer table with two rows
For script see chap8_sql folder in that chap8_fga_cust.sql;

3. Now we need to add the auditing policies as specified previously. We need to use the
ADD_POLICY procedure found in DBMS_FGA. The procedure requires the following
paramerters:

OBJECT_SCHEMA: name of the user that the desired object is to audit.

OBJECT_NAME: name of the object that we want to audit
POLICY_NAME: Unique name of the audit policy
AUDIT_CONDITION: Condition on the row used as audit criteria. NULL means no
condition
AUDIT_COLUMN: Name of columns that we would like to audit. NULL means no all
columns
HANDLER_SCHEMA: name of the user name that owns the procedure that is used as
the event handler. NULL indicate current schema.
HANDLER_MODULE: name of the procedure that fire when the audit is exercised.
ENABLE: indication of whether the audit policy is enable or not. The default value is
TRUE, which means enabled.
STATEMENT_TYPES: specification of what DML statement we would like to audit.
Allowed are INSERT, UPDATE, DELETE, or SELECT, and the default value is
SELECT.
For script see chap8_sql folder in that chap8_fga_policy.sql;
4.we can use USER_AUDIT_POCLIES to view all auditing policies that we own.
SQL> SELECT OBJECT_NAME,
POLICY_NAME,
POLICY_TEXT,
SEL, INS, UPD, DEL
FROM USER_AUDIT_POLICIES;

5. As SYS, we must turn on “auditing” on the DML statements, using the audit
SQL> AUDIT SELECT, INSERT, UPDATE, DELETE on SYS.AUD$ BY
ACCESS;

6. Now we need to perform some DML action on the customer table, like insert,
delete, update on CUSTOMERS table.
For script see chap8_sql folder in that chap8_fga_select.sql;

7. Now we can check the contents of DBA_FGA_AUDIT_TRAIL to view the audit

trial of the DML activities performed on CUSTOMERS table.
SQL> SELECT OBJECT_NAME, POLICY_NAME, SQL_TEXT,
STATEMENT_TYPE
FROM DBA_FGA_AUDIT_TRAIL

Audit Trail
DML Action Auditing with Oracle
Oracle 10g provides functionality to implement auditing schemes from basic to
advanced. Oracle uses a combination of database objects, such as triggers, tables, and
stored procedures. The purpose of auditing DML statements is to record the data
change occurring on the table, including the name of the person who mad the data
change, the date, and the time of the change. In this model the before or after value of
the columns are not recorded. Figure represents the data model for this. On the left of
the diagram is the DEPARTMENTS table that will contain application data, whereas
the table on the right is the auditing table APP_AUDIT_DATA that will contain audit
trail of all data change operations on the DEPARTMENTS table.
APP_AUDIT_DATA can be used for other tables that need to be audited.
To implement this model, follow these steps
1. Using a user other than SYSTEM or SYS with privileges to create TABLES,
SEQUENCE, and TRIGGER.
2. use DBSEC schema for this creating.
3. create the DEPARTMENTS table
4. create the auditing table APP_AUDIT_DATA
5. create a sequence object that will be used for the AUDIT_DATA_ID colum
APP_AUDIT_DATA table. The sequence will generate unique values.
6. create the trigger on the DEPARTMENTS table that will record the DML
operations that occur on ti.
7. Now we are ready to see what is recorded to insert, modify, or update on the
DEPARTMENTS table.
8. we will insert some rows
9. now update any row
10. now delete any row
11. To view the contents of the auditing table APP_AUDIT_DATA.

For scrip see chap8_sql folder in that chap8_audit_trial.sql;

Data Manipulation History

Every financial company implements some form of data manipulation history.
This is because almost all data in the financial world has monetary impact and is
therefore sensitive.

A data manipulation history provides a complete trail of all changes that are applied to
data. The history contains either the before or the after value of the data, as well as a
record of the person who made the change and date and time it occurred. The benefit of
such an audit is to reconcile and verify current values.

History Auditing Model implementation using oracle

History data auditing is simple to implement. Its main components are the TRIGGER and
TABLE objects. Consider the following scenario. A small retail company decides to
develop an historical audit solution to keep track of its customer’s phone numbers,
addresses, and the name of their sales representative. Figure is the customer table. And
the last four columns are used to control columns, containing auditing information.
Notice that it is an identity copy of the CUSTOMERS table, except that two columns
have been added: HST_INS_DTTM and HST_OPR_TYPE. These representatively, the
data and time of the copy of the customer record was captured, and the type of operation
applied to that record.
Now the following steps to implement historical auditing for the CUSTOMERS
table as shown in figure
1. To create the CUSTOMER table, use any database user account other than
SYSTEM or SYS that has privileges to create table and triggers.
2. Now we logon as DBSEC schema, for creation of CUSTOMERS table
3. we will be creating the history table CUSTOMERS_HISTORY for
CUSTOMERS, as shown in the data model in the previous example. This table
will contain all changes to the CUSTOMERS table before an UPDATE, INSERT,
or DELETE occurs. Note there is no primary key on this table because the same
row in CUSTOMERS is modified several times.
4. Create the trigger on the CUSTOMERS table to track changes and record all the
values of the columns before they were changed.
5. Now insert rows into the CUSTOMERS table.
6. Retrieve all rows from the CUSTOMERS and CUSTOMER_HISTORY table to
verify that the rows were added to the CUSTOMERS table and that the trigger is
populating the CUSTOMERS_HISTORY table
7. Now we update the CUSTOMERS table on one row
8. Examine the value of the row in the CUSTOMERS table.
9. Now DELETE the same customer which we had updated and verity it was
deleted. Is that row recorded in the history table?

For scrip see chap8_sql folder in that chap8_data_history.sql;

DML Auditing using Repository with Oracle (simple 1)

This model was presented in as simple auditing model 1. The main purpose of this
model is to flag users, table, or columns for auditing. This model serves as a mechanism
to audit by changing the registry entry for the user in the repository, without changing
code. For example, suppose TOM was suspected of suspicious activities. All need to do
is add an entry in the repository for that user and automatically any DML auditing
mechanism of DML statements. The difference is in the administrative skill level needed
to conduct an audit. In this DML model, an application administrator can manage the
audit because a user interface is built on top of the repository. Figure illustrates the data
model of this DML auditing structure.
We need to know the rules for the flagging hierarchy. The rules state that if the user is
registered, it does not matter where the table is registered or not. Actions by the user are
audited. This model does not record before or after column values. It only registers the
type of DML operation occurring on the table.