Docstoc

Oracle Auditing (PowerPoint)

Document Sample
Oracle Auditing (PowerPoint) Powered By Docstoc
					  Oracle Auditing

     Natalka Roshak
Presented to ABCD-Oracle
      4 March 2004
       About me
• Oracle and Sybase Database Administrator,
Analyst and Architect
• Experienced database programmer
• Oracle Certified Professional (OCP)
• Regular columnist for Oracle trade magazine,
orafaq.com, and other trade publications
• Oracle consultant, serving customers across
North America
• Available for consulting engagements
• http://rdbms-insight.com



          Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
      About this presentation
• Presented to Oracle users’ group at Harvard
University, ABCD-Oracle
• 4 March 2004




                  Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
              Auditing

2 main types of auditing:
• Oracle-supplied auditing using AUD$
• Trigger-based DML auditing




                Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
Oracle vs. Trigger-based Auditing

Different scope & application:
• AUD$ does not record any of the data values
  involved in a DML change; need to use trigger-
  based auditing to capture this info
• Obviously, Oracle auditing allows auditing of
  non-DML statements, eg. ALTER ROLE



                   Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
          Oracle Auditing

I. Overview of Oracle Auditing
II. Fun with AUDIT SESSION
III. Security considerations & AUD$ size




                Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
I. Overview of Oracle Auditing
     Enabling Oracle Auditing

First step: set static initialization parameter
AUDIT_TRAIL in INIT.ORA

Values:
• DB/TRUE
• OS
• NONE/FALSE
                   Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
           OS Audit Trail

Even if OS audit trail is not enabled, Oracle
will still write default actions to OS audit
  trail:
• instance startup
• instance shutdown
• connections to the database as SYSOPER
  or SYSDBA

                  Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
OS Audit Trail




    Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
                DB trail

• Writes all audit information to
  SYS.AUD$
• AUD$ installed by catalog.sql
• Create auditing data dictionary views by
  running
  $oracle_home\rdbms\admin\cataudit.sql
• Remove these views using catnoaud.sql

                 Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
•   SQL> desc aud$
                               SYS.AUD$
•    Name                                        Null?    Type
•    -----------------------------------------   -------- --------------
•    SESSIONID                                   NOT NULL NUMBER
•    ENTRYID                                     NOT NULL NUMBER
•    STATEMENT                                   NOT NULL NUMBER
•    TIMESTAMP#                                  NOT NULL DATE
•    USERID                                               VARCHAR2(30)
•    USERHOST                                             VARCHAR2(128)
•    TERMINAL                                             VARCHAR2(255)
•    ACTION#                                     NOT NULL NUMBER
•    RETURNCODE                                  NOT NULL NUMBER
•    OBJ$CREATOR                                          VARCHAR2(30)
•    OBJ$NAME                                             VARCHAR2(128)
•    AUTH$PRIVILEGES                                      VARCHAR2(16)
•    AUTH$GRANTEE                                         VARCHAR2(30)
•    NEW$OWNER                                            VARCHAR2(30)
•    NEW$NAME                                             VARCHAR2(128)
•    SES$ACTIONS                                          VARCHAR2(19)
•    SES$TID                                              NUMBER
•    LOGOFF$LREAD                                         NUMBER
•    LOGOFF$PREAD                                         NUMBER
•    LOGOFF$LWRITE                                        NUMBER
•    LOGOFF$DEAD                                          NUMBER
•    LOGOFF$TIME                                          DATE
•    COMMENT$TEXT                                         VARCHAR2(4000)
•    SPARE1                                               VARCHAR2(255)
•    SPARE2                                               NUMBER
•    OBJ$LABEL                                            RAW(255)
•    SES$LABEL                                            RAW(255)
•    PRIV$USED                                            NUMBER
                                         Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
                                       Audit Views
