DATAFILE ‘/u05/oradata/PDEV/ddl_logging01.dbf’ SIZE 100M reuse

EXTENT MANAGEMENT LOCAL

UNIFORM SIZE 64K;

drop user auditdb cascade;

create user auditdb identified by auditdb

The Default Auditing is Good for most environments. Test it now with:

sqlplus sys/PDEV11@PDEV as sysdba

SET LINESIZE 300

SET PAGESIZE 100

select max(to_char(timestamp, ‘Dy DD-Mon-YYYY HH24:MI:SS’)) as “Time of Change” from dba_audit_trail;

select max(timestamp)-min(timestamp) as “No of Days of Changes” from dba_audit_trail;

SELECT to_char(timestamp, ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time of Change”, SUBSTR(action_name, 1, 14) type_of_action, SUBSTR(obj_name, 1, 12) Object_name, SUBSTR(owner, 1, 8) owner, SUBSTR(username, 1, 10) username, SUBSTR(userhost, 1, 12) userhost, SUBSTR(SQL_TEXT, 1, 50) SQL_TEXT from dba_audit_trail 

where username not in (‘DBSNMP‘, ‘SYSMAN‘, ‘SYS’, ‘SYSTEM’)

and owner not in (‘DBSNMP‘, ‘SYSMAN‘, ‘SYS’, ‘SYSTEM’) and rownum <= 100

ORDER BY timestamp;

Set the init.ora parameter:

Add SQL Text  by  setting audit_trail=db_extended.

Enable or Set Database Auditing Options

Audit SYS Operations

vi initPDEV.ora

audit_sys_operations=TRUE  # ‘Begin auditing SYS’

audit_trail=’db_extended’  # ‘records changes + SQL statements to sys.aud$’

sqlplus “/ as sysdba”

shutdown immediate;

 — Save current spfile:

CREATE PFILE=’/u01/app/oracle/admin/PDEV/pfile/init_SpFile_June4.ora’ FROM SPFILE=’spfilePDEV.ora’;

create spfile=’spfilePDEV.ora’ FROM pfile=’/u01/app/oracle/admin/PDEV/pfile/initPDEV.ora’;

startup;

show parameter audit

NAME                                 TYPE        VALUE

———————————— ———– ——————————

audit_file_dest                      string      /u01/app/oracle/admin/PDEV/adump

audit_sys_operations                  boolean     TRUE

audit_syslog_level                   string

audit_trail                          string      DB_EXTENDED

Moving Audit Table Out From SYSTEM Tablespace

When moving the audit tables you need to make sure the tablespace has enough space to store the audit tables. The command will fail even though the tablespace’s data files’ auto extend is set.

Create a dedicated tablespace for the audit data and change the location of the audit trail. If this is not done AUD$ will remain in the SYSAUX tablespace.

Calculate Size of Tablespace:

select segment_name,segment_type,bytes/1024/1024 MB

from dba_segments where segment_type=’TABLE’ and segment_name=’AUD$‘;

col PARAMETER_NAME FOR a30

col PARAMETER_VALUE FOR a15

col AUDIT_TRAIL FOR a20

SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL

FROM DBA_AUDIT_MGMT_CONFIG_PARAMS

WHERE audit_trail LIKE ‘%AUDIT%’  ;

select * from DBA_AUDIT_MGMT_CONFIG_PARAMS;

PARAMETER_NAME                 PARAMETER_VALUE AUDIT_TRAIL

—————————— ————— ——————–

DB AUDIT TABLESPACE            SYSAUX          STANDARD AUDIT TRAIL

DB AUDIT TABLESPACE            SYSAUX          FGA AUDIT TRAIL

AUDIT FILE MAX SIZE            10000           OS AUDIT TRAIL

AUDIT FILE MAX SIZE            10000           XML AUDIT TRAIL

AUDIT FILE MAX AGE             5               OS AUDIT TRAIL

AUDIT FILE MAX AGE             5               XML AUDIT TRAIL

DB AUDIT CLEAN BATCH SIZE      10000           STANDARD AUDIT TRAIL

DB AUDIT CLEAN BATCH SIZE      10000           FGA AUDIT TRAIL

OS FILE CLEAN BATCH SIZE       1000            OS AUDIT TRAIL

OS FILE CLEAN BATCH SIZE       1000            XML AUDIT TRAIL

10 rows selected.

— drop tablespace audit_data including contents and datafiles;

CREATE tablespace audit_data datafile ‘/u05/oradata/PDEV/audit_data1.dbf’ 

SIZE 500M autoextend ON NEXT 100M maxsize 10G;

BEGIN

  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(

    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

    AUDIT_TRAIL_LOCATION_VALUE => ‘AUDIT_DATA’

  );

END;

/

— move the FGA_LOG$ TABLE:

BEGIN

 DBMS_AUDIT_MGMT.set_audit_trail_location(

 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,

 audit_trail_location_value => ‘AUDIT_DATA‘);

END;

/

commit;

ERROR at line 1:

ORA-46267: Insufficient space in ‘AUDIT_DATA’ tablespace, cannot complete

operation

ORA-06512: at “SYS.DBMS_AUDIT_MGMT”, line 1659

ORA-06512: at line 2

Solution:

 1.
TRUNCATE TABLE AUD$;

 2. Move – with above package

You can move both tables in one step by using AUDIT_TRAIL_DB_STD constant for audit_trail_type parameter 

Check the result

SELECT table_name, tablespace_name

FROM dba_tables

WHERE table_name IN (‘AUD$’, ‘FGA_LOG$’);

Initialize the audit infrastructure. 

In this example I’ve initialized the standard audit and set a default interval of 120 days (120×24 ) or 10 days = 24×10=240.

— Default_cleanup_interval:   Enter the default time interval,
in hours, after which the cleanup procedure should be called. The minimum value is 1 and the maximum is 999.

BEGIN

  DBMS_AUDIT_MGMT.INIT_CLEANUP(

    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

    DEFAULT_CLEANUP_INTERVAL => 240 /*hours*/

  );

END;

/

commit;

— Check Default Clean Up Interval has been Set and Cleanup is Initialized

COLUMN parameter_name FORMAT A30

COLUMN parameter_value FORMAT A20

COLUMN audit_trail FORMAT A20

SELECT * FROM dba_audit_mgmt_config_params WHERE PARAMETER_NAME = ‘DEFAULT CLEAN UP INTERVAL’;

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL

—————————— ——————– ——————–

DEFAULT CLEAN UP INTERVAL      180                  STANDARD AUDIT TRAIL

Review the audit settings

col PARAMETER_NAME FOR a30

col PARAMETER_VALUE FOR a15

col AUDIT_TRAIL FOR a20

SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL 

FROM DBA_AUDIT_MGMT_CONFIG_PARAMS 

WHERE audit_trail = ‘STANDARD AUDIT TRAIL’;

PARAMETER_NAME                 PARAMETER_VALUE AUDIT_TRAIL

—————————— ————— ——————–

DEFAULT CLEAN UP INTERVAL      240              STANDARD AUDIT TRAIL

DB AUDIT TABLESPACE            AUDIT_DATA       STANDARD AUDIT TRAIL

DB AUDIT CLEAN BATCH SIZE      10000            STANDARD AUDIT TRAIL

col segment_name FOR a10

SELECT owner,segment_name,tablespace_name FROM dba_segments WHERE segment_name =‘AUD$’;

OWNER                          SEGMENT_NA TABLESPACE_NAME

—————————— ———- ——————————

SYS                            AUD$       AUDIT_DATA

Ok, the audit infrastructure has been setup and configured. Now lets enable audit for certain system privilege and objects. The first part of the following audit statements have been taken from $ORACLE_HOME/rdbms/admin/secconf.sql. This script is used in 11g to enable audit during database creation.

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 ALTER any TABLE BY access;

audit CREATE any TABLE BY access;

audit DROP any TABLE BY access;

audit CREATE any PROCEDURE BY access;

audit DROP any PROCEDURE BY access;

audit ALTER any PROCEDURE BY access;

audit GRANT any privilege BY access;

audit GRANT any object privilege BY access;

audit GRANT any ROLE BY access;

audit audit system BY access;

audit CREATE external job BY access;

audit CREATE any job BY access;

audit CREATE any library BY access;

audit CREATE public DATABASE link BY access;

audit exempt access policy BY access;

audit ALTER USER BY access;

audit CREATE USER BY access;

audit ROLE BY access;

audit CREATE SESSION BY access;

audit DROP USER BY access;

audit ALTER DATABASE BY access;

audit ALTER system BY access;

audit ALTER profile BY access;

audit DROP profile BY access;

audit DATABASE link BY access;

audit system audit BY access;

audit profile BY access;

audit public synonym BY access;

audit system GRANT BY access;

 audit DELETE ON sys.aud$;

audit ALTER ON DEFAULT;

audit GRANT ON DEFAULT;

CHECK:

To see what are the enabled statement auditing options one can use the DBA_STMT_AUDIT_OPTS view:

select USER_NAME,AUDIT_OPTION from DBA_STMT_AUDIT_OPTS;

AUDIT ALL;

set linesize 300

SELECT count(*) FROM dba_stmt_audit_opts ;

SELECT * FROM dba_stmt_audit_opts ORDER BY user_name,audit_option;

no rows selected 

    —  turns off all statement audit options

NOAUDIT ALL; 

To see what are the audited system privileges one can use the DBA_PRIV_AUDIT_OPTS view:

select user_name, privilege from dba_priv_audit_opts;

Turns ON all privilege audit options: 

AUDIT ALL PRIVILEGES; 

SELECT * FROM DBA_PRIV_AUDIT_OPTS order by 3; 

NOAUDIT ALL PRIVILEGES;

NOAUDIT EXEMPT ACCESS POLICY;

To see what are the enabled object auditing options:

select OWNER, OBJECT_NAME from DBA_OBJ_AUDIT_OPTS;

    Turn ON all object audit options on the emp table

AUDIT ALL ON emp;

NOAUDIT ALL ON emp;  

    Turn off all default object audit options 

AUDIT ALL ON DEFAULT; 

SELECT * FROM DBA_OBJ_AUDIT_OPTS;

SELECT owner, OBJECT_NAME from DBA_OBJ_AUDIT_OPTS;

    Turn off all default object audit options 

NOAUDIT ALL ON DEFAULT; 

    Turn on all session audit options 

AUDIT session;

AUDIT session BY scott, hr;

    Turn off all SESSION audit options 

NOAUDIT session;

NOAUDIT session BY scott, hr;

Check the user sessions Audited:

USERNAME

select count(USERNAME) FROM DBA_AUDIT_SESSION ;

SELECT USERNAME, USERHOST, TERMINAL, ACTION_NAME, LOGOFF_TIME FROM DBA_AUDIT_SESSION where
rownum
<=12;

SELECT USERNAME, LOGOFF_TIME, LOGOFF_LREAD, LOGOFF_PREAD, LOGOFF_LWRITE, LOGOFF_DLOCK FROM DBA_AUDIT_SESSION;

The  privileges that can be audited can be seen in system_privilege_map.

select * from system_privilege_map;

Audit trail housekeeping

Create daily archive Timestamp job. This job will be a regular dbms_scheduler job.

Set-up or Create a Job to Move the Last Archive Timestamp Forward Each Day

BEGIN

  DBMS_SCHEDULER.CREATE_JOB (

    job_name   => ‘DAILY_AUDIT_ARCHIVE_TIMESTAMP‘,

    job_type   => ‘PLSQL_BLOCK’,

    job_action => ‘BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => 

                   DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,LAST_ARCHIVE_TIME => sysdate-10); END;’, 

    start_date => sysdate,

    —start_date
=>
TO_TIMESTAMP_TZ(‘2015/08/20 08:00:00.000000 +01:00’,‘yyyy/mm/dd hh24:mi:ss.ff tzr’), 

    repeat_interval => ‘FREQ=HOURLY;INTERVAL=24’, 

    enabled    =>  TRUE,

    comments   => ‘Create an archive timestamp’

  );

