Audit | SysDBA

alter system set audit_trail=TRUE scope=spfile;

1	alter system set audit_trail=TRUE scope=spfile;

AUDIT_TRAIL =  "none | os | db | db,extended | xml | xml,extended"

1	AUDIT_TRAIL = "none \| os \| db \| db,extended \| xml \| xml,extended"

AUDIT_TRAIL Options

*none or false – Audit is off (this is the default setting) *db or true – Activates the Audit. The audits are written to SYS.AUD$ *db, extended – Writes to SYS.AUD$ and SQL_TEXT *xml – Auditing writes to an xml file in the operating system *xml, extended – Writes to both the xml file and SQL_TEXT *os – Writes to the destination configured in AUDIT_FILE_DEST into a file with a .aud extension AUDIT_FILE_DEST – should be set to OS level audits Statements that can be viewed on the object level:

alter
audit
comment
delete
flashback
grant
index
insert
lock
rename
select
update

shutdown immediate

startup

shutdown immediate

startup

AUDIT DELETE ON SCOTT.EMP1 BY ACCESS

1	AUDIT DELETE ON SCOTT.EMP1 BY ACCESS

Auditing all the tables in the Scott schema

select 'audit delete on SCOTT.'||table_name||' BY ACCESS ;' from user_tables

1	select 'audit delete on SCOTT.'\|\|table_name\|\|' BY ACCESS ;' from user_tables

Disabling the audit

select 'NOAUDIT delete on SCOTT.'||table_name||' ;' from user_tables ;

1	select 'NOAUDIT delete on SCOTT.'\|\|table_name\|\|' ;' from user_tables ;

Example

 select 'audit delete on SCOTT.'||table_name||' BY ACCESS ;' from user_tables;

1	select 'audit delete on SCOTT.'\|\|table_name\|\|' BY ACCESS ;' from user_tables;

'AUDITDELETEONSCOTT.'||TABLE_NAME||'BYACCESS;' ----------------------------------------------------------------
audit delete on SCOTT.DEPT BY ACCESS ;
audit delete on SCOTT.EMP BY ACCESS ;
audit delete on SCOTT.BONUS BY ACCESS ;
audit delete on SCOTT.SALGRADE BY ACCESS ;
audit delete on SCOTT.GAME_PLAYER BY ACCESS ;
audit delete on SCOTT.EMP1 BY ACCESS ;

'AUDITDELETEONSCOTT.'||TABLE_NAME||'BYACCESS;' ----------------------------------------------------------------

audit delete on SCOTT.DEPT BY ACCESS ;

audit delete on SCOTT.EMP BY ACCESS ;

audit delete on SCOTT.BONUS BY ACCESS ;

audit delete on SCOTT.SALGRADE BY ACCESS ;

audit delete on SCOTT.GAME_PLAYER BY ACCESS ;

audit delete on SCOTT.EMP1 BY ACCESS ;

delete from scott.emp1 where empno=7521;

1	delete from scott.emp1 where empno=7521;

To view transactions that have been audited

SELECT "OWNER", "OBJ_NAME", "USERNAME", "ACTION_NAME", "TIMESTAMP"

FROM "SYS"."DBA_AUDIT_OBJECT"

SELECT "OWNER", "OBJ_NAME", "USERNAME", "ACTION_NAME", "TIMESTAMP"

FROM "SYS"."DBA_AUDIT_OBJECT"

COLUMN username FORMAT A10

COLUMN owner FORMAT A10

COLUMN obj_name FORMAT A10

COLUMN extended_timestamp FORMAT A35

SELECT username,

extended_timestamp,

owner,

obj_name,

action_name

FROM dba_audit_trail

WHERE owner = 'SCOTT'

ORDER BY timestamp;

COLUMN username FORMAT A10

COLUMN owner FORMAT A10

COLUMN obj_name FORMAT A10

COLUMN extended_timestamp FORMAT A35

SELECT username,

extended_timestamp,

owner,

obj_name,

action_name

FROM dba_audit_trail

WHERE owner = 'SCOTT'

ORDER BY timestamp;

COLUMN db_user FORMAT A10

