DATABASE SECURITY



DATABASE SECURITY

AND AUDTING

[pic]

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:

[pic] Important points [pic]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.

[pic]

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

[pic]

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.

[pic]

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 };

[pic]

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.

[pic]

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.

[pic]

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.

[pic]

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.

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download