samedi 14 juin 2025

Audit ...



1] Users connexions

1.1 Log users connexions

For User management purpose, I need to record information from the the last connexion to the Oracle instance for every users. The information are gathered locally in a table, in a real simple manner with the SYS_CONTEXT function ( formerly 9i USERENV ) :

INSTANCE ; USERNAME ; LAST_LOGin time ; MODULE ; SERVICE_NAME ; Machine ; OS_USER

INSTANCE_NAME The name of the instance.
MODULE The application name (module) set through the DBMS_APPLICATION_INFO package or OCI.
SERVICE_NAME The name of the service to which a given session is connected.
HOST Name of the host machine from which the client has connected.
OS_USER Operating system user name of the client process that initiated the database session.

Since we store only the last connexion data, no partitionned table needed.

Goal
Log DDLs actions
Version
10gR2
Trigger
AFTER LOGON ON DATABASE
BEFORE LOGOFF ON DATABASE
Function
SYS_CONTEXT to extract session infomation
Log as
SYSTEM

1.1.1 LOGON TRIGGER


-- 1/ create monitoring table

CREATE TABLE USERS_MON
(
  INSTANCE   VARCHAR2(16 BYTE)             NOT NULL,
  USERNAME   VARCHAR2(30 BYTE)             NOT NULL,
  LAST_LOG   DATE                          NOT NULL,
  MODULE     VARCHAR2(64 BYTE),
  SERVICE_NAME      VARCHAR2(64 BYTE),
  machine    VARCHAR2 (64 byte),
  OSUSER     VARCHAR2 (30 byte)
)
TABLESPACE SYSAUX ;

create unique index USERS_MON_PK on USERS_MON (INSTANCE, USERNAME) tablespace SYSAUX ;

ALTER TABLE USERS_MON ADD CONSTRAINT USERS_MON_PK PRIMARY KEY (INSTANCE, USERNAME) using index ;



-- 2/ create on logon trigger

CREATE OR REPLACE TRIGGER DB_LOGON
after logon on database
DECLARE
session_id number;
instanc      VARCHAR2 (16) := sys_context('USERENV', 'INSTANCE_NAME');
service      VARCHAR2 (64) := sys_context('USERENV', 'SERVICE_NAME');
moduleU      VARCHAR2 (48) := sys_context('USERENV', 'MODULE');
HOST   VARCHAR2 (64) := sys_context('USERENV', 'HOST');
OSUS   VARCHAR2 (30) := sys_context('USERENV', 'OS_USER');
begin
select sys_context('USERENV','SESSIONID') into session_id from dual;
IF session_id != 0 --ignore internal connections
then
-- insert logging info, merge
merge into users_mon using ( SELECT 1 FROM dual ) on (username = user)
   WHEN MATCHED THEN
   UPDATE SET instance = instanc , last_log  = sysdate , service_name = service , module = moduleU , machine = host , osuser = osus
   WHEN NOT MATCHED THEN
        INSERT (instance,username,last_log,service_name,module,machine,osuser)
        VALUES (instanc,user,sysdate,service,moduleU,host,osus) ;
end if;
end;
/


Query login table :


select * from users_mon order by 3 desc ;


USERNAME
LAST_LOG
MODULE
SERVICE_NAME
MACHINE
OSUSER
SCOTT
28/04/2011 16:42
oracle@srv-5 (TNS V1-V3)
SYS$USERS
srv-5
oracle
APP1
28/04/2011 16:41
JDBC Thin Client
SCH01.corp.fr
srv-prod-11.corp.fr
tomcat
APP2
28/04/2011 16:41
frmweb@srv.corp.fr
SAP.corp.fr
srv.corp.fr
oracle
USR0
28/04/2011 16:41
frmweb@srv-as-4.corp.fr
SYS$USERS
srv-as-4.corp.fr
oracle
APP3
28/04/2011 16:41
calcul@srv-6 (TNS V1-V3)
SYS$USERS
srv-6
hrt
APP4
28/04/2011 16:41
httpd@srv-web-4.corp.fr
SYS$USERS
srv-web-4.corp.fr
nobody
USR1
28/04/2011 16:41
java@srv.corp.fr
SAP.corp.fr
srv.corp.fr
oracle
APP5
28/04/2011 16:40
RTSDGN@srv-9 (TNS V1-V3)
HHR.corp.fr
srv-9
hhrv7
USR2
28/04/2011 16:40
frmweb@srv-as-2.corp.fr
database.corp.fr
srv-as-2.corp.fr
oracle
APP6
28/04/2011 16:40
MSACCESS.EXE
SYS$USERS
ADCORP\PC-07002501-ID
kraviola