COLUMN object_schema FORMAT A10

COLUMN object_name FORMAT A10

COLUMN extended_timestamp FORMAT A35

SELECT db_user,

extended_timestamp,

object_schema,

object_name,

action

FROM v$xml_audit_trail

WHERE object_schema = 'SCOTT'

ORDER BY extended_timestamp;

COLUMN db_user FORMAT A10

COLUMN object_schema FORMAT A10

COLUMN object_name FORMAT A10

COLUMN extended_timestamp FORMAT A35

SELECT db_user,

extended_timestamp,

object_schema,

object_name,

action

FROM v$xml_audit_trail

WHERE object_schema = 'SCOTT'

ORDER BY extended_timestamp;

Example

select 'test on test '||table_name||' ' from user_tables

1	select 'test on test '\|\|table_name\|\|' ' from user_tables

SELECT view_name

FROM dba_views

WHERE view_name LIKE 'DBA%AUDIT%'

ORDER BY view_name;

VIEW_NAME

------------------------------

DBA_AUDIT_EXISTS

DBA_AUDIT_OBJECT

DBA_AUDIT_POLICIES

DBA_AUDIT_POLICY_COLUMNS

DBA_AUDIT_SESSION

DBA_AUDIT_STATEMENT

DBA_AUDIT_TRAIL

DBA_COMMON_AUDIT_TRAIL

DBA_FGA_AUDIT_TRAIL

DBA_OBJ_AUDIT_OPTS

DBA_PRIV_AUDIT_OPTS

DBA_REPAUDIT_ATTRIBUTE

DBA_REPAUDIT_COLUMN

DBA_STMT_AUDIT_OPTS

SELECT view_name

FROM dba_views

WHERE view_name LIKE 'DBA%AUDIT%'

ORDER BY view_name;

VIEW_NAME

------------------------------

DBA_AUDIT_EXISTS

DBA_AUDIT_OBJECT

DBA_AUDIT_POLICIES

DBA_AUDIT_POLICY_COLUMNS

DBA_AUDIT_SESSION

DBA_AUDIT_STATEMENT

DBA_AUDIT_TRAIL

DBA_COMMON_AUDIT_TRAIL

DBA_FGA_AUDIT_TRAIL

DBA_OBJ_AUDIT_OPTS

DBA_PRIV_AUDIT_OPTS

DBA_REPAUDIT_ATTRIBUTE

DBA_REPAUDIT_COLUMN

DBA_STMT_AUDIT_OPTS

Example

CREATE USER omer IDENTIFIED BY password

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE temp

QUOTA UNLIMITED ON users;

GRANT connect TO omer;

GRANT create table, create procedure TO omer;

CREATE USER omer IDENTIFIED BY password

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE temp

QUOTA UNLIMITED ON users;

GRANT connect TO omer;

GRANT create table, create procedure TO omer;

AUDIT ALL BY OMER BY ACCESS;

1	AUDIT ALL BY OMER BY ACCESS;

CREATE TABLE heybe (

id NUMBER

);

INSERT INTO heybe (id) VALUES (1);

UPDATE heybe SET id = id;

SELECT * FROM heybe;

DELETE FROM heybe;

DROP TABLE heybe;

CREATE TABLE heybe (

id NUMBER

);

INSERT INTO heybe (id) VALUES (1);

UPDATE heybe SET id = id;

SELECT * FROM heybe;

DELETE FROM heybe;

DROP TABLE heybe;

SELECT username,

extended_timestamp,

owner,

obj_name,

action_name

FROM dba_audit_trail

where owner='omer'

ORDER BY timestamp desc;

SELECT username,

extended_timestamp,

owner,

obj_name,

action_name

FROM dba_audit_trail

where owner='omer'

ORDER BY timestamp desc;

USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME ACTION_NAME

OMER 07.06.2011 15:34 OMER HEYBE DROP TABLE

OMER 07.06.2011 15:34 OMER HEYBE CREATE TABLE

USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME ACTION_NAME

OMER 07.06.2011 15:34 OMER HEYBE DROP TABLE

OMER 07.06.2011 15:34 OMER HEYBE CREATE TABLE

