|
Audit Trail
|
In this issue, I will actually analyze audit trail starting with Level 1
mentioned in the last issue.
Level 1: Get a log for auditing user logging into a specific application
and detect unauthorized access to database.
First of all, I set the following parameters:
(1) audit_trail=[ NONE | TRUE | FALSE | DB | OS ]
(2) audit_file_dest=$ORACLE_HOME/rdbms/audit (default)
If audit_trail is set to either NONE or FALSE, auditing is disabled.
If audit_trail is set to either TRUE or DB, database auditing is enabled
and all audit records are stored to database audit trail (i.e. SYS.AUD$).
If audit_trail is set to OS, data are stored in a directory specified
by the parameter audit_file_dest.
(For Windows NT, data are directed to event viewer.)
As data are constantly directed to SYS.AUD$, you need to delete the accumulated
data on regular basis. Besides, SYS.AUD$ is created in SYSTEM tablespace and is truncated
by DML. You may concern fragmentation of SYSTEM tablespace and try to
move SYS.AUD$ to another tablespace. This is actually not a good idea, though.
If you really concern fragmentation, set audit_trail to OS to store data
as a file.
(3) audit_sys_operations=[ TRUE | FALSE ]
Audit_sys_operations enables or disables the auditing of operations issued by user SYS
(including SYSOPER and SYSDBA).
In versions earlier than Oracle8i, you cannot let the parameter audit user SYSOPER
and SYSDBA.
This time, I set the parameters as follows and reboot Oracle database.
audit_trail=DB
audit_sys_operations=FALSE
Since my objectives are to keep track of logs of auditing sessions and to prevent
unauthorized access, I issue a command to audit the session.
SQL> audit session;
Audit succeeded.
|
This option enables collecting information related to login (SUCCESS or FAILURE),
time to log off, and OS user.
I make sure if auditing is properly set by checking DBA_STMT_AUDIT_OPTS.
(you may need to execute $ORACLE_HONE/rdbms/aimin/cataudit.sql to view
audit related information.)
SQL> select * from dba_stmt_audit_opts;
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
--------- ---------- ------------------- ---------- ----------
CREATE SESSION BY ACCESS BY ACCESS
^^^^^^^^^ ^^^^^^^^^^
*DBA_STMT_AUDIT_SESSION
USERNAME : User name if auditing is made per user. ANY CLIENT if access via proxy
instead of client is an audit target.
NULL if entire system is audit target.
PROXY_NAME : Proxy user name processing an operation to a client
NULL if client directly processes an operation.
AUDIT_OPTION : System audit option name
SUCCESS : WHENEVER SUCCESSFUL system audit mode
FAILURE : WHENEVER NOT SUCCESSFUL system audit mode
|
Optional auditing of logins (Success or Failure) is properly set.
This time, I do not audit logins of a specific user but logins of
all users. Thus, NULL appears in USER_NAME.
If you want to audit a specific user, issue following command:
SQL> audit session by "specific user name";
Suppose twenty-five valid users (SAMPLE_A to SAMPLE_Y) are allowed to
make an access to the database.
And in this situation, make some of the users access by entering invalid password,
or make a fictitious user, SAMPLE_Z attempt an unauthorized access.
I check DBA_AUDIT_SESSION that is a subset of SYS.AUD$.
select os_username
,username
,terminal
,to_char(timestamp,'hh24:mi:ss') timestamp
,action_name
,to_char(logoff_time,'hh24:mi:ss') logoff_time
,returncode
from dba_audit_session;
OS_USERNAME USERNAME TERMINAL TIMESTAMP ACTION_NAME LOGOFF_TIME RETURNCODE
----------- -------- -------- --------- ----------- ----------- ----------
kshinkub SAMPLE_A KSHINKUB 13:59:22 LOGOFF 13:59:32 0 <-(1)
...
(skipped)
...
kshinkub SAMPLE_Y KSHINKUB 14:02:03 LOGOFF 14:02:13 0
oracle XPRT 14:03:14 LOGON 0 <-(2)
kshinkub SAMPLE_Z KSHINKUB 14:03:39 LOGON 1017
kshinkub SAMPLE_A KSHINKUB 14:03:45 LOGON 1017 <-(3)
|
(1) SAMPLE_A logs in at 13:59 and logs off at the same time.
Logoff is properly done.
(2) User XPRT logs in successfully. As LOGOFF_TIME indicates
NULL, XPRT is still logging in to the database.
(3) is similar to (2). However, as RETURNCODE indicates ORA-01017,
password must be invalid. Someone pretends to be user SAMPLE_A to
attempt a login with invalid password.
(3) is an issue here. If you see many activities like (3) during
a specific time range, person(s) on the inside may be attempting unauthorized
access.
Even valid accesses like (1), be sure to always check USERNAME column.
Default users (such as users HR and SH who are normally locked)
may use the default user to attempt an unauthorized access.
This time, 252 logins, 250 normal and 2 invalid, are made.
LPS (Logon Per Second) is 2.5 when audit is enabled.
Whereas LPS is 3.0 when audit is disabled.
*I have created a word LPS (Logon Per Second).
If audit log for a session keeps being accumulated, it may affect Oracle performance.
Moreover, if a system has large number of sessions, analyzing the audit log
will be more complicated.
If I think of (3) as a problem (i.e. trace of unauthorized access), I can also
issue a command not to audit logins if these are considered success.
SQL> conn / as sysdba
SQL> truncate table aud$; <- AUD$ where data can be operable
SQL> audit session whenever not successful;
^^^^^^^^^^^^^^^^^^^^^^^
Audit succeeded.
SQL> select * from dba_stmt_audit_opts;
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
--------- ---------- ------------------- ---------- ----------
CREATE SESSION NOT SET BY ACCESS
^^^^^^^
select os_username
,username
,terminal
,to_char(timestamp,'hh24:mi:ss') timestamp
,action_name
,to_char(logoff_time,'hh24:mi:ss') logoff_time
,returncode
from dba_audit_session;
OS_USERNAME USERNAME TERMINAL TIMESTAMP ACTION_NAME LOGOFF_TIME RETURNCODE
----------- -------- -------- --------- ----------- ----------- ----------
kshinkub SAMPLE_Z KSHINKUB 15:03:39 LOGON 1017
kshinkub SAMPLE_A KSHINKUB 15:03:45 LOGON 1017
Sessions with FAILURE logins only are recorded to audit log.
|
Following is a command to clear audit.
SQL> noaudit session;
^^^^^^^
Noaudit succeeded.
SQL> select * from dba_stmt_audit_opts;
no rows selected
|
Next week, I will look into LOGOFF_LREAD and LOGOFF_PREAD in DBA_AUDIT_SESSION
to identify a session causing a problem and then try to identify SQL statement
issued by that session.
That's it for today.
Koji Shinkubo
|
|