DB_LOGON may be enhanced to include ‘alter session’ statements for specific needs.
Implied « grant alter session to system ; ».

  • Get Number of Connections Per Month :

select username , osuser , LDM , ceil(count(LDM)) CON_PER_MONTH from (
select username , upper(osuser) osuser , trunc(login_date,'MONTH') LDM
-- , case WHEN UNDO_KB > 100 THEN 'RW' ELSE 'R' END TYPE
from adb.users_mon where lower(module) like '%access%'
and login_date > sysdate-365/2 -- 6 months
and username in ('APPNAME_INT','MTB_2_INT')
order by 1,2,3
)
group by username , osuser , LDM order by 1,2,3

USERNAME     OSUSER LDM    CON_PER_MONTH

APPNAME_INT  UTIL1  01/04/2018   4
APPNAME_INT  UTIL1  01/05/2018   8

SELECT TRUNC(sysdate, 'YEAR') "New Year" FROM DUAL -- 01/01/2014
SELECT TRUNC(sysdate, 'MONTH') FROM DUAL -- 01/09/2014
SELECT TRUNC(sysdate) FROM DUAL -- 15/09/2014


  • keep only the last_log of users ( several rows, keep the last based on LOGIN_DATE column )

create view SYSTEM.USERS_MON as
select instance,username,login_date last_log,module,service_name,machine,osuser from ( select instance,username,login_date,module,service_name,machine,osuser,
ROW_NUMBER() OVER(PARTITION BY instance,username order by login_date desc ) rn
from ADB.USERS_MON order by 3 desc ) where rn=1 ;

1.1.2 LOGOFF TRIGGER

<UPDATE>
Add a few columns to the USERS_MON table and get more information like if the Session used UNDO segments and therefore did Modification in Database :

CREATE TABLE USERS_MON
(
  INSTANCE      VARCHAR2(16 BYTE)               NOT NULL,
  USERNAME      VARCHAR2(30 BYTE)               NOT NULL,
  LOGIN_DATE    DATE                            NOT NULL,
  MODULE        VARCHAR2(48 BYTE),
  SERVICE_NAME  VARCHAR2(64 BYTE),
  MACHINE       VARCHAR2(64 BYTE),
  OSUSER        VARCHAR2(30 BYTE),
  SESS_AUD      NUMBER,
  UNDO_KB       INTEGER,
  LOGOFF_DATE   DATE,
  CLIENT_I      VARCHAR2(64 BYTE)
)

CREATE INDEX USERS_MON_I01 ON USERS_MON (SESS_AUD)

CREATE OR REPLACE TRIGGER DB_LOGON
after logon on database
DECLARE
session_id number;
status_database VARCHAR2 (32);
instanc VARCHAR2 (16) := sys_context('USERENV', 'INSTANCE_NAME');
service VARCHAR2 (64) := sys_context('USERENV', 'SERVICE_NAME');
moduleU VARCHAR2 (48) := sys_context('USERENV', 'MODULE');
HOST VARCHAR2 (64) := sys_context('USERENV', 'HOST');
OSUS VARCHAR2 (30) := sys_context('USERENV', 'OS_USER');
CLTI VARCHAR2 (64) := sys_context('USERENV', 'CLIENT_INFO');
begin
-- Cas Base avec Standby
 select database_role into status_database from v$database;
 if status_database = 'PRIMARY' then
  select sys_context('USERENV','SESSIONID') into session_id from dual;

