BY SESSION Specify BY SESSION if you want Oracle Database to write a single record for all SQL statements of the same type issued and operations of the same type executed on the same schema objects in the same session. BY ACCESS Specify BY ACCESS if you want Oracle Database to write one record for each audited statement and operation. WHENEVER SUCCESSFUL/NOT SUCCESSFUL Specify WHENEVER SUCCESSFUL to audit only SQL statements and operations that succeed. Specify WHENEVER NOT SUCCESSFUL to audit only statements and operations that fail or result in errors. If you omit this clause, then Oracle Database performs the audit regardless of success or failure.
SELECT
TO_CHAR(TIMESTAMP,'MM/DD HH24:MI') TIMESTAMP,
SUBSTR(OS_USERNAME,1,20) OS_USERNAME,
SUBSTR(USERNAME,1,20) USERNAME,
SUBSTR(TERMINAL,1,20) TERMINAL,
ACTION_NAME,
RETURNpre
FROM
SYS.DBA_AUDIT_SESSION
WHERE
USERNAME LIKE 'USERNAME%'
AND TIMESTAMP BETWEEN SYSDATE-7 AND SYSDATE
ORDER BY
TIMESTAMP DESC;
The RETURNpre column is the number associated with the ORA- error pre that was returned to the client computer:
What are we auditing?
SELECT * FROM DBA_STMT_AUDIT_OPTS where user_name = 'A157584'
What can we audit?
Select * from audit_actions
Turn off auditing options
SQL> noaudit insert table; SQL> noaudit execute procedure; SQL> noaudit select table; Noaudit succeeded.
Last logon � CIB
select username, max(logon_time) from ORACLE.SUCCESSFUL_LOGINS group by username order by username;
Failed logon with password
col os_username form a15 col username form a15 col userhost form a15 select os_username, username, userhost, to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') logon_time, action_name, returnpre from dba_audit_session where returnpre =1017;
set lines 90
set pages 60
ttitle 'PDGPRD01: Monthly Report Oracle Viewer Usage/Users'
break on year on month skip 1 on report
compute sum of tally on month report
SELECT to_char(timestamp,'YYYY') year,
to_char(timestamp,'MON') month,
a.username username,
nvl(b.name, b.name) name,
COUNT (*) tally
FROM dba_audit_session a, a157584.users b
WHERE a.username NOT IN ('A157584', 'SYSTEM', 'SYS', 'DBSNMP','PDG','PDGDEV','PDGSC','PUBLIC','PDG5C3T#1','A','OEM')
AND a.username = b.username(+)
and to_char(timestamp,'YYYY') = '2011'
GROUP BY a.username, b.name, to_char(timestamp,'YYYY'), to_char(timestamp,'MON'), to_char(timestamp,'MM')
ORDER BY 1,to_char(timestamp,'MM')
/
select * from dba_audit_session where to_char(timestamp,'YYYY-MM-DD') between '2015-11-10' and '2015-11-17'
set lines 300
set pages 25
col OS_USERNAME form a20
col userhost form a20
select os_username, username, userhost, to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') logon_time, action_name, returnpre from dba_audit_session;
The SET_AUDIT_TRAIL_LOCATION procedure allows you to alter the location of the standard and/or fine-grained database audit trail. It does not currently allow the alteration of the OS audit trail, although the documentation suggests this may happen in future. The procedure accepts two parameters.
� AUDIT_TRAIL_TYPE: They type of audit trail that is to be moved.
� AUDIT_TRAIL_LOCATION_VALUE: The tablespace the audit trail tables should be moved to.
The AUDIT_TRAIL_TYPE parameter is specified using one of three constants.
� DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail (AUD$).
� DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail (FGA_LOG$).
� DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trails.
Let's see this in action. First check the current location of the audit trail tables.
CONN / AS SYSDBA
SELECT table_name, tablespace_name
FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
AUD$ SYSTEM
FGA_LOG$ SYSTEM
SQL>
Next, create a new tablespace to hold the audit trail.
CREATE TABLESPACE audit_aux DATAFILE '/u01/app/oracle/oradata/DB11G/audit_aux01.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M;
Then we move the standard audit trail to the new tablespace.
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'AUDIT_AUX');
END;
/
PL/SQL procedure successfully completed.
SQL>
-- Check locations.
SELECT table_name, tablespace_name
FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
AUD$ AUDIT_AUX
FGA_LOG$ SYSTEM
SQL>
Next we move the fine-grained audit trail.
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => 'AUDIT_AUX');
END;
/
PL/SQL procedure successfully completed.
SQL>
alter system set audit_trail=db_extended scope=spfile; select count(*) from DBA_AUDIT_TRAIL where USERNAME = 'EXIMADMIN'; audit update table, delete table, insert table by EXIMADMIN by access; noaudit select table, update table, delete table, insert table by EXIMADMIN by access; select ACTION_NAME from dba_audit_trail where USERNAME = 'EXIMADMIN'; 14:52:15 sys@EXMPPD01:PRI>noaudit select table by EXIMADMIN; Noaudit succeeded. select count(*) from DBA_AUDIT_TRAIL where USERNAME = 'EXIMADMIN' and action = 3 select action_name, audit_option from DBA_AUDIT_STATEMENT where grantee = 'EXIMADMIN'
Not knowing exactly what you did -- I cannot point out what you did wrong. However, I can show you how to do this correctly.
I simply: 1. set audit_trail=true; and restarted. 2. ran the following: SQL> @connect "/ as sysdba" SQL> set termout off SQL> GET afiedt.buf NOLIST SQL> set termout on SQL> SQL> truncate table aud$; Table truncated. SQL> audit table by access; Audit succeeded. SQL> audit alter table by access; Audit succeeded. SQL> SQL> @connect / SQL> set termout off SQL> GET afiedt.buf NOLIST SQL> set termout on SQL> create table audit_tab ( x int ); Table created. SQL> select username, action_name from dba_audit_trail; USERNAME ACTION_NAME ------------------------------ --------------------------- OPS$TKYTE CREATE TABLE SQL> SQL> alter table audit_tab add y date; Table altered. SQL> select username, action_name from dba_audit_trail; USERNAME ACTION_NAME ------------------------------ --------------------------- OPS$TKYTE CREATE TABLE OPS$TKYTE ALTER TABLE SQL> SQL> SQL> drop table audit_tab; Table dropped. SQL> select username, action_name from dba_audit_trail; USERNAME ACTION_NAME ------------------------------ --------------------------- OPS$TKYTE CREATE TABLE OPS$TKYTE ALTER TABLE OPS$TKYTE DROP TABLE
As you can see -- the create and drop (covered by the AUDIT TABLE BY ACCESS) as well as the ALTER (covered by the AUDIT ALTER TABLE BY ACCESS) were recorded. I can only assume that in your attempts -- you entered many different audit commands and that is why the create table audit is hanging about. To see what is in effect:
SQL> select * from dba_priv_audit_opts;
no rows selected
SQL> select * from dba_stmt_audit_opts;
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
ALTER TABLE BY ACCESS BY ACCESS
TABLE BY ACCESS BY ACCESS
that shows I have table and alter table and if I:
SQL> @connect "/ as sysdba" SQL> set termout off SQL> GET afiedt.buf NOLIST SQL> set termout on SQL> SQL> noaudit table; Noaudit succeeded. SQL> noaudit alter table; Noaudit succeeded. SQL> truncate table aud$; Table truncated. SQL> SQL> @connect / SQL> set termout off SQL> GET afiedt.buf NOLIST SQL> set termout on SQL> create table audit_tab ( x int ); Table created. SQL> select username, action_name from dba_audit_trail; no rows selected SQL> SQL> alter table audit_tab add y date; Table altered. SQL> select username, action_name from dba_audit_trail; no rows selected SQL> SQL> SQL> drop table audit_tab; Table dropped. SQL> select username, action_name from dba_audit_trail; no rows selected
I'm no longer auditing. >
16:18:52 sys@EXMPPD01:PRI> select * from DBA_STMT_AUDIT_OPTS where user_name = 'EXIMADMIN';
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE ------------------------------ ------------------------------ ---------------------------------------- ---------- ---------- EXIMADMIN INSERT TABLE BY ACCESS BY ACCESS EXIMADMIN UPDATE TABLE BY ACCESS BY ACCESS EXIMADMIN DELETE TABLE BY ACCESS BY ACCESS
Auditing will be done at statement level. Statements that can be audited are found in STMT_AUDIT_OPTION_MAP.
SQL> audit table by scott;
Audit records can be found in DBA_STMT_AUDIT_OPTS.
SQL> select * from DBA_STMT_AUDIT_OPTS;
Auditing will be done at object level. These objects can be audited: tables, views, sequences, packages, stored procedures and stored functions.
SQL> audit insert, update, delete on scott.emp by hr;
Audit records can be found in DBA_OBJ_AUDIT_OPTS.
SQL> select * from DBA_OBJ_AUDIT_OPTS;
Auditing will be done at privilege level. All system privileges that are found in SYSTEM_PRIVILEGE_MAP can be audited.
SQL> audit create tablespace, alter tablespace by all;
Specify ALL PRIVILEGES to audit all system privileges. Audit records can be found in DBA_PRIV_AUDIT_OPTS.
SQL> select * from DBA_PRIV_AUDIT_OPTS;
truncate table sys.aud$; select table_name,tablespace_name from dba_tables where table_name='AUD$';
-- Recolcate aud$
begin dbms_audit_mgmt.set_audit_trail_location( audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std, audit_trail_location_value => 'SYSAUX'); end; /
-- Initialize the cleanup, and to define the oldest record to leave in the table (Here 30 days):
begin
DBMS_AUDIT_MGMT.INIT_CLEANUP(audit_trail_type=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
default_cleanup_interval=>720);
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => SYSTIMESTAMP-90);
end;
/
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(audit_trail_type=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_purge_interval => 168,
audit_trail_purge_name=> 'PURGE_AUD_JOB',
use_last_arch_timestamp => TRUE);
END;
/
SELECT * FROM dba_audit_mgmt_config_params
BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => SYSTIMESTAMP);
END;
/
SELECT * FROM dba_audit_mgmt_last_arch_ts;
set echo off feedback off verify off trimspool on
set pagesize 50000 linesize 300
column filename new_val filename
select '/tmp/audit_trail.xls' filename from dual;
SET MARKUP HTML ON
spool &filename
select * from dba_audit_trail
where EXTENDED_TIMESTAMP between
to_Timestamp('20/04/2020 00:00:00.413164', 'dd/mm/yyyy hh24:mi:ss.FF') and
to_Timestamp('26/04/2020 23:59:26.413164', 'dd/mm/yyyy hh24:mi:ss.FF');
spool off
SET MARKUP HTML OFF
clear columns breaks
set feedback on trimspool on
Minimalistic Oracle Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations. Friday, November 1, 2013 How to audit CREATE SESSION Audit all session connecting to the database: audit create session [ whenever successful | not successful ]; To audit sessions created by a specific user: audit create session by SCOTT [ whenever successful | not successful ]; If you omit the condition "whenever successful/not successful", Oracle will audit all connections, regardless of their success or failure. To disable auditing: noaudit create session; noaudit create session by scott; To verify that the auditing options has indeed been set: SELECT * FROM DBA_PRIV_AUDIT_OPTS WHERE USER_NAME = 'SCOTT'; Result: USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE SCOTT CREATE SESSION BY ACCESS BY ACCESS The view DBA_PRIV_AUDIT_OPTS gives you "current system privileges being audited across the system and by user". Note that a NULL value in the user_name column of DBA_PRIV_AUDIT_OPTS means system-wide auditing.
How to use sqlerrm to reveal the meaning of audit information
As an example, the audit information may look like the following:
ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';
SELECT
os_username,
userhost,
timestamp,
returnpre
FROM dba_audit_session
WHERE action_name = 'LOGON'
AND returnpre > 0
AND trunc(timestamp) = to_date('08.07.2013', 'DD.MM.YYYY')
AND username = 'TSF'
ORDER BY timestamp DESC;
OS_USERNAME USERHOST TIMESTAMP RETURNpre
----------- ----------- ------------------- ----------
billyb CHM06071130 08.07.2013 12:24:07 1017
billyb CHM06071130 08.07.2013 10:06:06 28000
You can use the sqlerrm keyword to print the meaning of a return pre, like this:
exec dbms_output.put_line(sqlerrm(-1017)) ;
exec dbms_output.put_line(sqlerrm(-28000)) ;
These commands will yield:
ORA-01017: invalid username/password; logon denied
ORA-28000: the account is locked
set lines 300
set pages 66
col user_name for a25
cOL audit_trail FORMAT A20
COL last_archive_ts FORMAT A40
ttitle 'Last Archive Timestamp'
SELECT audit_trail, last_archive_ts FROM dba_audit_mgmt_last_arch_ts;
col cleanup_time form a40
ttitle 'Clean-up Events over the Past Week'
select * from (SELECT * FROM DBA_AUDIT_MGMT_CLEAN_EVENTS order by cleanup_time desc) where rownum < =7;
ttitle 'DBA Privilege Options'
select user_name, privilege, success, failure from dba_priv_audit_opts order by user_name, privilege;
ttitle 'DBA Statement Options'
select user_name, audit_option, success, failure from dba_stmt_audit_opts order by user_name, audit_option;
col parameter_name form a40
col parameter_value form a40
ttitle 'Configuration Parameters'
SELECT * FROM dba_audit_mgmt_config_params where audit_trail = 'STANDARD AUDIT TRAIL';
col JOB_FREQUENCY form a30
col job_name form a30
ttitle 'Return information on cleanup jobs'
select * from dba_audit_mgmt_cleanup_jobs where audit_trail = 'STANDARD AUDIT TRAIL';
prompt 'Clean-up Initialization Done?"
SET SERVEROUTPUT ON
BEGIN
IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
DBMS_OUTPUT.put_line('YES');
ELSE
DBMS_OUTPUT.put_line('NO');
END IF;
END;
/
ttitle off