I don’t know what auditing requirements may be in place in other parts of the world, but here in the U.S. we have the Sarbanes-Oxley Act (resulting from the Enron debacle from a few years back) which basically calls for audit trails for all financial activity in an organization, to prevent such cover-ups from recurring. That being said it appears that everyone, performing financial and non-financial tasks, is being audited under the aegis of SOx (as Sarbanes-Oxley is known). Many DBAs are being asked to supply such audit trails; Oracle (depending upon the release) makes this fairly straightforward. [I will not be posting an exhaustive treatise on auditing, I’ll simply be providing some examples and references for further reading.] Starting with 8i (I’ll consider 8.1.6 through 8.1.7.4) connection auditing was offered with the advent of the AFTER LOGON and BEFORE LOGOFF triggers. The code I’ve pasted below, modified a bit by myself, has been around for a long while, and I can’t remember where I first saw it [if anyone can provide proper attribution I’ll gladly give the author due recognition]. Notice it uses the SYS_CONTEXT function, which should be investigated for the Oracle version you’re using, as implementations and available information can change with each new release:
--
-- Create the connection log table
--
create table
stats$user_log
(
user_id varchar2(30),
session_id number(8),
sid number,
serial# number,
host varchar2(64),
ip_address varchar2(16),
last_program varchar2(48),
last_action varchar2(32),
last_module varchar2(48),
logon_day date,
logon_time varchar2(10),
logoff_day date,
logoff_time varchar2(10),
elapsed_minutes number(8),
elapsed_seconds number(8),
last_sql_address raw(4),
last_sql_hash_value number,
prior_sql_address raw(4),
prior_sql_hash_value number
)
tablespace tools
;
--
-- Create the logon trigger to populate the audit trail
--
-- SYS_CONTEXT('USERENV','IP_ADDRESS') will be NULL for local connections
--
create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
DECLARE
lo_dt date;
BEGIN
select max(logon_time)
into lo_dt
from v$session
where username = user;
dbms_output.put_line(lo_dt);
-- if user 'SYS' then
if user not in ('SYS','SYSTEM','P2000AP') then
insert into stats$user_log
( user_id ,
session_id ,
sid ,
serial# ,
host ,
ip_address ,
last_program ,
last_action ,
last_module ,
logon_day ,
logon_time ,
logoff_day ,
logoff_time ,
elapsed_minutes,
elapsed_seconds,
last_sql_address,
last_sql_hash_value,
prior_sql_address,
prior_sql_hash_value)
values(
user,
sys_context('USERENV','SESSIONID'),
null,
null,
sys_context('USERENV','HOST'),
sys_context('USERENV','IP_ADDRESS'),
null,
null,
null,
lo_dt,
to_char(lo_dt, 'hh24:mi:ss'),
null,
null,
null,
null,
null,
null,
null,
null
);
update stats$user_log
set sid = (select sid from v$session where sys_context('USERENV','SESSIONID') = audsid),
serial# = (select serial# from v$session where sys_context('USERENV','SESSIONID') = audsid)
where session_id = sys_context('USERENV','SESSIONID');
end if;
END;
/
show errors
create or replace trigger logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
DECLARE
lo_dt date:=sysdate;
BEGIN
-- ***************************************************
-- Update the user record
--
-- Set last_action, last_program, last_module,
-- logoff day and time and total minutes connected
-- ***************************************************
-- if user 'SYS' then
if user not in ('SYS','SYSTEM','P2000AP') then
update
stats$user_log
set
last_action = (select action from v$session where sys_context('USERENV','SESSIONID') = audsid),
last_program = (select program from v$session where sys_context('USERENV','SESSIONID') = audsid),
last_module = (select module from v$session where sys_context('USERENV','SESSIONID') = audsid),
last_sql_address = (select sql_address from v$session where sys_context('USERENV','SESSIONID') = audsid),
last_sql_hash_value = (select sql_hash_value from v$session where sys_context('USERENV','SESSIONID') = audsid),
prior_sql_address = (select prev_sql_addr from v$session where sys_context('USERENV','SESSIONID') = audsid),
prior_sql_hash_value = (select prev_hash_value from v$session where sys_context('USERENV','SESSIONID') = audsid),
logoff_day = lo_dt,
logoff_time = to_char(lo_dt, 'hh24:mi:ss'),
elapsed_minutes = round((lo_dt - logon_day)*1440),
elapsed_seconds = round((lo_dt - logon_day)*86400)
where
sys_context('USERENV','SESSIONID') = session_id;
end if;
END;
/
This works well in 8i (but, hopefully, you’re not running a museum and your version is currently supported) creating a connection audit trail for all users, including SYS and SYSTEM (which is necessary for any serious auditing scenario). It also provides the last ACTION and MODULE the connected account used to allow some traceability and limited accountability. [11g (and releases down to 9i2) offers the AUDIT SESSION option, which should replace the code listed above and provides logon time, logoff time and various session-level statistics. These records are written to the SYS.AUD$ table (and are also available in a ‘massaged’ format in the DBA_AUDIT_TRAIL view). SYS.AUD$ can change from release to release, but includes, among other columns, ACTION#, which can be ‘translated’ into an action using the AUDIT_ACTIONS table, which lists the ACTION (mapped to ACTION# in SYS.AUD$) and the NAME. 10g and later releases provide the AUDIT CONNECT option, populating the same tables, but also providing logon and logoff information and the return code, which allows the DBA to investigate abnormal session termination. It is recommended in 9i and later releases to use the provided AUDIT options rather than create the connection audit table and triggers necessary in 8i.] This, however, is not enough of a trail to trap any ‘suspect’ activity in the database; All releases since 9iR2 provide Fine-grained auditing of ‘qualified’ Select statements (those constructed with a WHERE clause) producing an audit trail of ‘offending’ queries, queries accessing sensitive information general users should not see. Fine-grained auditing works in this way:
A policy is set up on a table providing a ‘triggering’ mechanism based upon a column in that table. If a user queries this table for the audited column and supplies the necessary audit condition a record is written to the SYS.FGA_LOG$ table. From the on-line documentation: The following example shows how you can audit SELECT statements on table hr.emp to monitor any query that accesses the salary column of the employee records which belong to sales department:
begin DBMS_FGA.ADD_POLICY( object_schema => 'hr', object_name => 'emp', policy_name => 'chk_hr_emp', audit_condition => 'dept = ''SALES'' ', audit_column => 'salary'); end; /
Then, either of the following SQL statements will cause the database to log an audit event record.
SELECT count(*) FROM hr.emp WHERE dept = 'SALES' and salary > 10000000;
or
SELECT salary FROM hr.emp WHERE dept = 'SALES';
Notice that SELECT salary from hr.emp; would NOT generate an audit record as the audit condition (dept = ‘SALES’) is not supplied. Thus, any query not ‘qualified’ with the ‘proper’ [read that as ‘defined in the audit policy’] WHERE clause will not produce a trail, even if the desired column is queried. To make this possibly more confusing this query won’t produce a trail, either: SELECT salary from hr.emp where dept = ‘MARKETING’; since MARKETING isn’t a department triggering any audit action. One can, though, establish an audit policy which doesn’t require a WHERE clause:
begin
DBMS_FGA.ADD_POLICY(
object_schema => 'hr',
object_name => 'emp',
policy_name => 'chk_hr_emp'
);
end;
/
With the above policy in place any query against the EMP table will produce a trail:
SQL> select *
2 from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL>
SQL> column obj$name format a10
SQL> column obj$schema format a10
SQL> column lsqltext format a30
SQL>
SQL> select
2 SESSIONID,
3 OBJ$SCHEMA,
4 OBJ$NAME,
5 LSQLTEXT,
6 NTIMESTAMP#
7 from sys.fga_log$;
SESSIONID OBJ$SCHEMA OBJ$NAME LSQLTEXT NTIMESTAMP#
---------- ---------- ---------- ------------------------------ ---------------------------------
1095613 BING EMP select * 17-APR-09 05.40.10.264000 PM
from emp
SQL>
Of course producing such an extensive trail also consumes disk space, as records are written to audit tables located, by default, in the SYSTEM tablespace; these tables include AUDIT$ and AUD$ for basic auditing data, FGA$ and FGA_LOG$ for the fine-grained auditing data and RLS$, RLS_CTX$ and RLS_GRP$ for fine-grained access control (row-level security, discussed briefly below). It has been suggested by Oracle these tables be moved to a separate tablespace. [However Oracle also states that moving these tables can affect database upgrades and before upgrading a database these tables should be moved back to the SYSTEM tablespace. Interesting.] A tablespace solely for auditing can be created and, by using ALTER TABLE … MOVE TABLESPACE … , these tables can be relocated.
How do you know what actions are being audited? The ACTION# column in AUD$ lists the associated code for the action, and AUDIT_ACTIONS associates a name with the action. A query to return who did what based upon the ACTION# is shown below:
SQL> select sessionid, userid, statement, name
2 from aud$, audit_actions
3 where action = action#;
SESSIONID USERID STATEMENT NAME
---------- ------------------------------ ---------- ----------------------------
387143 BING 1 LOGON
387143 BING 7 SESSION REC
387143 BING 8 SESSION REC
387143 BING 11 AUDIT OBJECT
387143 BING 12 SELECT
387143 BING 12 SELECT
6 rows selected.
SQL>
Versions since 9i (9.0.1.x) and 9iR2 (9.2.0.x) also offer Fine-grained access control, with 10g and later releases providing Virtual Private Database, where data can be restricted automatically (depending upon table structure). An example of fine-grained access control would be allowing companies to ‘see’ only their data by establishing a security policy that would dynamically modify the query submitted by adding a WHERE clause limiting the data to only that for the user’s company. The current documentation is an excellent source of information for both Virtual Private Database and an older option, Oracle Label Security. VPD is discussed here, and Oracle Label Security is discussed here. Both topics are too involved to discuss in this article, hence the links to the online documentation.
Okay, so you can’t easily restrict data in some tables, so fine-grained access control won’t work, and fine-grained auditing won’t be of any use as management has decided implementing such a strategy would not be cost-effective. Plain-vanilla auditing can help by providing a basic knowledge of the activities of a session or against particular objects. This doesn’t provide any actual SQL statements (you’ll need to be creative and write your own mechanism to extract SQL from V$SQLTEXT based upon information found in V$SESSION and V$PROCESS, which might be more work than configuring fine-grained auditing) but it does provide a sort of ‘history’ of actions against an object, who performed them and when they occurred. Auditing by access produces a record every time the object is accessed, for any session and without limit to the number of accesses, which can produce a rather bloated audit trail. Such auditing requires the audit_trail initialization parameter be set to db so that records can be written to the SYS.AUD$ table. To audit select statements for the DEPARTMENT table by access:
AUDIT SELECT ON DEPARTMENT BY ACCESS;
Auditing by session writes a record to the audit trail once for each monitored object accessed in a session, regardless of the number of times that object is accessed. To perform this level of auditing on select statements against the DEPARTMENT table:
AUDIT SELECT ON DEPARTMENT BY SESSION;
In the first case, audit by access, if the DEPARTMENT table is being monitored and SUE issues six select statements against the DEPARTMENT table then six audit records are written to the trail. If audit by session is used instead, those six select statements would write only one record in the audit trail, indicating the table was accessed by SUE’s session but not showing how many times this access occurred. It all depends upon how management wishes to track such actions; it’s up to the DBA to provide a workable solution using the tools Oracle provides.
Using the ‘plain vanilla’ audit trail any SELECT statement will generate an audit record; it depends only upon which method, by access or by session, is selected to determine how much of a trail is produced. In fact, any SELECT, INSERT, UPDATE or DELETE action can be audited on an object or schema and you can audit successful attempts, unsuccessful attempts, or both (the default). To disable auditing use the NOAUDIT command:
NOAUDIT SELECT ON DEPARTMENT;
Note this turns off all auditing, by session and by access, for the given object. The documentation at http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c25audit.htm#538 has a wealth of information on this topic.
The auditing options haven’t changed much in 11g — everything available in 9i and 9iR2 is available in both releases of 10g, and a new wrinkle, ‘Label Security auditing’, is offered. Not having a 10g database with which to play I’ll refer you again to the on-line documentation:
http://download-east.oracle.com/docs/cd/B19306_01/network.102/b14267/audit.htm
How can you keep track of which objects are being audited? In 9i and later releases the DBA_OBJ_AUDIT_OPTS view exists, which contains the owner, object_name, object_type and audit options in effect for all audited objects. A simple…
SQL> select owner, object_name
2 from dba_obj_audit_opts
3 /
OWNER OBJECT_NAME
------------------------------ ------------------------------
BING EMPLOYEE
BING DEPARTMENT
SQL>
…will display all objects and owners currently being audited in any manner.
Auditing is not a task to be taken lightly, and it can require fairly extensive management depending upon which auditing path is chosen. I hope I’ve provided a basic overview and a general idea of what options are available and what those options do.