IF session_id != 0 and ( user not in ('DBSNMP') ) -- ignore internal connections and DBSNMP
then
 -- A/ insert logging info
    INSERT into ADB.USERS_MON (instance,username,login_date,service_name,module,machine,osuser,sess_aud,undo_kb,client_i)
       VALUES (instanc,user,sysdate,service,moduleU,host,osus,session_id,0,clti) ;

end if;
end;
/


CREATE OR REPLACE TRIGGER DB_LOGOFF
before logoff on database
DECLARE
session_id number;
undo_used_kb integer ;
begin
select sys_context('USERENV','SESSIONID') into session_id from dual;
IF session_id != 0 and ( user not in ('DBSNMP') ) -- ignore internal connections and DBSNMP
then
 -- insert loggoff info
select round(to_number(b.value)/1024,1) "KB_Undo" into undo_used_kb
from v$statname a inner join v$sesstat b on a.statistic# = b.statistic#
inner join v$mystat c on b.sid = c.sid
where a.name = 'undo change vector size' and c.statistic# = 0 ;

 UPDATE APP.USERS_MON set UNDO_KB = undo_used_kb , logoff_date = sysdate where sess_aud = session_id ;
end if;
end;
/

INSTANCE
USERNAME
LOGIN_DATE
MODULE
SERVICE_NAME
DB
USER
15/12/2015 11:45
kslstart.exe
APP
DB
USER
15/12/2015 11:44
java@srv.corp (TNS V1-V3)
APP
DB
USER
15/12/2015 11:43
httpd@srv.corp (TNS V1-V3)
APP
DB
USER
15/12/2015 11:43
java@srv.corp (TNS V1-V3)
APP
DB
USER
15/12/2015 11:43
kslstart.exe
APP
DB
USER
15/12/2015 11:43
frmweb@srv.corp (TNS V1-V3)
APP
DB
USER
15/12/2015 11:43
w3wp.exe
APP

MACHINE
OSUSER
SESS_AUD
UNDO_KB
LOGOFF_DATE
CLIENT_I
PC
user
542132333
0
15/12/2015 11:45

srv.corp
oracle
542132299
5
15/12/2015 11:44

srv.corp
oracle
542132277
18
15/12/2015 11:43

srv.corp
oracle
542132275
14
15/12/2015 11:43

PC2
user2
542132276
0
15/12/2015 11:43

srv.corp
Oracle
542132274
0


IIS APPPOOL
user3
542132270
0




1.1.3 Every 2 Hours, update the Consumed UNDO Per Session column

- When a session is using a graphical tool, such as « TOAD » or « SQL Developper » and may modify Data outside of the scope of an « official » application.
- To store the data, even if the session stops abruptely and doesn’t trigger the LOGOFF code above.

Create a procedure

CREATE OR REPLACE procedure ADB.CD13_ORA_CHK_UNDO_SESS AUTHID CURRENT_USER AS

-- select to_char(trunc(sysdate,'HH24'), 'HH24:MI') into cur_hour from dual ;
-- job every hours, check undo_kb from « out-of-applications » sessions
-- context : session_id == v$session : audsid
CURSOR c1 is
WITH SSIIDD AS
( select sid , audsid , username from v$session where audsid in ( select sess_aud from adb.users_mon where -- username = 'SYSTEM' and (
( lower(MODULE) like '%sqlplus%' and lower(machine) like '%novea%' ) OR lower(MODULE) like '%toad%' OR lower(MODULE) like '%developer%' OR lower(MODULE) like '%excel%' OR lower(MODULE) like '%access%' )
-- )
)
select round(to_number(s.value)/1024,1) KB_UNDO , i.AUDSID
from v$sesstat s, v$statname n , SSIIDD i
where name = 'undo change vector size' and s.statistic#=n.statistic# and s.sid = i.sid
order by 2 ;

BEGIN

-- avoid update on old rows ( > SYSDATE-1 )
For Cur IN C1 Loop
 UPDATE ADB.USERS_MON set UNDO_KB = Cur.KB_UNDO , UNDO_I = SYSDATE where sess_aud = Cur.AUDSID and LOGIN_DATE > SYSDATE-1 ;
 commit ;
