Lecture Database security and auditing - Protecting data integrity and accessibility

Database Security and Auditing: Protecting Data Integrity and Accessibility

Chapter 8 Application Data Auditing

Objectives

• Understand the difference between the auditing architecture of DML Action Auditing Architecture and DML changes

• Create and implement Oracle triggers • Create and implement SQL Server triggers • Define and implement Oracle fine-grained

auditing

Database Security and Auditing 2

Objectives (continued)

• Create a DML statement audit trail for Oracle

and SQL Server

• Generate a data manipulation history

•

Implement a DML statement auditing using a repository

Database Security and Auditing 3

Objectives (continued)

• Understand the importance and the

•

implementation of application errors auditing in Oracle Implement Oracle PL/SQL procedure authorization

Database Security and Auditing 4

DML Action Auditing Architecture

• Data Manipulation Language (DML): companies

use auditing architecture for DML changes • DML changes can be performed on two levels:

– Row level – Column level

• Fine-grained auditing (FGA)

Database Security and Auditing 5

DML Action Auditing Architecture (continued)

Database Security and Auditing 6

DML Action Auditing Architecture (continued)

Database Security and Auditing 7

Oracle Triggers

• Stored PL/SQL procedure executed whenever:

– DML operation occurs – Specific database event occurs

• Six DML events (trigger timings): INSERT,

UPDATE, and DELETE

• Purposes:

– Audits, controlling invalid data – Implementing business rules, generating values

Database Security and Auditing 8

Oracle Triggers (continued)

Database Security and Auditing 9

Oracle Triggers (continued)

• CREATE TRIGGER • Executed in a specific order:

– STATEMENT LEVEL triggers before COLUMN

LEVEL triggers

– BEFORE triggers before AFTER triggers • USER_TRIGGERS data dictionary view: all

triggers created on a table

• A table can have unlimited triggers: do not

overuse them

Database Security and Auditing 10

Oracle Triggers (continued)

Database Security and Auditing 11

SQL Server Triggers

• CREATE TRIGGER DDL statement: creates a

trigger

• Trigger condition:

– Prevents a trigger from firing – UPDATE() and COLUMNS_UPDATE() functions

• Logical tables:

– DELETED contains original data – INSERTED contains new data

Database Security and Auditing 12

SQL Server Triggers (continued)

• Restrictions—Transact-SQL statements not

allowed: – ALTER and CREATE DATABASE – DISK INIT and DISK RESIZE – DROP DATABASE and LOAD DATABASE – LOAD LOG – RECONFIGURE – RESTORE DATABASE – RESTORE LOG

Database Security and Auditing 13

Implementation of an Historical Model with SQL Server

• Create a history table:

– Same structure as original table – HISTORY_ID column

• Create a trigger: inserts original row into the

HISTORY table

Database Security and Auditing 14

Fine-grained Auditing (FGA) with Oracle

• Oracle provides column-level auditing: Oracle

PL/SQL-supplied package DBMS_FGA

• DBMS_FGA procedures:

– ADD_POLICY – DISABLE_POLICY – DROP_POLICY – ENABLE_POLICY

Database Security and Auditing 15

Fine-grained Auditing (FGA) with Oracle (continued)

• ADD_POLICY parameters:

– OBJECT_SCHEMA – OBJECT_NAME – POLICY_NAME – AUDIT_CONDITION – AUDIT_COLUMN – HANDLER_SCHEMA

Database Security and Auditing 16

Fine-grained Auditing (FGA) with Oracle (continued)

• ADD_POLICY parameters (continued):

– HANDLER_MODULE – ENABLE – STATEMENT_TYPES

• DBA_FGA_AUDIT_TRAIL: view the audit trail

of the DML activities

Database Security and Auditing 17

DML Action Auditing with Oracle

• Record data changes on the table:

– Name of the person making the change – Date of the change – Time of the change

• Before or after value of the columns are not

recorded

Database Security and Auditing 18

DML Action Auditing with Oracle (continued)

