Database Security and Auditing: Protecting Data Integrity and Accessibility

1
Database Security and Auditing Protecting Data
Integrity and Accessibility

• Chapter 8
• Application Data Auditing

2
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

3
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

4
Objectives (continued)

• Understand the importance and the implementation
  of application errors auditing in Oracle
• Implement Oracle PL/SQL procedure authorization

5
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)

6
DML Action Auditing Architecture (continued)
7
DML Action Auditing Architecture (continued)
8
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

9
Oracle Triggers (continued)
10
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

11
Oracle Triggers (continued)
12
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

13
SQL Server Triggers (continued)

• RestrictionsTransact-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

14
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

15
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

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

• ADD_POLICY parameters
• OBJECT_SCHEMA
• OBJECT_NAME
• POLICY_NAME
• AUDIT_CONDITION
• AUDIT_COLUMN
• HANDLER_SCHEMA

17
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

18
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

19
DML Action Auditing with Oracle (continued)
20
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

21
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

22
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

23
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

24
DML Auditing Using Repository with Oracle (Simple
1) (continued)
25
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

26
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

27
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

28
DML Auditing Using Repository with Oracle (Simple
2) (continued)
29
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

30
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

31
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

32
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

33
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

34
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

35
Summary

• Two approaches for DML auditing
• Set up an audit trail for DML activities
• Register all column values before or after the
  DML statement (column-level auditing)
• Fine-grained auditing (Oracle)
• Triggers
• Stored PL/SQL procedure automatically executed
• Oracle has six DML events

36
Summary (continued)

• Triggers are executed in order
• USER_TRIGGERS data dictionary view shows all
  triggers
• SQL Server 2000
• CREATE TRIGGER DDL statement
• Conditional functions UPDATE() and
  COLUMNS_UPDATED()
• FGA allows generation of audit trail of DML
  activities

37
Summary (continued)

• FGA is capable of auditing columns or tables
  Oracle PL/SQL-supplied package DBMS_FGA
• PL/SQL stored procedures security modes
• Invoker rights
• Definer rights