END;

/

— Check Last Archive Timestamp Values are Set

SELECT * FROM dba_audit_mgmt_last_arch_ts ;

How to drop a program or Job:

BEGIN

–remove program         

—SYS.DBMS_SCHEDULER.DROP_PROGRAM(program_name=>
‘STATISTICS_COLUMNS_PROG’,FORCE=>TRUE);

–remove job

SYS.DBMS_SCHEDULER.DROP_JOB (job_name =>
‘DAILY_AUDIT_ARCHIVE_TIMESTAMP’);

END
;

Create daily purge job — Set-up Automated Purge Job

BEGIN

  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(

    AUDIT_TRAIL_TYPE           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

    AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours */,

    AUDIT_TRAIL_PURGE_NAME     => ‘Daily_Audit_Purge_Job’,

    USE_LAST_ARCH_TIMESTAMP    => TRUE

  );

END;

/

commit;

The defined AUDIT JOB’s are then visible in
DBA_AUDIT_MGMT_CLEANUP_JOBS.

col JOB_NAME FOR a30

col JOB_FREQUENCY FOR a40

SELECT JOB_NAME,JOB_STATUS,AUDIT_TRAIL,JOB_FREQUENCY FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;

JOB_NAME               JOB_STAT AUDIT_TRAIL          JOB_FREQUENCY