STMT_AUDIT_OPTION_MAP
Contains information about auditing option type codes.
AUDIT_ACTIONS
Contains descriptions for audit trail action type codes.
ALL_DEF_AUDIT_OPTS
Contains default object-auditing options that will be applied when objects are created.
DBA_STMT_AUDIT_OPTS
Describes current system auditing options across the system and by user.
DBA_PRIV_AUDIT_OPTS
Describes current system privileges being audited across the system and by user.
DBA_OBJ_AUDIT_OPTS, USER_OBJ_AUDIT_OPTS
Describes auditing options on all objects. USER view describes auditing options on all objects owned by the current user.
DBA_AUDIT_TRAIL, USER_AUDIT_TRAIL
Lists all audit trail entries. USER view shows audit trail entries relating to current user.
DBA_AUDIT_OBJECT,USER_AUDIT_OBJECT
Contains audit trail records for all objects in the system. USER view lists audit trail records for statements concerning objects
       that are accessible to the current user.
DBA_AUDIT_SESSION, USER_AUDIT_SESSION
Lists all audit trail records concerning CONNECT and DISCONNECT. USER view lists all audit trail records concerning
       connections and disconnections for the current user.
DBA_AUDIT_STATEMENT, USER_AUDIT_STATEMENT
Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the
       database, or for the USER view, issued by the user.
DBA_AUDIT_EXISTS
Lists audit trail entries produced by AUDIT EXISTS and AUDIT NOT EXISTS.


                                                     Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
       3 levels of audit options

Statement
• Audits on the type of SQL statement used, such as any
  SQL statement on a table (which records each
  CREATE, TRUNCATE, and DROP TABLE
  statement)
Object
• Audits specific statements on specific objects, such as
  ALTER TABLE on the EMP table
Privilege
• Audits use of a particular system privilege, such as
  CREATE TABLE
                       Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
        Statement Auditing

• Eg. AUDIT SELECT BY SCOTT
  audits all select statements performed by
  SCOTT
• AUDIT SELECT TABLE, UPDATE
  TABLE BY SCOTT, BLAKE;



                 Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
        Object Auditing

• Eg. AUDIT SELECT ON scott.emp;
• AUDIT SELECT ON scott.emp WHENEVER
  NOT SUCCESSFUL;
• AUDIT SELECT ON scott.emp WHENEVER
  SUCCESSFUL;
• AUDIT ALL ON scott.emp ;



              Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
         Privilege Auditing

eg. AUDIT GRANT SEQUENCE audits any
  statements of the type:
• GRANT privilege ON sequence ;
• REVOKE privilege ON sequence ;
AUDIT EXECUTE PROCEDURE audits
• CALL of any procedure
AUDIT SELECT TABLE audits
• SELECT FROM table/view/materialized view;

                 Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
     Privilege auditing, con’t

AUDIT INDEX audits any statements of the type:
• CREATE INDEX
• ALTER INDEX
• DROP INDEX
AUDIT NOT EXISTS audits all SQL stmts that
  fail because an object doesn’t exist
AUDIT SYSTEM AUDIT audits all
  AUDIT/NOAUDIT statments
*AUDIT SESSION audits logon/logoff
                  Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
     BY SESSION/ACCESS

• BY SESSION: One audit record is inserted for
  one session, regardless of the number of times
  the statement is executed
• BY ACCESS: One audit record is inserted
  each time the statement is executed
• Default is BY SESSION



                   Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
       Further examples

• AUDIT SESSION BY JOHN, ALEX
  WHENEVER NOT SUCCESSFUL;
• AUDIT UPDATE, DELETE ON
  scott.emp BY ACCESS;
• NOAUDIT UPDATE, DELETE ON
  scott.emp;

             Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
II. Fun with AUDIT SESSION
      Fun with AUDIT SESSION
• Underappreciated fact: AUD$ records IP when
  session auditing is enabled
•   SQL> select timestamp#, userid, machine, action#, returncode, logoff$time,
    comment$text from aud$ where action# in (100,101);
•   TIMESTAMP USERID MACHINE                                ACTION# RETURNCODE LOGOFF$TI
    COMMENT$TEXT
•   --------- ---------- --------------- ------- ---------- -------
•   02-FEB-04 EREQ_USER GYPSY                                   101  0 02-FEB-04 Authenticated by:
    DATABASE; Client address:
    (ADDRESS=(PROTOCOL=tcp)(HOST=140.247.232.23)(PORT=3406))
•   02-FEB-04 EREQ_USER GYPSY                                   100  0      Authenticated by:
    DATABASE; Client address:
•   (ADDRESS=(PROTOCOL=tcp)(HOST=140.247.232.23)(PORT=3427))
•   02-FEB-04 EREQ_USER GYPSY                                   101  0 02-FEB-04 Authenticated by:
    DATABASE; Client address:
    (ADDRESS=(PROTOCOL=tcp)(HOST=140.247.232.23)(PORT=3432))
•   02-FEB-04 BULKLOAD SHALLOT                                  100  0      Authenticated by:
    DATABASE; Client address:
    (ADDRESS=(PROTOCOL=tcp)(HOST=140.247.164.242)(PORT=2544))

                                         Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
             AUD$ fields

For 100/101 events (ACTION# IN (100,101)):
• col SPARE1 = OS username
• col RETURNCODE = ora-xxxx returncode
• col TERMINAL = terminal name (eg. ttyp3 in
  UNIX, machine name in Windows)
• col COMMENT$TEXT = IP and port of client
  -- very useful!

                  Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
              Extracting the IP

select userid, terminal, spare1, substr(s1,1,instr(s1,')')-1)
   IP,
returncode, timestamp#
from
   (select A.*,
   substr(comment$text,instr(comment$text,'HOST=')+5,
   100) s1
   from aud$ a where action# in (100,101) )
order by IP;

                         Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
                            Sample output
USERID    TERMINAL SPARE1               IP                  RETURNCODE         TIMESTAMP#
-------------------------------------------------------------------------------------------
HERS      MARS      apacheco            128.103.231.86      0 02/24/2004 3:08:36 PM
HERS                oracle              128.103.231.86      0 01/25/2004 11:14:11 AM
EREQ_USER MRUTENBUR mrutenburg          140.247.10.130      0 01/30/2004 4:42:52 PM
BULKLOAD CJTRU      cjtru               140.247.10.132      0 02/05/2004 4:26:41 PM
BULKLOAD ttyp3      oracle              140.247.10.132      0 02/09/2004 10:42:13 AM
SYS       ttyp3     oracle              140.247.10.132      0 02/09/2004 11:55:56 AM
BULKLOAD ROBIN      michael             140.247.10.135      0 01/29/2004 7:59:49 AM
SYSTEM    JB        Jen?Braster         140.247.10.135      0 01/28/2004 8:34:31 PM
EREQ_USER JB        Jen?Braster         140.247.10.135      0 01/28/2004 8:39:07 PM




                                      Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
                Returncodes

• Indicates ora-XXXX if logon failed
• Common returncodes:
   •   0 – successful
   •   1017 – invalid username/password
   •   1005 – null password given
   •   1035 – RESTRICTED SESSION enabled
• Eg. Add “where” clause to query to filter on
  ora-1017s
                    Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
          Other possibilities

• Create table of known IPs expected to connect,
  query for other IPs
• Alert when there’s a connection attempt from a
  new or non-Harvard IP
• Create table of known terminals expected to
  connect over VPN (140.247.10/11.0/255),
  query for other terminals (or terminal &
  username combo) connecting over VPN

                   Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
Filtering on known IPs/terminals
select * from (
select userid, terminal, spare1, substr(s1,1,instr(s1,')')-1) IP,
k.hostname, str.returncode, count(*) from
(select A.*, substr(comment$text,instr(comment$text,'HOST=')+5,100) s1
 from aud$ a
 ) str, system.known_ips k
where 1=1
and (substr(s1,1,instr(s1,')')-1) not in (select ip from system.known_ips)
   and NOT ( (substr(s1,1,instr(s1,')')-1) like '140.247.10%'
           OR substr(s1,1,instr(s1,')')-1) like '140.247.11%' )
         AND str.terminal in (select terminal from system.known_terminals)
         )
   )
and str.terminal NOT in (select terminal from system.known_terminals)
and substr(s1,1,instr(s1,')')-1)=k.ip(+)
and timestamp# > sysdate-20
group by userid, terminal, spare1, substr(s1,1,instr(s1,')')-1), hostname, returncode
) where IP not like '140.247.164.%' and IP not in ('140.247.232.22','140.247.232.23')
order by IP
                                     Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
Filtering on known IPs/terminals

USERID    TERMINAL            SPARE1    IP                  RETURNCOD COUNT(*)
-----------------------------------------------------------------------------
    -
SYS       unknown             n_user    140.247.10.149      0         1
UTILITY   LOKI                dfaux     140.247.10.150      0         1
BULKLOAD CLIENT1              kyu       140.247.11.20       0         3
SYS       ttyp2               oracle    140.247.11.20       0         1
SYS       ttyp2               oracle    140.247.11.20       1017      1
ASPERIN   CLIENT1             kyu       140.247.11.9        0         5
HR_DATA   CLIENT1             kyu       140.247.11.9        0         8
FIN_OFFIC UHALL-TEMP5         hadass    140.247.70.24       0         7
FIN_OFFIC IBM-5F0826A1BDE     nasson    140.247.70.28       0         5
FIN_OFFIC IBM-5F0826A1BDE     nasson    140.247.70.28       1017      4




                               Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
III. Security Considerations
          Too Much AUD$

• Problem: AUD$ is located in SYSTEM
  tablespace
• If AUDIT SESSION is enabled, DOS attack
  can fill up SYSTEM tablespace, bringing
  database to a complete halt
• An untended AUD$ can do the same thing over
  time
• Solution: Monitor size of AUD$
                  Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
   MANAGE_AUD$ package

• Job to check size of AUD$ and move rows out
  of SYS tablespace if AUD$ too large
• Write to alert log if AUD$ has to be cleaned
  out
• Procedures for quarterly log rotation




                   Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
Create tables to archive AUD$
• SYSTEM.AUD$_BU
• SYSTEM.AUD$_PREV_QUARTER

drop table system.aud$_bu;

create table system.aud$_bu
tablespace tools
as (select * from sys.aud$ where 1=2);

drop table system.aud$_prev_quarter;

create table system.aud$_prev_quarter
tablespace tools
as (select * from sys.aud$ where 1=2);

                       Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
        Rotate AUD$ quarterly
procedure quarterly_rotation
is

/* Procedure quarterly_rotation
 * Cleans out table system.aud$_prev_quarter
 * and repopulates it with the previous quarter's worth of audit
    info
 * Should be scheduled to run every quarter
 */

begin
  commit;
  set transaction use rollback segment rbs_bulk;
  delete from system.aud$_prev_quarter;
  insert into system.aud$_prev_quarter
  ( select * from sys.aud$ au
   where au.timestamp# < trunc(sysdate,'Q'));
  delete from sys.aud$ where timestamp# < trunc(sysdate,'Q') ;
  commit;
end quarterly_rotation;
                           Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
Use dbms_system.kdswrt() to log

 …
 delete from sys.aud$ where timestamp# < trunc(sysdate,'Q') ;
 commit;

  sys.dbms_system.ksdwrt(3,'ORA-AUDIT
  TRAIL: Quarterly rotation COMPLETE');
 sys.dbms_system.ksdwrt(3,'ORA-AUDIT
  TRAIL: This message brought to you by
  sys.manage_aud$');
 end quarterly_rotation ;




                            Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
            Check AUD$ size
procedure
keep_size_in_check
is
   rowCount number;
begin
   select count(*) into rowCount from sys.aud$ ;
   if rowCount > 50000
   then
     commit;
     set transaction use rollback segment rbs_bulk;
     insert into system.aud$_bu (select * from sys.aud$);
     delete from sys.aud$ ;
     commit;
     sys.dbms_system.ksdwrt (3,'ORA-AUDIT TRAIL: POSSIBLE
    DOS
     ATTACK!!!! AUD$ rows moved to AUD$_BU');
   end if;
end keep_size_in_check;
                       Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
    Poll AUD$ table regularly

declare
  myjob number;
begin
  dbms_job.submit(myjob,
     what =>
  ‘manage_aud$.keep_size_in_check;’,
     interval => ‘trunc(sysdate)+1/96’);
End;




                   Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
                Summary

• Oracle Auditing is good for auditing non-DDL
  events, and auditing DDL where a record of the
  data changed is not important
• AUDIT SESSION lets you track connections
  by IP, OS username, & terminal
• If DB audit trail is enabled, the size of AUD$
  must be managed


                   Oracle Auditing - Natalka Roshak - http://rdbms-insight.com
    Natalka Roshak
    Oracle and Sybase Database
 Administrator, Architect and Analyst

    http://rdbms-insight.com
http://toolkit.rdbms-insight.com

				
DOCUMENT INFO