Auditing the database administrators

alter system set AUDIT_SYS_OPERATIONS = TRUE scope=spfile;

1	alter system set AUDIT_SYS_OPERATIONS = TRUE scope=spfile;

sqlplus / as sysdba

ALTER SYSTEM FLUSH SHARED_POOL;

sqlplus / as sysdba

ALTER SYSTEM FLUSH SHARED_POOL;

 update scott.emp1 set sal=3333 where empno=7654 ;

1 row updated.

commit;

Commit complete.

update scott.emp1 set sal=3333 where empno=7654 ;

1 row updated.

commit;

Commit complete.

cat /u01/app/oracle/admin/orcl/adump/

1	cat /u01/app/oracle/admin/orcl/adump/

Mon Jun 6 14:31:46 2011

LENGTH : '198'

ACTION :[30] 'ALTER SYSTEM FLUSH SHARED_POOL'

DATABASE USER:[3] 'sys'

PRIVILEGE :[6] 'SYSDBA'

CLIENT USER:[11] 'mdervisoglu'

CLIENT TERMINAL:[10] 'IST-PCD068'

STATUS:[1] '0'

DBID:[10] '1257644776'

-----------------------------

Mon Jun 6 14:31:49 2011

LENGTH : '216'

ACTION :[48] 'update scott.emp1 set sal=3333 where empno=7654 '

DATABASE USER:[3] 'sys'

PRIVILEGE :[6] 'SYSDBA'

CLIENT USER:[11] 'mdervisoglu'

CLIENT TERMINAL:[10] 'IST-PCD068'

STATUS:[1] '0'

DBID:[10] '1257644776'

Mon Jun 6 14:31:46 2011

LENGTH : '198'

ACTION :[30] 'ALTER SYSTEM FLUSH SHARED_POOL'

DATABASE USER:[3] 'sys'

PRIVILEGE :[6] 'SYSDBA'

CLIENT USER:[11] 'mdervisoglu'

CLIENT TERMINAL:[10] 'IST-PCD068'

STATUS:[1] '0'

DBID:[10] '1257644776'

-----------------------------

Mon Jun 6 14:31:49 2011

LENGTH : '216'

ACTION :[48] 'update scott.emp1 set sal=3333 where empno=7654 '

DATABASE USER:[3] 'sys'

PRIVILEGE :[6] 'SYSDBA'

CLIENT USER:[11] 'mdervisoglu'

CLIENT TERMINAL:[10] 'IST-PCD068'

STATUS:[1] '0'

DBID:[10] '1257644776'

Deleting the audit records

DELETE FROM SYS.AUD$;

1	DELETE FROM SYS.AUD$;

Viewing a single user

AUDIT SESSION

BY omer;

AUDIT SESSION

BY omer;

SELECT "USERNAME", "OS_USERNAME", "USERHOST", "EXTENDED_TIMESTAMP" FROM "SYS"."DBA_AUDIT_SESSION" WHERE returncode != 0

1	SELECT "USERNAME", "OS_USERNAME", "USERHOST", "EXTENDED_TIMESTAMP" FROM "SYS"."DBA_AUDIT_SESSION" WHERE returncode != 0

SELECT "PRIV_USED", "USERNAME", "ACTION_NAME", "TIMESTAMP" FROM SYS.DBA_AUDIT_TRAIL dat, SYS.DBA_PRIV_AUDIT_OPTS dpao WHERE dat.PRIV_USED = dpao.PRIVILEGE and to_char(extended_timestamp AT TIME ZONE DBTIMEZONE, 'YYYY-MM-DD HH24:MI:SS TZD') &gt; TO_CHAR((SYSDATE-1),'YYYY-MM-DD HH24:MI:SS')

1	SELECT "PRIV_USED", "USERNAME", "ACTION_NAME", "TIMESTAMP" FROM SYS.DBA_AUDIT_TRAIL dat, SYS.DBA_PRIV_AUDIT_OPTS dpao WHERE dat.PRIV_USED = dpao.PRIVILEGE and to_char(extended_timestamp AT TIME ZONE DBTIMEZONE, 'YYYY-MM-DD HH24:MI:SS TZD') > TO_CHAR((SYSDATE-1),'YYYY-MM-DD HH24:MI:SS')