———————- ——– ——————– ————————

DAILY_AUDIT_PURGE_JOB  ENABLED  STANDARD AUDIT TRAIL FREQ=HOURLY;INTERVAL=24

— Check the DBMS Scheduler Jobs are Configured.

As we can see in dba_scheduler_jobs, DBMS_SCHEDULER.CREATE_JOB is just creating another scheduler job.

col next_run_date FOR a50

SELECT owner,job_name FROM DBA_SCHEDULER_JOBS WHERE job_name IN (‘PURGE_ALL_AUDIT_TRAILS’,’MOVE_LAST_TIMESTAMP_FORWARD’);

SELECT job_name, next_run_date, state, enabled FROM dba_scheduler_jobs WHERE job_name LIKE ‘%AUDIT%’;

JOB_NAME                       NEXT_RUN_DATE                                      STATE           ENABL

—————————— ————————————————– ————— —–

DAILY_AUDIT_ARCHIVE_TIMESTAMP  31-MAY-11 01.46.58.000000 PM +02:00                SCHEDULED       TRUE

DAILY_AUDIT_PURGE_JOB          31-MAY-11 01.49.34.900000 PM EUROPE/VIENNA         SCHEDULED       TRUE

Conclusion

For new Oracle 11g database auditing is enabled by default. Unfortunately Oracle does not setup a housekeeping. To make sure, that you do not end up with a full AUDIT_TRAIL you have to either switch off auditing or define some housekeeping jobs. With DBMS_AUDIT_MGMT this is a pretty easy job. It is that easy, that it is worth to think about having some kind of rolling audit window. Define a short retention time to save disk space, but long enough to have access to the audit information of the last hours or days. You may then be able to answer questions like “Did anybody change something?”, “I can not access XY but I could yesterday…”. When enabling audit for sys operation you will also be able to review what user SYS did all day long. Depending on the amount of audit data it will be quite a task to create nice audit reports.