END LOOP;

END ;
/

Use Scheduler

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'CD13_ORA_CHK_UNDO_SESS'
      ,start_date      => TO_TIMESTAMP_TZ('2015/12/15 05:00:00.000000 +01:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
      ,repeat_interval => 'FREQ=DAILY;BYHOUR=00,02,04,06,08,10,12,14,16,18,20,22;BYMINUTE=0;'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'DECLARE BEGIN ADB.CD13_ORA_CHK_UNDO_SESS; END;'
      ,comments        => 'AUC 15/12/2015: Calcul UNDO Sess'
    );

Get results

1.2 Log failed user connection

Goal
Log Failed connections
Version
10gR2
Audit
create session [ Default ]
Log as
SYSTEM


SQL> alter system set audit_trail=DB scope=spfile ;

SQL> audit create session whenever not successful;
Audit succeeded.


Check , return codes are
0 : OK
1017 : invalid username/password
28000 : the account is locked


select os_username , terminal , username , userhost , timestamp , action_name , returncode from dba_audit_trail where action_name = 'LOGON' and username = '<SCHEMA_USER>' order by timestamp desc


OS_USERNAME
TERMINAL
USERNAME
USERHOST
TIMESTAMP
ACTION_NAME
RETURNCODE
09/04/2013






oracle
unknown
<SCHEMA_USER>
sr-pprod-1.corp.fr
08/04/2013 17:25
LOGON
28000
oracle
unknown
<SCHEMA_USER>
sr-pprod-1.corp.fr
08/04/2013 17:25
LOGON
28000
oracle
unknown
<SCHEMA_USER>
sr-pprod-1.corp.fr
08/04/2013 17:25
LOGON
1017
oracle
unknown
<SCHEMA_USER>
sr-pprod-1.corp.fr
08/04/2013 17:25
LOGON
1017
oracle
unknown
<SCHEMA_USER>
sr-pprod-1.corp.fr
08/04/2013 17:25
LOGON
1017
oracle
unknown
<SCHEMA_USER>
sr-pprod-1.corp.fr
08/04/2013 17:25
LOGON
1017
oracle
unknown
<SCHEMA_USER>
sr-pprod-1.corp.fr
08/04/2013 17:25
LOGON
1017
oracle
unknown
<SCHEMA_USER>
sr-pprod-1.corp.fr
08/04/2013 17:25
LOGON
1017
oracle
unknown
<SCHEMA_USER>
sr-pprod-1.corp.fr
08/04/2013 17:25
LOGON
1017
oracle
unknown
<SCHEMA_USER>
sr-pprod-1.corp.fr
08/04/2013 17:25
LOGON
1017
oracle
unknown
<SCHEMA_USER>
sr-pprod-1.corp.fr
08/04/2013 17:25
LOGON
1017
oracle
unknown
<SCHEMA_USER>
sr-pprod-1.corp.fr
08/04/2013 17:25
LOGON
1017
BI_USER
unknown
<SCHEMA_USER>
PC-08013808-ID
08/04/2013 17:24
LOGON
0
oracle
unknown
<SCHEMA_USER>
sr-pprod-1.corp.fr
08/04/2013 17:24
LOGON
1017
oracle
unknown
<SCHEMA_USER>
sr-pprod-1.corp.fr
08/04/2013 17:24
LOGON
1017
oracle
unknown
<SCHEMA_USER>
sr-pprod-1.corp.fr
08/04/2013 17:24
LOGON
1017
oracle
unknown
<SCHEMA_USER>
sr-pprod-1.corp.fr
08/04/2013 17:24
LOGON
1017
oracle
unknown
<SCHEMA_USER>
sr-pprod-1.corp.fr
08/04/2013 17:24
LOGON
1017
BI_USER
unknown
<SCHEMA_USER>
PC-08013808-ID
08/04/2013 17:23
LOGON
0
BI_USER
unknown
<SCHEMA_USER>
PC-08013808-ID
08/04/2013 17:21
LOGON
0
BI_USER
unknown
<SCHEMA_USER>
PC-08013808-ID
08/04/2013 17:20
LOGON
0
BI_USER
unknown
<SCHEMA_USER>
PC-08013808-ID
08/04/2013 17:14
LOGON
0
BI_USER
unknown
<SCHEMA_USER>
PC-08013808-ID
08/04/2013 17:11
LOGON
0


