Auditing in Oracle

Auditing in Oracle

SQL> show parameter audit NAME TYPE ----------------------------------- ------------------audit_file_dest string audit_sys_operations boolean audit_syslog_level string audit_trail string transaction_auditing boolean

VALUE

------------?/rdbms/audit FALSE NONE DB TRUE

AUDIT_TRAIL can have the following values. AUDIT_TRAIL={NONE or FALSE| OS| DB or TRUE| or TRUE| DB_EXTENDED| XML |XML_EXTENDED}

Change this parameter in initprod.ora

audit_trail=db

SQL> SHUTDOWN Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP ORACLE instance started. Total System System Global Area 289406976 bytes Fixed Size 1248600 bytes Variable Size 71303848 bytes Database Buffers 213909504 bytes Redo Buffers 2945024 bytes Database mounted. Database opened. SQL> Enable audit on FND_USER ,DBA_USERS table AUDIT INSERT , UPDATE , DELETE ON applsys.fnd_user BY ACCESS; AUDIT INSERT , UPDATE , DELETE ON DBA_USERS BY ACCESS; To view audit data: select * select  * from from dba_audit_trail  dba_audit_trail;; Disable Audit:  NOAUDIT select ,INSERT , UPDATE , DELETE  NOAUDIT select ,INSERT , UPDATE , DELETE Maintenance: SQL> DELETE FROM sys.aud$;

ON applsys.fnd_user; ON DBA_USERS;

5. Enable Database Auditing http://blog.opensecurityresearch.com/2012/03/top-10-oracle-steps-to-secure-oracle.html

Audit SYS Operations By default Oracle databases do not audit SQL commands executed by the privileged SYS, and users connecting with SYSDBA or SYSOPER privileges. If your database is hacked, these privileges are going to t he be the hackers first target. Fortunately auditing SQL commands of these privileged users is very simple: sqlplus> alter system set audit_sys_operations=true scope=spfile;

Enable Database Auditing Again, by default Oracle auditing of SQL commands is not enabled by default. Auditing should be turned on for all SQL commands. Database auditing is turned on with the audit_trail parameter: sqlplus> alter system set audit_trail=DB,EXTENDED scope=spfile; Note: The command above would enable auditing from the database, but not the database vault information, into the table SYS.AUD$. There are actually four database auditing types: OS, DB, EXTENDED, and XML.

Enable Auditing on Important Database Objects Once auditing has been enabled, it can be turned on objects where an audit trail is important. The following is a list of common objects that should be audited: AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT AUDIT

CREATE USER BY ACCESS; ALTER USER BY ACCESS; DROP USER BY ACCESS; CREATE ROLE BY ACCESS; SELECT ON DBA_USERS BY ACCESS; CREATE EXTERNAL JOB BY ACCESS; -- 10g Rel.2 CREATE JOB BY ACCESS; -- 10g Rel.1 CREATE ANY JOB BY ACCESS; CREATE ANY LIBRARY BY ACCESS; ALTER DATABASE BY ACCESS; ALTER SYSTEM BY ACCESS; AUDIT SYSTEM BY ACCESS; EXEMPT ACCESS POLICY BY ACCESS; GRANT ANY PRIVILEGE BY ACCESS; GRANT ANY ROLE BY ACCESS; ALTER PROFILE BY ACCESS; CREATE ANY PROCEDURE BY ACCESS; ALTER ANY PROCEDURE BY ACCESS; DROP ANY PROCEDURE BY ACCESS; CREATE PUBLIC DATABASE LINK BY ACCESS; CREATE PUBLIC SYNONYM BY ACCESS; EXECUTE ON DBMS_FGA BY ACCESS; EXECUTE ON DBMS_RLS BY ACCESS; EXECUTE ON DBMS_FILE_TRANSFER BY ACCESS; EXECUTE ON DBMS_SCHEDULER BY ACCESS; EXECUTE ON DBMS_JOB BY ACCESS; SELECT ON SYS.V_$SQL BY ACCESS; SELECT ON SYS.GV_$SQL BY ACCESS; EXECUTE ON SYS.KUPP$PROC BY ACCESS; EXECUTE ON DBMS_XMLGEN BY ACCESS; EXECUTE ON DBMS_NETWORK_ACL_ADMIN BY ACCESS; -- 11g