Oracle Administration Manage Database Auditing (traditional)

From Robs_Wiki
Revision as of 11:17, 5 March 2020 by Qadmin wiki (talk | contribs) (Check the user logon audit trail)
Jump to: navigation, search

Audit logon

Audit user logon

First view the current auditing options:

SQL> SELECT audit_option, success, failure FROM dba_stmt_audit_opts; 

no rows selected

Now audit user logon

SQL> audit create session;

Audit succeeded.

Check the new audit settings:

SQL> SELECT audit_option, success, failure FROM dba_stmt_audit_opts;

AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------
CREATE SESSION                           BY ACCESS  BY ACCESS

Check the user logon audit trail

The returncode <> 0 reports an error status, which can be further examined.

COL OSUSER FOR A20
COL DBUSER FOR A20
COL TERMINAL FOR A15
SET LINESIZE 150
SET PAGESIZE 1000
SELECT os_username "OSUSER",
     username "DBUSER",
     terminal,
     returncode,
     TO_CHAR(timestamp,   'DD-MON-YYYY HH24:MI:SS') LOGON_TIME,
     TO_CHAR(logoff_time, 'DD-MON-YYYY HH24:MI:SS') LOGOFF_TIME
FROM dba_audit_session; 

OSUSER               DBUSER               TERMINAL        RETURNCODE LOGON_TIME                    LOGOFF_TIME
-------------------- -------------------- --------------- ---------- ----------------------------- -----------------------------
robla                ROBLA                DZ07083                  0 08-MAR-2019 14:23:35          08-MAR-2019 16:29:25
oracle               ROBLA                pts/5                    0 12-FEB-2019 10:14:15          12-FEB-2019 09:14:48
oracle               ROBLA                pts/5                    0 12-FEB-2019 10:16:56          12-FEB-2019 09:32:27
prduser              PRDOWNER             unknown                  0 12-FEB-2019 10:34:28          12-FEB-2019 09:59:13
prduser              PRDOWNER             unknown                  0 12-FEB-2019 10:34:30          12-FEB-2019 09:59:13
robla                ROBLA                DZ07083                  0 12-FEB-2019 14:07:21          12-FEB-2019 13:43:30
robla                ROBLA                DZ07083                  0 12-FEB-2019 14:07:36          12-FEB-2019 13:07:37
robla                ROBLA                DZ07083                  0 08-MAR-2019 14:23:43          08-MAR-2019 13:23:45
oracle               PRDOWNER             pts/4                    0 08-MAR-2019 14:28:17          08-MAR-2019 13:32:40

9 rows selected.

= Check the user audit trail for the last 100 failed logon attempts in which a wrong password was provided

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

SQL> select * from (
select * from dba_audit_trail where returncode=1017 order by timestamp desc
)
where rownum < 101;

== Audit tables ==
Turn on the auditing:
<pre>
audit select on robla.TBL_REC_TEST;
audit all on robla.AUDIT_TEST;

Verify audit settings of the table

SQL> select * from dba_obj_audit_opts where object_name = 'TBL_REC_TEST';

OWNER                OBJECT_NAME          OBJECT_TYPE          ALT       AUD       COM       DEL       GRA       IND       INS       LOC       REN       SEL       UPD       REF EXE       CRE       REA       WRI       FBK
-------------------- -------------------- -------------------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --- --------- --------- --------- --------- ---------
ROBLA                TBL_REC_TEST         TABLE                -/-       -/-       -/-       -/-       -/-       -/-       -/-       -/-       -/-       A/A       -/-       -/- -/-       -/-       -/-       -/-       -/-

Check the audit logs

SQL> select username, obj_name, action_name, to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') action_date from dba_audit_trail;

USERNAME             OBJ_NAME             ACTION_NAME                  ACTION_DATE
-------------------- -------------------- ---------------------------- -----------------------------
ROBLA                TBL_REC_TEST         SELECT                       04-APR-2019 07:32:37

Remark:

  • SYS operations are only logged if parameter audit_sys_operations is true.
  • SYS operations are not stored in the audit trail but in the audit_dump_dest. See example below:
. . .
Tue Oct  1 15:48:12 2019 +01:00
LENGTH : '200'
ACTION :[46] 'delete from robla.audit_test where rownum < 12'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/2'
STATUS:[1] '0'
DBID:[10] '2537534894'
. . .

Turn off the table auditing

SQL> noaudit all on robla.audit_test;

Noaudit succeeded.

Verify:

SQL> select * from dba_obj_audit_opts where object_name = 'AUDIT_TEST';

no rows selected

Audit proxy users

SQL> AUDIT UPDATE TABLE, INSERT TABLE, DELETE TABLE BY robla ON BEHALF OF prod_schema;