2] Log every DDL orders

Who drop my table ?
Easier than log mining, I may audit every DDLs on my database from now on and find out what’s going on :

Goal
Log DDLs actions
Version
10gR2
Trigger
AFTER DDL ON DATABASE
Log as
SYSTEM

I log every DDL except 'CREATE','COMMENT','ANALYZE'


CREATE TABLE ddl_log (
user_name     VARCHAR2(30),
ddl_date      DATE,
ddl_type      VARCHAR2(30),
object_type   VARCHAR2(18),
owner         VARCHAR2(30),
object_name   VARCHAR2(128))
TABLESPACE SCH_DAT ;

CREATE OR REPLACE TRIGGER ddl_trig
AFTER DDL ON DATABASE
 DECLARE
 oper ddl_log.ddl_type%TYPE;
 BEGIN
  SELECT ora_sysevent INTO oper FROM dual;
  IF oper NOT IN ('CREATE','COMMENT','ANALYZE') THEN
   INSERT INTO ddl_log
    (user_name, ddl_date, ddl_type, object_type, owner, object_name)
   VALUES
    (ora_login_user, SYSDATE, ora_sysevent,ora_dict_obj_type, ora_dict_obj_owner,ora_dict_obj_name);
  END IF;
END ddl_trig;
/


Who did it ? Well, I did !!


select * from ddl_log order by ddl_date desc ;

-- select ddl_type , count(1) from ddl_log group by ddl_type ;


USER_NAME
DDL_DATE
DDL_TYPE
OBJECT_TYPE
OWNER
OBJECT_NAME
SYSTEM
27/04/2011 06:00
DROP
TABLE
IUS
BIN$od...+g==$0
SYSTEM
27/04/2011 06:00
DROP
VIEW
IUS
APP_SDI_DR_OS
SYSTEM
27/04/2011 06:00
DROP
TABLE
IUS
AD_MACHINE
SYSTEM
27/04/2011 06:00
ALTER
TABLE
IUS
AD_USER


More complete :


CREATE TABLE DDL_LOG
(
  OS_USER       VARCHAR2(255 BYTE),
  HOST          VARCHAR2(255 BYTE),
  MODULE        VARCHAR2(64 BYTE),
  SERVICE_NAME  VARCHAR2(64 BYTE),
  USER_NAME     VARCHAR2(30 BYTE),
  DDL_DATE      DATE,
  DDL_TYPE      VARCHAR2(30 BYTE),
  OBJECT_TYPE   VARCHAR2(18 BYTE),
  OWNER         VARCHAR2(30 BYTE),
  OBJECT_NAME   VARCHAR2(128 BYTE)
) ;

CREATE OR REPLACE TRIGGER DDL_TRIG
AFTER DDL ON DATABASE
DECLARE
 oper SCHEMA.ddl_log.ddl_type%TYPE;
 obje SCHEMA.ddl_log.object_name%TYPE;
 moduleU VARCHAR2 (64) := sys_context('USERENV', 'MODULE');
 service VARCHAR2 (64) := sys_context('USERENV', 'SERVICE_NAME');
 BEGIN
  SELECT ora_sysevent INTO oper FROM dual;
  SELECT ora_dict_obj_name INTO obje FROM dual;
  IF oper NOT IN ('CREATE','COMMENT','ANALYZE') AND obje not like 'SYS_EXPORT_SCHEMA%' and obje not like 'ORA_TEMP_1_DS_%' THEN
   INSERT INTO DDL_LOG
    (os_user, host, module , service_name , user_name, ddl_date, ddl_type, object_type, owner, object_name)
   VALUES
    (sys_context('USERENV','OS_USER'), sys_context('USERENV','HOST'), moduleU , service , ora_login_user, SYSDATE, ora_sysevent, ora_dict_obj_type, ora_dict_obj_owner, ora_dict_obj_name);
  END IF;
