|
Audit Trail
|
In this issue, I will analyze if it is possible for us to prevent information leakage.
Information leakage and security vulnerability have been major issues these days.
Many companies have experienced malicious attack, security violation, unauthorized
access and computer virus. Does your company have strict security policy?
Can you exactly tell your boss when you actually copy data to external media?
Auditing enables monitoring when data are copied and which user attempts
an access to the data.
Following are operations that might be used to copy data to external media.
(1) exp command to copy data from database to external file
(2) spool command in SQL*PLUS to copy data from database to external file
exp command and sqlplus command are granted a permission by OS. I will keep security
issue regarding exp command and sqlplus command for future issue and this time,
I will examine if Oracle auditing is enabled to these operations.
(1) exp command to copy data from database to external file
Oracle does not contain a function for auditing/monitoring expert processes, but
I think auditing an export process is indispensable to prevent information leakage.
Here's what I'm going to do. First, I will investigate if there is a way to possibly audit
the export processes, and then I will focus on the following SQL statement that is issue when
exp command is executed.
SELECT FUNC_SCHEMA
,FUNC_PACKAGE
,FUNC_PROC
,CALLARG,OBJ_TYPE
,USER_ARG
FROM SYS.EXPACT$
WHERE SYS.EXPACT$.OWNER = :b1
AND SYS.EXPACT$.NAME = :b2
AND SYS.EXPACT$.CODE = :b3
ORDER BY SYS.EXPACT$.CALLORDER;
|
Auditing exp command is made possible by auditing SYS.EXPACT$.
EXPACT$(EXPort ACTion table)
EXPACT$ table records actions executed by IMPORT Utility. With this record,
IMPORT Utility can create Internal Trigger again.
Now, I enable audit to EXPACT$ table and audit export processes.
SQL> audit all on sys.expact$ by session;
Audit succeeded
|
$ exp userid=sample_a/sample_a file=sample.dmp
...
Export completed successfully
I execute exp command from a client machine WIN2K.
...
Export completed successfully
SQL> col os_username for a15
SQL> col export_user for a15
SQL> col terminal for a10
SQL> col export_time for a25
SQL>
SQL> select os_username
,username export_user
,terminal
,to_char(timestamp,'yyyy/mm/dd hh24:mi:ss') export_time
from dba_audit_object
where obj_name = 'EXPACT$' ;
OS_USERNAME EXPORT_USER TERMINAL EXPORT_TIME
------------------------- --------------- ---------- ---------------------------
oracle SAMPLE_A pts/0 2003/08/25 17:06:12
Administrator SAMPLE_A WIN2K 2003/08/25 18:15:55 <-(1)
|
What we need to do is to monitor any operation that database administrator
does not recognize as valid operation (i.e. data may be copied without authorization.)
Take a look at (1) above. SAMPLE_A executes exp command on WIN2K. I did it for testing purpose,
but if your environment allows any user to access data from a client machine via network,
data is no longer secure.
(2) spool command in SQL*PLUS to copy data from database to external file
SQL*Plus contains security functions. You can improve the security level by setting
security function in PRODUCT_USER_PROFILE table.
Now, I actually restrict SAMPLE_A user from executing spool command.
SQL> connect system/****
SQL> insert into product_user_profile
2> (product
3> ,userid
4> ,attribute
5> ,scope
6> ,numeric_value
7> ,char_value
8> ,date_value
9> ,long_value)
10> values
11> ('SQL*Plus'
12> ,'SAMPLE_A'
13> ,'SPOOL'
14> ,null
15> ,null
16> ,'DISABLED'
17> ,null
18> ,null);
SQL> commit;
Connect as SAMPLE_A user to issue spool command.
SQL> spool test
SP2-0544: Invalid command: spool
SQL>
SAMPLE_A user is not allowed to execute spool command in SQL*Plus.
|
If you want to improve the security level, be sure to audit PRODUCT_USER_PROFILE table.
That's it for today.
Koji Shinkubo
|
|