Listing Audit Records

The following query lists audit records generated by statement and object audit options:

SELECT * FROM DBA_AUDIT_OBJECT;

To Stop Auditing:

SELECT  * FROM DBA_STMT_AUDIT_OPTS; 

NOAUDIT ALL; — turns off all statement audit options

NOAUDIT ALL PRIVILEGES;

NOAUDIT ALL ON DEFAULT;

NOAUDIT session;

NOAUDIT session BY scott, hr;

NOAUDIT DELETE ON scott.emp;

NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;

SELECT  * FROM DBA_STMT_AUDIT_OPTS;

SELECT count(*) FROM DBA_OBJ_AUDIT_OPTS;

SELECT * FROM DBA_OBJ_AUDIT_OPTS;

SELECT * FROM DBA_PRIV_AUDIT_OPTS;

SELECT count(*) FROM dba_audit_trail;

Query Audit data:

sqlplus sys/sys@PDEV as sysdba

/*  All Data is stored in  aud$

     select count(*) from aud$;

How big is my AUD$ table:

analyze table AUD$ compute statistics ;

SET LINESIZE 300

clear breaks

select blocks, empty_blocks, avg_space, num_freelist_blocks from user_tables where table_name = ‘AUD$’;

select NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN, NUM_ROWS, NUM_FREELIST_BLOCKS from all_tables where table_name = ‘AUD$’;

select SUBSTR(segment_name, 1, 12) segment_name,segment_type,bytes/1024/1024 “Size_in_MB” from dba_segments where segment_type=’TABLE’ and segment_name=’AUD$’;

     truncate table aud$;

*/

TEST AUDITING

sqlplus sys/ofD111@PDEV as sysdba

create user cho identified by cho;

grant resource, create session to cho;

sqlplus cho/cho@PDEV

create table audit_test (id number);

insert into audit_test values (1212);

update audit_test set id = 9900 where id = 1212;

delete from audit_test where id=1212;

drop
table audit_test;

create table t ( col1 number, col2 char(1000) default ‘*’ );

insert into t (col1) select rownum from all_tables where rownum < 50;

analyze table t compute statistics ;

select NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN, NUM_ROWS, NUM_FREELIST_BLOCKS from all_tables where table_name = ‘T’;

select sum(vsize(rowid)), sum(vsize(col1)), sum(vsize(col2)), avg(vsize(col1)), avg(vsize(col2)) from t ;

drop table t;

commit;

exit

sqlplus sys/ofD111@PDEV as sysdba

select count(*) from
dba_audit_trail;

select count(*) from
audit$;

SET LINESIZE 300

SET PAGESIZE 100

— select max(to_char(timestamp, ‘Dy DD-Mon-YYYY HH24:MI:SS’)) as “Time of Change” from dba_audit_trail;

SELECT to_char(timestamp, ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time of Change”, SUBSTR(action_name, 1, 14) type_of_action, SUBSTR(obj_name, 1, 12) Object_name, SUBSTR(owner, 1, 8) owner, SUBSTR(username, 1, 10) username, SUBSTR(userhost, 1, 12) userhost, SUBSTR(SQL_TEXT, 1, 50) SQL_TEXT from dba_audit_trail 

where username not in (‘DBSNMP‘, ‘SYSMAN‘)

and owner not in (‘DBSNMP‘, ‘SYSMAN‘)

ORDER BY timestamp;

COLUMN username FORMAT A8

COLUMN obj_name FORMAT A10

COLUMN action_name FORMAT A12

COLUMN sql_text FORMAT A37

SELECT count(*) FROM
dba_audit_trail;

SELECT username,obj_name,action_name, sql_text FROM
dba_audit_trail

— WHERE username = ‘SCOTT’

ORDER BY timestamp;

How to audit sys into an OS file owned by root