END DDL_TRIG;
/


3] Audit on object level

3.1 Pre-requisite

Why is the inserted record missing in this table ??

Some Examples About Auditing And Output Of Auditing [ID 167293.1]
        BY SESSION/BY ACCESS
BY SESSION causes Oracle Database to write a single record for all SQL statements of the same type issued in the same session.
BY ACCESS causes Oracle to write one record for each access.

  • Pre-requisite

alter system set audit_trail = 'DB_EXTENDED' scope = spfile ;

SQL> sho parameter audit_trail
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB_EXTENDED

3.2 By Session

  • Options

SQL> select * from all_def_audit_opts;

ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA

These correspond to the following object options:
alter audit comment delete grant index insert lock rename select update reference execute

  • Audit ALL

connect smh
SQL> audit all on SMH.DOSSIER by session ;

SQL> connect SYSTEM
audit select,delete,insert,update on APP01.T01 ;

Default : BY SESSION

SQL> select * from dba_obj_audit_opts where owner='APP01' and object_name='T01';

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -----------------
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
APP01                            BIN_DOCS                       TABLE
-/- -/- -/- S/S -/- -/- S/S -/- -/- S/S S/S -/- -/- -/- -/- -/- -/-


OWNER
OBJECT_NAME
OBJECT_TYPE
ALT
AUD
COM
DEL
GRA
IND
INS
LOC
REN
SEL
UPD
REF
EXE
CRE
REA
WRI
FBK
IAN
ITBU_SITE
TABLE
S/S
S/S
S/S
S/S
S/S
S/S
S/S
S/S
S/S
S/S
S/S
-/-
-/-
-/-
-/-
-/-
S/S
IUS
ADCORP_MACHINES_SITES
VIEW
-/-
S/S
S/S
S/S
S/S
-/-
S/S
S/S
S/S
S/S
S/S
-/-
-/-
-/-
-/-
-/-
S/S
IUS
ADCORP_DEPLOY_APP_SITES
VIEW
-/-
S/S
S/S
S/S
S/S
-/-
S/S
S/S
S/S
S/S
S/S
-/-
-/-
-/-
-/-
-/-
S/S

  • Reset

connect smh
SQL> NOAUDIT ALL on SMH.DOSSIER ;
Noaudit succeeded.

SQL> connect SYSTEM
noaudit select,delete,insert,update on dgc.bin_docs ;

§  See results


select userid,ntimestamp#,ses$actions,sqltext,sqlbind from sys.aud$ ;



select count(1) from dgc.bin_docs ;

delete APP01.T01 where 1=2 ;
commit ;

SQL> connect SYSTEM
select timestamp, username, priv_used, ses_actions from dba_audit_object where obj_name='T01' and owner='APP01';


TIMESTAMP
USERNAME
PRIV_USED
SES_ACTIONS
10/09/2012 10:36
SYSTEM
SELECT ANY TABLE
---------S------
10/09/2012 10:41
SYSTEM
DELETE ANY TABLE
---S------------

The characters are:
  - for none
  S for success
  F for failure
  B for both

More information :


select timestamp, username, owner , obj_name, priv_used , decode(ses_actions,
       '---S------------','DELETE',
       '------S---------','INSERT',
       '---------S------','SELECT',
       '----------S-----','UPDATE',
       '---S--S--S------','DELETE/INSERT/SELECT',
       '---S--S--SS-----','DELETE/INSERT/SELECT/UPDATE',
       '------S--S------','INSERT/SELECT',
       '------S--SS-----','INSERT/SELECT/UPDATE',
       '---------SS-----','SELECT/UPDATE',
       'UNKNOWN') ACTION
--ses_actions
from dba_audit_object where obj_name in ('ITBU_SITE','ADCORP_DEPLOY_APP_SITES','ADCORP_MACHINES_SITES')
order by timestamp desc


TIMESTAMP
USERNAME
OWNER
OBJ_NAME
PRIV_USED
ACTION
16/04/2014 10:55
APEX_PUBLIC_USER
IAN
ITBU_SITE

