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
• Define and implement Oracle fine-grained auditing

3
Objectives (continued)

• Create a DML statement audit trail for Oracle
• Generate a data manipulation history
• Understand the importance and the implementation
  of application errors auditing in Oracle
• Implement Oracle PL/SQL procedure authorization

4
ORACLE Auditing Categories

• Audited by default
• Instance startup shutdown use of Administrator
  privileges
• Database auditing
• Is enabled by the DBA
• Cannot record column values
• Can record use of DML or DDL statements and use
  of grants
• Cannot record use of select statements

5
Auditing Categories

• Fine grained auditing
• Can record use of select statements
• Includes the code of the statement that was
  executed
• In 9i only available for select statements in 10g
  also available for DML statements
• Value-based or application auditing
• Is implemented through code (usually PL/SQL)
• Can record column values
• Used to track changes to tables

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

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

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

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

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

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

15
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

16
ORACLE Fine-grained Auditing

• Provides the monitoring of data access based on
  content
• Is implemented using the DBMS_FGA package
• Applies only to select statements in 9i
• Is available for DML statements in 10g
• Is implemented through POLICIES

17
Example Adding a Fine Grained Auditing Policy

• begin
• dbms_fga.add_policy(
• object_schema gt hr',
• object_name gt 'employees',
• policy_name gt 'audit_pay_access',
• audit_condition gt department20',
• audit_column gt salary',
• enable gt TRUE
• )
• end
• /
• In this case we are auditing all select
  statements that retrieve the salary column of the
  employees table for any employees in department
  20

18
Example Dropping and Disabling FGA Policies

• Dropping a policy
• begin
• dbms_fga.drop_policy(
• object_schema gt hr',
• object_name gt employees',
• policy_name gt 'audit_salary_access')
• end
• /
• Disabling a policy
• begin
• dbms_fga.disable_policy(
• object_schema gt hr',
• object_name gt employees',
• policy_name gt 'audit_salary_access')
• end
• /
• Enabling a policy as above except use
  enable_policy

19
Monitoring FGA Results

• Use the view - DBA_FGA_AUDIT_TRAIL
• This view includes the column SQL_TEXT which
  list the SQL statement that generated the audit
  record
• E.g.
• SQLgt select db_user, object_name, policy_name,
  sql_text from dba_fga_audit_trail
• DB_USER OBJECT_NAME POLICY_NAME
• --------------------------------------------------
  ---------------------------------------
• SQL_TEXT
• --------------------------------------------------
  ------------------------------
• DBA11 EMP_HIST
  AUDIT_SUSPENSION_ACCESS
• select from emp_hist

20
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

21
Value Based or Application Auditing

• Create a table or tables to store audit results
• Write PL/SQL triggers that cause records to be
  written to your audit tables when triggering
  events occur
• Advantages
• Greater flexibility about what is audited under
  what conditions
• Can write as much information about the audited
  action as desired
• For example can write old and new values of an
  updated field into the audit record
• Disadvantages
• Greater complexity and potentially greater
  overhead
• Cannot be used for select statements since a
  SELECT does not create an event that is monitored
  for triggers

22
Example of User Defined Value Based Auditing

• We will create a table to store information about
  increases to employee salaries and
• Create a trigger that will write identifying
  information, as well as, the old and new salary
  values whenever an employee record is updated in
  a way that increases salary
• The create table statement
• create table
• sal_increase_log
• (ctl_upadeuser varchar2(20),
• ctl_upd_time date,
• tablename varchar2(20),
• old_salary number(8,2),
• new_salary number(8,2))

23
Example of Trigger code

• create or replace trigger emp_sal_inc
• before update of salary on hr.employees
• for each row
• begin
• if new.salary gt old.salary then
• insert into dba11.sal_increase_log values
  (ora_login_user, sysdate,'EMPLOYEES',
  old.salary, new.salary)
• end if
• end

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

26
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

27
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

28
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

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

31
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

32
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

33
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

34
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

35
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

36
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

37
Summary (continued)

• Triggers are executed in order
• USER_TRIGGERS data dictionary view shows all
  triggers
• FGA allows generation of audit trail of DML
  activities
• 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