Viewing a user’s transactions

SELECT "USERNAME", "ACTION_NAME", "OWNER", "OBJ_NAME", "TIMESTAMP", "SQL_TEXT", "SESSIONID", "USERHOST", "TERMINAL", "OS_USERNAME"
FROM "SYS"."DBA_AUDIT_OBJECT";

Where username = 'USER_NAME'

and

owner = 'SCHEMA_OWNER'

SELECT "USERNAME", "ACTION_NAME", "OWNER", "OBJ_NAME", "TIMESTAMP", "SQL_TEXT", "SESSIONID", "USERHOST", "TERMINAL", "OS_USERNAME"

FROM "SYS"."DBA_AUDIT_OBJECT";

Where username = 'USER_NAME'

and

owner = 'SCHEMA_OWNER'

SELECT * FROM "SYS"."DBA_AUDIT_OBJECT"

Where username = 'USER_NAME'

and

owner = 'SCHEMA_NAME'

SELECT * FROM "SYS"."DBA_AUDIT_OBJECT"

Where username = 'USER_NAME'

and

owner = 'SCHEMA_NAME'

Viewing the SQL statements run according to username

SELECT * FROM V_$SQLAREA WHERE

FIRST_LOAD_TIME

BETWEEN

'2007-12-28/09:00:00'

AND

'2007-12-28/09:18:00'

AND

PARSING_SCHEMA_NAME = 'USER_NAME'

SELECT * FROM V_$SQLAREA WHERE

FIRST_LOAD_TIME

BETWEEN

'2007-12-28/09:00:00'

AND

'2007-12-28/09:18:00'

AND

PARSING_SCHEMA_NAME = 'USER_NAME'

To view information on what has been entered into the database

select "OS_USERNAME","USERNAME","USERHOST","TERMINAL","TIMESTAMP","ACTION_NAME","LOGOFF_TIME","LOGOFF_LREAD","LOGOFF_PREAD","LOGOFF_LWRITE","LOGOFF_DLOCK","SESSIONID","RETURNCODE",

CASE

WHEN TO_CHAR(RETURNCODE) = '1017' THEN 'ORA-01017: invalid username password logon denied'

WHEN TO_CHAR(RETURNCODE) = '28000' THEN 'ORA-28000: the account is locked'

WHEN TO_CHAR(RETURNCODE) = '28009' THEN 'ORA-28009: connection as sys should be as sysdba or sysoper'

WHEN TO_CHAR(RETURNCODE) = '604' THEN 'ORA-00604: error occurred at recursive sql level'

ELSE TO_CHAR(RETURNCODE) END "RETURNMESSAGE",

"CLIENT_ID","SESSION_CPU","EXTENDED_TIMESTAMP","PROXY_SESSIONID","GLOBAL_UID","INSTANCE_NUMBER","OS_PROCESS"

from dba_audit_session

where returncode > 0;

select "OS_USERNAME","USERNAME","USERHOST","TERMINAL","TIMESTAMP","ACTION_NAME","LOGOFF_TIME","LOGOFF_LREAD","LOGOFF_PREAD","LOGOFF_LWRITE","LOGOFF_DLOCK","SESSIONID","RETURNCODE",

CASE

WHEN TO_CHAR(RETURNCODE) = '1017' THEN 'ORA-01017: invalid username password logon denied'

WHEN TO_CHAR(RETURNCODE) = '28000' THEN 'ORA-28000: the account is locked'

WHEN TO_CHAR(RETURNCODE) = '28009' THEN 'ORA-28009: connection as sys should be as sysdba or sysoper'

WHEN TO_CHAR(RETURNCODE) = '604' THEN 'ORA-00604: error occurred at recursive sql level'

ELSE TO_CHAR(RETURNCODE) END "RETURNMESSAGE",

"CLIENT_ID","SESSION_CPU","EXTENDED_TIMESTAMP","PROXY_SESSIONID","GLOBAL_UID","INSTANCE_NUMBER","OS_PROCESS"

from dba_audit_session

where returncode > 0;