SQL> AUDIT UPDATE TABLE, INSERT TABLE, DELETE TABLE BY robla BY ACCESS;

SQL> AUDIT execute any procedure by robla on behalf of prod_schma;

SQL> AUDIT execute any procedure by robla by access;

Purge the Audit Trail

First check the current size

SQL> select owner, segment_name, segment_type, tablespace_name, bytes/1024/124 size_MB 
from dba_segments where segment_name = 'AUD$';

OWNER        SEGMENT_NAME                   SEG_TYPE   TABLESPACE_NAME       SIZE_MB
------------ ------------------------------ ---------- ------------------ ----------
SYS          AUD$                           TABLE      SYSTEM             5879.74194
SQL> select count(*) from sys.aud$;

  COUNT(*)
----------
   4285807
SQL> SELECT MIN(NTIMESTAMP#) OLDEST_RECORD FROM AUD$;

OLDEST_RECORD
---------------------------------------------------------------------------
31-DEC-10 11.30.01.389115 PM

Check how many records will be deleted when we keep 6 Month history

SQL> select /*+ parallel(a,8) */ count(1) from sys.aud$ a
where ntimestamp#<to_date(to_char(add_months(sysdate, -6),'YYYYMM')||'01','YYYYMMDD'); 

  COUNT(1)
----------
   2998983

Create the history table

SQL> alter session force parallel ddl;

SQL> create table aud$_hist01 tablespace sysaux parallel (degree 8) as select * from aud$;

Table created.

Elapsed: 00:00:35.44

Purge the audit table

SQL> !cat clean_audit.sql

exec dbms_application_info.set_client_info ('SYS_AUD_DELETE');
set serveroutput on size 100000 timing on
 declare
 TYPE aud_tab_type IS TABLE OF rowid INDEX BY PLS_INTEGER;
 t_aud aud_tab_type;
 l_commits number:=0;
 begin
 loop
 select /*+ parallel(a,16) */ rowid bulk collect into t_aud from sys.aud$ a
 where ntimestamp#<to_date(to_char(add_months(sysdate, -6),'YYYYMM')||'01','YYYYMMDD') and rownum<=100000;
 exit when t_aud.count=0 ;
 forall i in t_aud.first .. t_aud.last delete /*+ parallel(a,16) */ from sys.aud$ a where rowid = t_aud(i) ;
 commit;
 l_commits:=l_commits+1;
 end loop;
 dbms_output.put_line('completed.  commits='||to_char(l_commits));
 end;
 /
SQL> @clean_audit.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
completed.  commits=30

PL/SQL procedure successfully completed.

Elapsed: 00:06:47.22

Check the table settings afterwards

SQL> SELECT MIN(NTIMESTAMP#) OLDEST_RECORD FROM AUD$;

OLDEST_RECORD
---------------------------------------------------------------------------
01-MAY-19 12.01.32.231430 AM

Elapsed: 00:00:12.17

SQL> select count(*) from sys.aud$;

  COUNT(*)
----------
   1286824

Elapsed: 00:00:06.32

Shrink the table and check the size

Check the size before the shrink operation:

select owner, segment_name, segment_type, tablespace_name, bytes/1024/124 size_MB 
from dba_segments where segment_name = 'AUD$';

OWNER        SEGMENT_NAME                   SEG_TYPE   Tablespace                SIZE_MB
------------ ------------------------------ ---------- ---------------------- ----------
SYS          AUD$                           TABLE      SYSTEM                 6474.32258

Create a copy table:

SQL> create table aud_temp tablespace archive_data nologging
as select /*+ parallel(a,8) */ a.* from sys.aud$ a;

Table created.

Elapsed: 00:00:11.32

truncate the sys.aud$ table. This will reset the High Water Mark and free up the storage

SQL> truncate table sys.aud$ drop storage;      

Table truncated.

Elapsed: 00:00:00.12

Insert the records back in:

SQL> insert /*+ append parallel(a,8) */ into sys.aud$ a
  2  select /*+ parallel(b,8) */ * from sys.aud_temp;

1286824 rows created.

Elapsed: 00:00:11.58
SQL> commit;

Commit complete.

check the new size of the table:

SQL> select owner, segment_name, segment_type, tablespace_name, bytes/1024/124 size_MB 
from dba_segments where segment_name = 'AUD$';

OWNER        SEGMENT_NAME                   SEG_TYPE   Tablespace                SIZE_MB
------------ ------------------------------ ---------- ---------------------- ----------
SYS          AUD$                           TABLE      SYSTEM                 1981.93548

Drop the temporary table:

SQL> drop table sys.aud_temp;

Table dropped.

Elapsed: 00:00:00.53