SELECT
16/04/2014 09:44
APEX_PUBLIC_USER
IAN
ITBU_SITE

SELECT
16/04/2014 09:28
DI
IAN
ITBU_SITE

SELECT

3.3 By Access

SQL> audit delete,insert,update on APP.CITY by access ;

SQL> col obj_name for a20
SQL> col SES_ACTIONS for a30
SQL> select timestamp, obj_name, username, action_name, ses_actions, returncode from dba_audit_object where obj_name='CITY' and owner='APP';

TIMESTAMP            OBJ_NAME    USERNAME      ACTION_NAME  SES_ACTIONS   RETURNCODE
-------------------- -------------------- ------------------------------ ---------
04-DEC-2012 09:57:13 CITY     SMITH            UPDATE                     0
04-DEC-2012 09:57:37 CITY     SMITH            INSERT                     0
04-DEC-2012 09:57:42 CITY     SMITH      DELETE                    0

If audit is set by SESSION, the column DBA_AUDIT_OBJECT.SES_ACTIONS contains a  value that explains if any of the DML action succeeded or not within the session .
Refer NOTE:1019326.102 'SES_ACTIONS in DBA_AUDIT_OBJECT' for more details on the possible values.
If audit is set by ACCESS, the column DBA_AUDIT_OBJECT.SES_ACTIONS is empty.

select timestamp, sql_text , sql_bind from dba_audit_object where owner = 'USER' and action_name = 'DELETE' order by 1 desc

TIMESTAMP
SQL_TEXT
SQL_BIND
PRIJA

Variables
30/01/2015 11:18
delete from t1 where t1_id=:1
 #1(6):103784
30/01/2015 11:18
delete from t2 where t2_id=:1
 #1(6):103784
30/01/2015 11:18
delete from t3 where t3_id=:1 and name=:2
 #1(6):103755 #2(36): Revues_2012 TRAV SOC.xls

3.4 11G+

Starting with Oracle 11g the BY SESSION clause is obsolete. This is documented in the Database Security Guide :
" The BY SESSION clause of the AUDIT statement now writes one audit record for every audited event. In previous releases, BY SESSION wrote one audit record for all SQL statements or operations of the same type that were executed on the same schema objects in the same user session. Now, both BY SESSION and BY ACCESS write one audit record for each audit operation. In addition, there are separate audit records for LOGON and LOGOFF events. "

When AUD$ becomes very Huge :

truncate table aud$;

CAUTION: SYS.AUD$ is the only SYS object that should ever be directly modified

Watch for item in the audit trail :

select USERNAME, action, action_name, count(*) from dba_audit_trail
  group by  USERNAME, action, action_name order by USERNAME, action;

Execute Monthly truncate :

-- AUDIT TABLE 7 GB
select sum(bytes/1024/1024) from dba_segments where owner = 'SYS' and segment_name = 'AUD$' -- 7766 MB

-- execute a single Delete every Month
-- as SYS : truncate table aud$
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'TRUNC_AUDIT_TRAIL',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'BEGIN EXECUTE IMMEDIATE ''TRUNCATE TABLE aud$'' ; END;',
   start_date           => to_timestamp('09/12/2014 10:15:00', 'dd/mm/yyyy hh24:mi:ss'),
   repeat_interval      => 'FREQ=MONTHLY;BYDAY=WED;BYHOUR=10;BYMINUTE=00;',
   enabled              =>  TRUE,
   comments             => 'SCH 091214: truncate table aud$;');
END;
/

EXEC dbms_scheduler.run_job('TRUNC_AUDIT_TRAIL');

select sum(bytes/1024/1024) from dba_segments where owner = 'SYS' and segment_name = 'AUD$' -- 0,06 MB

4] Audit Oracle Schemas to find out if they are used

Goal
Find last Access on Tables
Version
10gR2
SELECT uses
SYS.col_usage$, dba_objects, dba_tab_columns
DML uses
Log as
SYSTEM

4.1 READ Access

Last Access was more than 6 Months :

