In this article I will explore Oracle’s most powerful auditing
tool, the Oracle9i fine-grained auditing (FGA) package.
For complete details and working scripts, see the book “Oracle
Privacy Security Auditing” by Arup Nanda, a great source of expert advice on
Oracle SQL auditing.
September 22,
2003
The new HIPAA health care laws have placed a tremendous burden
on enterprises using Oracle database systems. According to the
law, organizations must provide complete audit trails for all
DDL (i.e., schema changes), DML (e.g., updates, insert,
deletes), and select audits of confidential patient
information. HIPAA also requires that all health care
companies (not just hospitals) archive audit trails of anyone
who views patient data, and HIPAA provides severe penalties,
including jail time, for companies that fail to implement
select auditing. Hence, thousands of Oracle shops are rushing
to implement Oracle auditing mechanisms.
Starting with Oracle9i you'll see a more sophisticated
auditing mechanism using the dbms_fga package. The
dbms_fga package allows you to specify the auditing rules
for a particular column of a table and report on anyone whose
queries match the criteria.
Below, I create a policy called expensive_books
that acts as a trigger for any queries against the book where
anyone views a book row where book_retail_price is
greater than $50.
connect pubs/pubs
exec dbms_fga.drop_policy( -
object_schema => 'PUBS', -
object_name => 'BOOK', -
policy_name => 'EXPENSIVE_BOOKS' -
);
begin
dbms_fga.add_policy(
object_schema => 'PUBS',
object_name => 'BOOK',
policy_name => 'EXPENSIVE_BOOKS',
audit_condition => 'BOOK_RETAIL_PRICE>=50',
audit_column => 'BOOK_TITLE',
handler_schema => null,
handler_module => null,
enable => true
);
end;
/
Note the parameters of the FGA policy function can give you
many choices of auditing options. You can specify the table to
be audited and the Boolean criteria for triggering a select
audit.
All audit information will be written to an Oracle view called
dba_fga_audit_trail. Below shows the contents of each audit row; note that
all details are captured including the user ID and the actual
SQL that was used to read the table. Of course, in a busy
system this view will become huge very quickly, and mechanisms
must be written to periodically offload the audit data.
desc dba_fga_audit_trail
Name Null? Type
---------------------- -------- ---------------------
SESSION_ID NOT NULL NUMBER
TIMESTAMP NOT NULL DATE
DB_USER VARCHAR2(30)
OS_USER VARCHAR2(255)
OBJECT_SCHEMA VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
POLICY_NAME VARCHAR2(30)
SCN NUMBER
SQL_TEXT VARCHAR2(4000)
SQL_BIND VARCHAR2(4000)
COMMENT$TEXT VARCHAR2(4000)
Now, let’s test the policy and run some SQL queries. Below, I've run a variety of queries against the book
table.
In this example, you can see that not all SQL statements will
meet the Boolean criteria and only rows where books that cost
over $50 will appear in dba_fga_audit_trail.
For example, if you have a 1,000,000 row patient table
that is viewed ten million times per day, there will be ten
million rows written to dba_fga_audit_trail, and the
audit table will become larger than the patient table
in just a few hours. So you must develop a mechanism to
offload the audit data before the tablespace becomes full.
Many shops write a dbms_job routine to transport the
table data into a special tablespace, usually using WORM
(write once, read many) media such as CD-ROM.
connect fred/fred;
select
*
from
pubs.book
where
book_type = ‘porno’; --This will trigger an audit
select
book_type
from
pubs.book
where
book_retail_price > 60; -- not selecting book_title, so no audit
select
book_title,
book_type
from
pubs.book
where
book_retail_price > 60; -- selected book_title w price > 50, so audit
Now let’s look at ways to display the audit information.
Below is a script that I used to display the audit
table entries. Note that you can roll up select counts by hour
of day by aggregating on the timestamp column.
set lines 100
col c1 heading 'Time|Stamp' format a12
col c2 heading 'DB|User' format a5
col c3 heading 'OS|User' format a15
col c4 heading 'Schema' format a5
col c5 heading 'Object|Name' format a10
col c6 heading 'Policy|name' format a20
col c7 heading 'SQL statement' format a40
select
timestamp c1,
db_user c2,
os_user c3,
object_schema c4,
object_name c5,
policy_name c6,
sql_text c7
from
dba_fga_audit_trail
order by
timestamp;
spool off;
The report is shown below. I got complete details from the audit, and only
those rows specifically in the policy are written to
dba_fga_audit_trail.
Time DB
OS
Object Policy
Stamp User User Schem Name name
------------ ----- --------------- ----- ---------- --------------------
SQL statement
----------------------------------------
20-MAY-03 FRED BASK\Owner PUBS BOOK EXPENSIVE_BOOKS
select * from pubs.book
20-MAY-03 FRED BASK\Owner PUBS BOOK EXPENSIVE_BOOKS
select * from pubs.book
20-MAY-03 FRED BASK\Owner PUBS BOOK EXPENSIVE_BOOKS
select book_title, book_type from pubs.book where book_retail_price > 60
