Using Accounting Setup Manager
Chapter 5
Oracle Database Auditing
1
Objectives
After completing this lesson, you should be able to:
• Describe DBA responsibilities for security and auditing
• Enable standard database auditing
• Specify audit options
• Review audit information
• Maintain the audit trail
2
Separation of Responsibilities
• Users with DBA privileges must be trusted.
▪ Abuse of trust
▪ Audit trails protecting the trusted position
• DBA responsibilities must be shared.
• Accounts must never be shared.
• Separate operator and DBA responsibilities.
3
Database Security
A secure system ensures the confidentiality of the data
that it contains. There are several aspects of security:
• Restricting access to data and services
• Authenticating users
• Monitoring for suspicious activity
4
Monitoring for Compliance
Monitoring or auditing must be an integral part of
your security procedures.
Review the following:
• Mandatory auditing - database activities
• Standard database auditing - object activities
• Value-based auditing - values that were inserted, updated or
deleted
• Fine-grained auditing (FGA) – sql statement
5
Standard Database Auditing
1 Enable
database Parameter
DBA auditing. User
file executes
2 Specify audit options. command.
Database
Server
process
Audit
Generate
options
audit trail.
3 Review audit
information.
Audit
trail OS or XML
4 Maintain audit audit
trail. trail
6
Configuring the Audit Trail
Use AUDIT_TRAIL to enable database auditing.
Audit trail can be set to:
• NONE
• OS
• DB
• DB, EXTENDED
• XML
• XML, EXTENDED
ALTER SYSTEM SET AUDIT_TRAIL='XML' SCOPE=SPFILE;
Restart database after modifying this static
initialization parameter.
7
Specifying Audit Options
• SQL statement auditing:
AUDIT table;
• System-privilege auditing (nonfocused and focused):
AUDIT select any table, create any trigger;
AUDIT select any table BY hr BY SESSION;
• Object-privilege auditing (nonfocused and focused):
AUDIT ALL on hr.employees;
AUDIT UPDATE,DELETE on hr.employees BY ACCESS;
8
Default Auditing
Privileges Audited by Default
ALTER ANY PROCEDURE CREATE ANY LIBRARY GRANT ANY PRIVILEGE
ALTER ANY TABLE CREATE ANY PROCEDURE GRANT ANY ROLE
ALTER DATABASE CREATE ANY TABLE DROP ANY PROCEDURE
ALTER PROFILE CREATE EXTERNAL JOB DROP ANY TABLE
ALTER SYSTEM CREATE PUBLIC DATABASE LINK DROP PROFILE
ALTER USER CREATE SESSION DROP USER
AUDIT SYSTEM CREATE USER EXEMPT ACCESS POLICY
CREATE ANY JOB GRANT ANY OBJECT PRIVILEGE
Statements Audited by Default
SYSTEM AUDIT BY ACCESS
ROLE BY ACCESS
9
Enterprise Manager Audit Page
10
Using and Maintaining Audit Information
Disable audit options if you are not using them.
11
Value-Based Auditing
A user makes a The trigger fires. Audit record is
change. created by the trigger.
User’s change Audit record is
is made. inserted into an audit
trail table.
12
SYSDBA Auditing
Users with SYSDBA or SYSOPER privileges can
connect when the database is closed.
• Audit trail must be stored outside the database.
• Connections as SYSDBA or SYSOPER are always audited.
• You can enable additional auditing of SYSDBA or SYSOPER
actions with AUDIT_SYS_OPERATIONS.
• You can control the audit trail with AUDIT_FILE_DEST.
13
Maintaining the Audit Trail
The audit trail should be maintained with the following
best-practice guidelines:
• Review and store old records.
• Prevent storage problems.
• Avoid loss of records.
14
Quiz
Standard database auditing captures the before and
after changes of a DML transaction.
1. True
2. False
15
Quiz
Auditing of SYSDBA and SYSOPER actions is enabled
by default.
1. True
2. False
16
Summary
In this lesson, you should have learned how to:
• Describe DBA responsibilities for security and auditing
• Enable standard database auditing
• Specify audit options
• Review audit information
• Maintain the audit trail
17
Practice 7 Overview:
Implementing Oracle Database Security
This practice covers the following topics:
• Enabling standard database auditing
• Specifying audit options for the HR.JOBS table
• Updating the table
• Reviewing audit information
• Maintaining the audit trail
18