-- 1/ READ
-- Tables most recent SELECT access
-- More than 6 Months
-- create table SCH.APP_NOT_READ_AFTER ( OWNER varchar2(32) , WAS_NOT_READ_AFTER date ) ;

insert into SCH.APP_NOT_READ_AFTER
select owner , Most_Recent_Select_Access_Date from (
SELECT distinct b.owner, max(a.timestamp) over ( partition by b.owner ) Most_Recent_Select_Access_Date
-- dba_tab_columns.table_name, --         dba_tab_columns.column_name, --SYS.col_usage$.*
 FROM SYS.col_usage$ a, dba_objects, dba_tab_columns b
 WHERE obj# = dba_objects.object_id
  AND b.owner = dba_objects.owner
  AND b.table_name = dba_objects.object_name
  AND intcol# = b.column_id
  AND dba_objects.owner not in ('APEX_030200','APPQOSSYS','CTXSYS','DBSNMP','EXFSYS','FLOWS_FILES','MDSYS','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','SCOTT','SYS','SYSMAN','SYSTEM','WMSYS','XDB')
--     AND object_name = 'T'
)
where Most_Recent_Select_Access_Date < sysdate-(31*6)
ORDER BY owner -- intcol#

commit ;

OWNER  WAS_NOT_READ_AFTER
APP1   19/03/2015 14:51:01
APP2   18/03/2015 10:37:01
APP3   13/02/2015 08:53:26
APP4   16/01/2015 15:34:12

4.2 WRITE Access

--select owner , sum(bytes/1024/1024) from dba_segments group by owner order by 2 desc

-- 2/ WRITE
-- Tables most recent WRITE access
-- QUERY = select min(first_time) WAS_NOT_Modified_AFTER from v$log_history where first_change# >= ( select max(ora_rowscn) from HGS.AGENT ) ;
-- create table SCH.APP_NOT_MODIFIED_AFTER ( OWNER varchar2(32) , TABLE_NAME varchar2(32) , WAS_NOT_MODIFIED_AFTER date ) ;

truncate table SCH.APP_NOT_MODIFIED_AFTER ;

-- select min(first_time) WAS_NOT_Modified_AFTER from v$log_history -- where first_change# >= ( select max(ora_rowscn) from ACT.MAIL_LIGNES )
-- 24/06/2015 23:48:44 // 4 MONTHS

set serveroutput on
DECLARE
 OWNER varchar2(32) ;
 TABLE_NAME varchar2(32) ;
 lmdwb date ;
begin
        dbms_output.disable;        -- *** Line added ***
        dbms_output.enable(100000); -- *** Line added ***
for i in ( select owner , table_name from dba_tables where table_name not like 'QTSF_CHAIN%' and -- owner = 'SSD' and
owner not in
('APEX_030200','APEX_040000','APPQOSSYS','CTXSYS','DBSNMP','EXFSYS','FLOWS_FILES','MDSYS','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','SCOTT','SYS','SYSMAN','SYSTEM','WMSYS','XDB')
order by 1,2 )
 loop
  BEGIN
--      DBMS_OUTPUT.PUT_LINE( ' '||i.owner||'.'||i.table_name||' ' ) ;
    execute immediate 'select min(first_time) from v$log_history where first_change# >= ( select max(ora_rowscn) from "'||i.owner||'"."'||i.table_name||'" )' into lmdwb ;
--      DBMS_OUTPUT.PUT_LINE( ' '||i.owner||'.'||i.table_name||' : '||lmdwb   ) ;
        if lmdwb is not null -- Table has zero row
        then
                execute immediate 'insert into SCH.APP_NOT_MODIFIED_AFTER values ( '''||i.owner||''' , '''||i.table_name||''' , '''||lmdwb||''' )' ;
                --      DBMS_OUTPUT.PUT_LINE( 'insert into SCH.WAS_NOT_MODIFIED_AFTER values ( '''||i.owner||''' , '''||i.table_name||''' , '''||lmdwb||''' )' ) ;
        end if ;
  END;
  commit ;
 end loop;
end;
/

4.3 Concat the Results

Into a table


Publié par orACsd à 08:00
Libellés :