Database Security and Auditing 19

DML Action Auditing with Oracle (continued)

• Steps:

– Use any user other than SYSTEM or SYS; with privileges to create tables, sequences, and triggers

– Create the auditing table – Create a sequence object – Create the trigger that will record DML

operations

– Test your implementation

Database Security and Auditing 20

History Auditing Model Implementation Using Oracle

• Historical data auditing is simple to implement; main components are TRIGGER objects and TABLE objects • Keeps record of:

– Date and time the copy of the record was

captured

– Type of operation applied to the record

Database Security and Auditing 21

History Auditing Model Implementation Using Oracle (continued)

• Steps:

– Use any user other than SYSTEM or SYS; with privileges to create tables, sequences, and triggers

– Create history table – Create the trigger to track changes and record

all the values of the columns

– Test your implementation

Database Security and Auditing 22

DML Auditing Using Repository with Oracle (Simple 1)

• Simple Auditing Model 1 • Flag users, tables, or columns for auditing • Requires less database administrative skills:

– Application administrators can do it – User interface is built in top of the repository

• Auditing flags are flexible • Does not record before or after column values;

only registers type of DML operations

Database Security and Auditing 23

DML Auditing Using Repository with Oracle (Simple 1) (continued)

Database Security and Auditing 24

DML Auditing Using Repository with Oracle (Simple 1) (continued)

• Steps:

– Use any user other than SYSTEM or SYS – Create triggers – Create sequence object – Build tables to use for applications – Populate application tables

Database Security and Auditing 25

DML Auditing Using Repository with Oracle (Simple 1) (continued)

• Steps (continued):

– Populate auditing repository with metadata – Create the stored package to be used with the

trigger

– Create triggers for application tables – Test your implementation

Database Security and Auditing 26

DML Auditing Using Repository with Oracle (Simple 2)

• Simple Auditing Model 2: requires a higher level

of expertise in PL/SQL • Stores two types of data:

– Audit data: value before or after a DML

statement

– Audit table: name of the tables to be audited

Database Security and Auditing 27

DML Auditing Using Repository with Oracle (Simple 2) (continued)

Database Security and Auditing 28

DML Auditing Using Repository with Oracle (Simple 2) (continued)

• Steps:

– Use any user other than SYSTEM or SYS; with

privileges to create tables, and triggers

– Create the auditing repository – Establish a foreign key in AUDIT_DATA table

referencing AUDIT_TABLE table

– Create a sequence object – Create the application schema

Database Security and Auditing 29

DML Auditing Using Repository with Oracle (Simple 2) (continued)

• Steps (continued):

– Add data to tables – A stored PL/SQL package will be used for

auditing within the triggers

– Create triggers for audited tables – Add auditing metadata – Test your implementation

Database Security and Auditing 30

Auditing Application Errors with Oracle

• Application errors must be recorded for further

analysis

• Business requirements mandate to keep an

audit trail of all application errors

• Materials:

– Repository consisting of one table – Methodology for your application

Database Security and Auditing 31

Auditing Application Errors with Oracle (continued)

• Steps:

– Select any user other than SYSTEM or SYS;

with privileges to create tables, and procedures

– Populate tables – Create the ERROR table – Create a stored package to perform the

UPDATE statement

– Test your implementation: perform and update

using the CREATE package

Database Security and Auditing 32

Oracle PL/SQL Procedure Authorization

• Oracle PL/SQL stored procedures are the mainstay of implementing business rules

• Security modes:

– Invoker rights: procedure is executed using

security credentials of the caller

– Definer rights: procedure is executed using

security credentials of the owner

Database Security and Auditing 33

Oracle PL/SQL Procedure Authorization (continued)

• Steps:

– Create a new user – Select a user with CREATE TABLE and

PROCEDURE privileges

– Populate tables – Create stored procedure to select rows in a table – Grant EXECUTE privileges on new procedure – Log on as the new user and query the table – Execute procedure

Database Security and Auditing 34