RE: Turning on Audit in Oracle database

Admin — Wed, 28 Oct 2009 16:34:58 GMT

Turning off Audit and Privilege Auditing

The following statements turn off the corresponding audit options:

NOAUDIT session;NOAUDIT session BY scott, lori;NOAUDIT DELETE ANY TABLE;NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,    EXECUTE PROCEDURE;

The following statement turns off all statement audit options:

NOAUDIT ALL;

The following statement turns off all privilege audit options:

NOAUDIT ALL PRIVILEGES;

To disable statement or privilege auditing options, you must have the AUDIT SYSTEM system privilege.

The following statements turn off the corresponding auditing options:

NOAUDIT DELETE   ON emp;NOAUDIT SELECT, INSERT, DELETE   ON jward.dept;

Furthermore, to turn off all object audit options on the emp table, enter the following statement:

NOAUDIT ALL   ON emp;

To turn off all default object audit options, enter the following statement:

NOAUDIT ALL   ON DEFAULT;

All schema objects created before this NOAUDIT statement is issued continue to use the default object audit options in effect at the time of their creation, unless overridden by an explicit NOAUDIT statement after their creation.

To disable object audit options for a specific object, you must be the owner of the schema object. To disable the object audit options of an object in another user's schema or to disable default object audit options, you must have the AUDIT ANY system privilege. A user with privileges to disable object audit options of an object can override the options set by any user.

Thanks,

Turning on Audit in Oracle database

Admin — Wed, 28 Oct 2009 16:29:31 GMT

The standard audit commands allow all system privileges to be audited along with access at the object level to any table or

view on the database for select, delete, insert or update. Audit can be run for either successful or unsuccessful attempts or both.

It can be for each individual user or for all users and it can also be done at the session level or access level. At action level a single

record is created per action and at session level one record is created for all audit actions per session.

Here are some commands to set auditing on:

To audit all successful and unsuccessful connections to and disconnections from the database, regardless of user,

BY SESSION (the default and only value for this option), enter the following statement:

AUDIT SESSION;

You can set this option selectively for individual users also, as in the next example:

AUDIT SESSIONBY scott, lori;

Privilege audit options exactly match the corresponding system privileges. For example, the option to audit use of the

DELETE ANY TABLE privilege is DELETE ANY TABLE. To turn this option on, you use a statement similar to the following example:

AUDIT DELETE ANY TABLE    BY ACCESS    WHENEVER NOT SUCCESSFUL;

To audit all successful and unsuccessful uses of the DELETE ANY TABLE system privilege, enter the following statement:
AUDIT DELETE ANY TABLE;
 
To audit all unsuccessful SELECT, INSERT, and DELETE statements on all tables and unsuccessful uses of the 
EXECUTE PROCEDURE system privilege, by all database users, and by individual audited statement, 
issue the following statement:
 
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE      BY ACCESS      WHENEVER NOT SUCCESSFUL;
 
To audit all successful and unsuccessful DELETE statements on the scott.emp table, 
BY SESSION (the default value), enter the following statement:
 
AUDIT DELETE ON scott.emp;
 
To audit all successful SELECT, INSERT, and DELETE statements on the dept table owned by user jward, 
BY ACCESS, enter the following statement:
 
AUDIT SELECT, INSERT, DELETE     ON jward.dept     BY ACCESS     WHENEVER SUCCESSFUL;
 
To set the default object auditing options to audit all unsuccessful SELECT statements, 
BY SESSION (the default), enter the following statement:
 
AUDIT SELECT     ON DEFAULT     WHENEVER NOT SUCCESSFUL;

You can check the following to see audit settings in oracle.

set serveroutput on size 1000000

--
-- SQL to check if the audit has been enabled in the database and where the output will be
-- written to.
--

select name,value from v$parameter
where name like 'audit%'
/

--
-- Test to see what audit actions are defined. Note:- doesn't include object audit actions.
--

select * from dba_stmt_audit_opts
union
select * from dba_priv_audit_opts
/

--
-- Turn on audit for capturing logon and logoff attemempts for all users except
-- privileged attempts.
--

audit create session
/

--
-- check what audit privileges have been granted and to what users and roles.
--

select *
from dba_sys_privs
where privilege like '%AUDIT%'

--
-- SQL to test for failed logon attempts, grouped per day.
--

select count(*),username,terminal,to_char(timestamp,'DD-MON-YYYY')
from dba_audit_session
where returncode<>0
group by username,terminal,to_char(timestamp,'DD-MON-YYYY')
/

--
-- SQL to check for attempts to access the database with non existant users. This could
-- indicate someone trying to guess user names and passwords.
--

select username,terminal,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS')
from dba_audit_session
where returncode<>0
and not exists (select 'x'
from dba_users
where dba_users.username=dba_audit_session.username)
/

--
-- Check for access attempts to the database at unusual hours. The two times should
-- be altered or passed in and if detail is needed for multiple days then group by
-- the date as well.
--

select username,
terminal,
action_name,
returncode,
to_char(timestamp,'DD-MON-YYYY HH24:MI:SS'),
to_char(logoff_time,'DD-MON-YYYY HH24:MI:SS')
from dba_audit_session
where to_date(to_char(timestamp,'HH24:MI:SS'),'HH24:MI:SS') < to_date('08:00:00','HH24:MI:SS')
or to_date(to_char(timestamp,'HH24:MI:SS'),'HH24:MI:SS') > to_date('19:30:00','HH24:MI:SS')
/

--
-- Check for users sharing database logons.
--

select count(distinct(terminal)),username
from dba_audit_session
having count(distinct(terminal))>1
group by username
/

--
-- Check for multiple database accounts being used from one terminal. This could indicate
-- wrong doing as each terminal should in theory be used by one person and one account?
--

select count(distinct(username)),terminal
from dba_audit_session
having count(distinct(username))>1
group by terminal
/

--
-- Check the audit trail for any changes being made to the structure of the database schema.
--

col username for a8
col priv_used for a16
col obj_name for a22
col timestamp for a17
col returncode for 9999
select username,
priv_used,
obj_name,
to_char(timestamp,'DD-MON-YYYY HH24:MI') timestamp,
returncode
from dba_audit_trail
where priv_used is not null
and priv_used<>'CREATE SESSION'
/

Hope this helps.

FranklinFaces.com - Oracle & SQL Server Database Forums for all IT Professionals / Oracle Forum / Oracle Database Administration / Turning on Audit in Oracle database / Latest Posts

RE: Turning on Audit in Oracle database

Turning on Audit in Oracle database