Wednesday, October 10, 2007

Some examples about auditing and output of auditing

Subject: Some examples about auditing and output of auditing
Doc ID: Note:167293.1 Type: BULLETIN
Last Revision Date: 04-JUN-2007 Status: PUBLISHED


"Checked for relevance on 27-Mar-2007"
Purpose:
~~~~~~~~
Purpose of the document is to show some examples of auditing output as most
articles do not show examples
How to set up auditing can be seen in for example:
Note 41800.1 Quick Reference to Auditing Information


Scope & Application:
~~~~~~~~~~~~~~~~~~~~
This article intends to demonstrate some examples of output of auditing.
The article is divided in

1. Auditing administrative connections
2. Auditing on object Level
3. Auditing on privilege Level
4. Auditing statements

Shown is for each section what can be audited, how to check what is audited and
some examples of the output of auditing.


Auditing:
~~~~~~~~~
Auditing can be set on statement level, object level and privilege level.
Also administrative connections are logged.

1. Auditing administrative connections
--------------------------------------
Administrative connections are CONNECT INTERNAL and CONNECT / AS SYSDBA.
There are some good examples of output of audited administrative connections
for Unix and for NT in:

Note 103964.1: RDBMS: How to Audit Connect Internal Using Oracle Server
Note 99137.1 : Setting up, Interpreting Auditing Using Windows NT Event Viewer

In RDBMS ver. 9.2 and up, it is now possible to audit the SYS user with the
parameter AUDIT_SYS_OPERATIONS , for details, please check this note:

Note 174340.1 : Audit SYS user Operations


2. Auditing on object Level
---------------------------
Objects that can be audited are : tables, views, sequences, packages, stored
procedures/functions. Note that because some objects may be dependant of other
objects (example function -> view -> table ) as a result several audit records
might be inserted when these objects are audited.

Object auditing options are set for all users of the database and cannot be set
for individual users.

What options can be set? This can be seen from all_def_audit_opts.

Example object auditing options
-------------------------------

SQL> connect system/manager
SQL> select * from all_def_audit_opts;

Result:

ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE
--- --- --- --- --- --- --- --- --- --- --- --- ---
-/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-

These correspond to the following object options respectively:

alter, audit, comment, delete, grant, index, insert, lock, rename,
select, update, reference and execute .

All except reference and execute can be applied to tables, otherwise
you will get the error ora-1982 "invalid auditing option for tables".

Example of auditing scott.emp
-----------------------------

SQL> connect system/manager
SQL> audit select on scott.emp by session;
=> Audit succeeded.

Check: Which objects are audited

SQL> col owner format a7
SQL> col object_name format a7
SQL> select * from dba_obj_audit_opts
where owner='SCOTT' and OBJECT_NAME='EMP';

result:

OWNER OBJECT_ OBJECT_TY ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE
------- ------- --------- --- --- --- --- --- --- --- --- --- --- --- --- ---
SCOTT EMP TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- S/S -/- -/- -/-

Generate some audit information:

SQL> connect scott/tiger
SQL> select * from emp;
=> all scott.emp's rows are shown
SQL> connect t/tl
SQL> select * from scott.emp;
=> ERROR at line 1:
ORA-00942: table or view does not exist
SQL> connect system/manager
SQL> select * from scott.emp;
=> all scott.emp's rows are shown


Results of auditing:

SQL> connect system/manager

SQL> col username format a8
SQL> col priv_used format 999
SQL> /

SQL> select username, priv_used, ses_actions from dba_audit_object
where obj_name='EMP' and owner='SCOTT';

Result:


USERNAME PRIV_USED SES_ACTIONS
-------- ---------------------------------------- ----------------
SYSTEM SELECT ANY TABLE ---------S---
T ---------F---
SCOTT ---------S---


3. Auditing on privilege Level
------------------------------

All system privileges can be audited.
The different privileges can be selected from system_privilege_map.
If you attempt to use a value that does not belong to the list, you get the
following error:

SQL> audit drop snapshot by access;
audit drop snapshot by access
*
ERROR at line 1:
ORA-00956: missing or invalid auditing option

Example: auditable system privileges
------------------------------------
SQL> connect system/manager
SQL> select * from system_privilege_map;

Result (this result is version dependant and still going up):

PRIVILEGE NAME
---------- ----------------------------------------
-3 ALTER SYSTEM
-4 AUDIT SYSTEM
-5 CREATE SESSION
-6 ALTER SESSION
-7 RESTRICTED SESSION
-10 CREATE TABLESPACE
-11 ALTER TABLESPACE
-12 MANAGE TABLESPACE
-13 DROP TABLESPACE
..... not entire result is shown ......
-167 GRANT ANY PRIVILEGE
-172 CREATE SNAPSHOT
-173 CREATE ANY SNAPSHOT
-174 ALTER ANY SNAPSHOT
-175 DROP ANY SNAPSHOT
-194 WRITEDOWN DBLOW
-195 READUP DBHIGH
-196 WRITEUP DBHIGH
-197 WRITEDOWN
-198 READUP
-199 WRITEUP


Example of auditing CREATE TABLE by scott and system
----------------------------------------------------

SQL> connect system/manager
SQL> audit create table by scott, system;
=> Audit succeeded.

This means each create table is audited for system and scott once per session.
Should each create table be audited then the clause 'by access' must be added.

check: Which privileges are audited

SQL> col user_name format a7
SQL> col privilege format a15
SQL> connect system/manager
SQL> select * from sys.dba_priv_audit_opts;

Result:

USER_NA PRIVILEGE SUCCESS FAILURE
------- --------------- ---------- ----------
SCOTT CREATE TABLE BY ACCESS BY ACCESS
SYSTEM CREATE TABLE BY ACCESS BY ACCESS

Generate some audit information:

SQL> connect scott/tiger
SQL> create table t1 (c1 number);
create table t1 (c1 number)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> create table tsc (c1 number);
=> table created
SQL> connect t/tl
SQL> create table tsc (c1 number);
=> table created
SQL> connect system/manager
SQL> create table scott.t1(c1 number);
=> create table scott.t1(c1 number)
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> create table tsc (c1 number);
=> table created

Results of auditing:

SQL> connect system/manager

SQL> col username format a8
SQL> col priv_used format 999
SQL> /

SQL> select username, priv_used, ses_actions from dba_audit_object;


Result:

USERNAME PRIV_USED SES_ACTIONS
-------- ---------------------------------------- -------------------
SCOTT CREATE TABLE
SCOTT CREATE TABLE
SYSTEM CREATE TABLE


SQL> connect system/manager

SQL> select action, action_name, username
from dba_audit_trail ;

Result:

ACTION ACTION_NAME USERNAME
---------- --------------------------- --------
1 CREATE TABLE SCOTT
1 CREATE TABLE SCOTT
1 CREATE TABLE SYSTEM



4. Auditing statements
----------------------
Statement auditing is the selective auditing of related groups of statements for
a type of database structure or type of schema object for ddl (data definition
language) or dml (data manipulation language) statements.
For example when 'audit table' is audited create, alter and drop table
statements are audited.
When 'audit select table' is audited all select statements from table, view,
snapshot.. will be audited.

The difference with privilege auditing is that in the latter case a system
privilege is audited. For example the 'audit create table' will only audit
create table and not alter or drop table statements.
When similar statement and privilege options are set only one audit record is
generated.

Example: auditable statements
-----------------------------
The statements taht can be adited can be seen from stmt_audit_option_map.

SQL> connect system/manager
SQL> select * from stmt_audit_option_map;

Result (this result is version dependant and still going up):

OPTION# NAME PROPERTY
---------- ---------------------------------------- ----------
3 ALTER SYSTEM 0
4 SYSTEM AUDIT 0
5 CREATE SESSION 0
6 ALTER SESSION 0
7 RESTRICTED SESSION 0
8 TABLE 0
9 CLUSTER 0
10 CREATE TABLESPACE 0
11 ALTER TABLESPACE 0
12 MANAGE TABLESPACE 0
13 DROP TABLESPACE 0
..... not entire result is shown ......
221 CONTEXT 0
234 ON COMMIT REFRESH 0
235 EXEMPT ACCESS POLICY 0
236 RESUMABLE 0
237 SELECT ANY DICTIONARY 0
238 DEBUG CONNECT SESSION 0
239 DEBUG CONNECT USER 0
240 DEBUG CONNECT ANY 0
241 DEBUG ANY PROCEDURE 0
242 DEBUG PROCEDURE 0

165 rows selected.

SQL> select * from stmt_audit_option_map
where name like '%TABLE%';

Result:

OPTION# NAME PROPERTY
---------- ---------------------------------------- ----------
8 TABLE 0
10 CREATE TABLESPACE 0
11 ALTER TABLESPACE 0
12 MANAGE TABLESPACE 0
13 DROP TABLESPACE 0
14 TABLESPACE 0
15 UNLIMITED TABLESPACE 0
40 CREATE TABLE 0
41 CREATE ANY TABLE 0
42 ALTER ANY TABLE 0
43 BACKUP ANY TABLE 0
44 DROP ANY TABLE 0
45 LOCK ANY TABLE 0
46 COMMENT ANY TABLE 0
47 SELECT ANY TABLE 0
48 INSERT ANY TABLE 0
49 UPDATE ANY TABLE 0
50 DELETE ANY TABLE 0
54 ALTER TABLE 0
57 LOCK TABLE 0
58 COMMENT TABLE 0
65 SELECT TABLE 0
66 INSERT TABLE 0
67 UPDATE TABLE 0
68 DELETE TABLE 0
69 GRANT TABLE 0


Example of auditing TABLE by scott and system
---------------------------------------------

SQL> connect system/manager
SQL> audit table by scott, system;
=> Audit succeeded.


check: Which statements are audited

SQL> col user_name format a8
SQL> col proxy_name format a6
SQL> col audit_option format a9
SQL> col privilege format a15
SQL> connect system/manager
SQL> select * from dba_stmt_audit_opts;

Result:

USER_NAM PROXY_ AUDIT_OPT SUCCESS FAILURE
-------- ------ --------- ---------- ----------
SYSTEM TABLE BY ACCESS BY ACCESS
SCOTT TABLE BY ACCESS BY ACCESS

Generate some audit information:

SQL> connect scott/tiger
SQL> create table t1 (c1 number);
create table t1 (c1 number)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> drop table t1;
=> table dropped
SQL> connect system/manager
SQL> create table scott.t1(c1 number);
=> table created
SQL> drop table no;
=> *
ERROR at line 1:
ORA-00942: table or view does not exist

Results of auditing:

SQL> connect system/manager

SQL> col username format a8
SQL> col priv_used format 999

SQL> select username, priv_used, ses_actions from dba_audit_object;


Result:

USERNAME PRIV_USED SES_ACTIONS
-------- ---------------------------------------- -------------------
SCOTT CREATE TABLE
SCOTT CREATE TABLE
SYSTEM CREATE TABLE


SQL> connect system/manager

SQL> select action, action_name, username
from dba_audit_trail ;

Result:

ACTION ACTION_NAME USERNAME
---------- --------------------------- --------
1 CREATE TABLE SCOTT
12 DROP TABLE SCOTT
1 CREATE TABLE SYSTEM
12 DROP TABLE SYSTEM

SQL> select username, priv_used, ses_actions from dba_audit_object;

Result:

USERNAME PRIV_USED SES_ACTIONS
-------- ---------------------------------------- -------------------
SCOTT CREATE TABLE
SCOTT
SYSTEM CREATE ANY TABLE
SYSTEM

Example of auditing SELECT TABLE by scott and system
----------------------------------------------------

SQL> connect system/manager
SQL> audit select table by scott, system;
=> Audit succeeded.


Check: Which statements are audited

SQL> col user_name format a8
SQL> col proxy_name format a6
SQL> col audit_option format a13
SQL> col privilege format a15
SQL> connect system/manager
SQL> select * from dba_stmt_audit_opts;

Result:

USER_NAM PROXY_ AUDIT_OPTION SUCCESS FAILURE
-------- ------ ------------- ---------- ----------
SYSTEM SELECT TABLE BY SESSION BY SESSION
SCOTT SELECT TABLE BY SESSION BY SESSION

Generate some audit information:

SQL> connect scott/tiger
SQL> delete from emp where ename='KING';

SQL> connect system/manager
SQL> insert into scott.emp (empno, ename) values (1, 'TEST');
SQL> select * from scott.emp;

Results of auditing:

SQL> connect system/manager

SQL> col username format a8
SQL> col priv_used format 999

SQL> select username, priv_used, ses_actions from dba_audit_object;


Result:

USERNAME PRIV_USED SES_ACTIONS
-------- ---------------------------------------- -------------------
SCOTT ---------S------
SCOTT ---------S------
SYSTEM ---------S------
SYSTEM SELECT ANY TABLE ---------S------
SYSTEM ---------S------
SYSTEM ---------S------
SYSTEM ---------S------
SCOTT ---------S------
SYSTEM ---------S------
SYSTEM ---------S------
SYSTEM ---------S------


SQL> connect system/manager

SQL> select action, action_name, username
from dba_audit_trail ;

Result:

ACTION ACTION_NAME USERNAME
---------- --------------------------- --------
103 SESSION REC SCOTT
103 SESSION REC SCOTT
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SCOTT
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM



Related Documents:
~~~~~~~~~~~~~~~~~~
Note 41800.1 : QREF: Quick Reference to Auditing Information
Note 99137.1 : Setting up, Interpreting Auditing Using Windows NT Event Viewer
Note 103964.1 : How to Audit Connect Internal Using Oracle Server
Note 1020945.6: How to Setup Auditing
Note 1068714.6: How does the NOAUDIT option work
Note 99786.1 : How to Audit User Connection, Disconnection Date and Time
Note 174340.1 : Audit SYS user Operations (9.2 and up)

17 comments:

Anonymous said...

Hello
http://www.rhythmmethodlive.com/ - lexapro medication
Doctors and other professional nowadays recommend the same Lexapro drug to the patients who are suffering from depression.
[url=http://www.rhythmmethodlive.com/]order lexapro[/url]
The credit for the invention of this Lexapro drug goes to the Lundbeck and Forest Laboratories.
purchase lexapro
This Lexapro drug is maninly used for treating the depression disorder in humans.

Anonymous said...

Hi I'd like to thank you for such a great made site!
thought this would be a perfect way to make my first post!

Sincerely,
Johnie Maverick
if you're ever bored check out my site!
[url=http://www.partyopedia.com/articles/diego-party-supplies.html]diego Party Supplies[/url].

Anonymous said...

http://markonzo.edu feet http://profiles.friendster.com/nizorals#moreabout http://inderal.indieword.com/ zachariah whtis http://www.netknowledgenow.com/members/prevacid-side-effects.aspx http://lamictal.indieword.com/ nagoya http://www.hothotheat.com/profiles/blogs/arimidex-side-effects-1

Anonymous said...

Hello. My wife and I bought our house about 6 months ago. It was a foreclosure and we were able to get a great deal on it. We also took advantage of the 8K tax credit so that definitely helped. We did an extensive remodeling job and now I want to refinance to cut the term to a 20 or 15 year loan. Does anyone know any good sites for mortgage information? Thanks!

Mike

Anonymous said...

hello, cute web page
the way to get blog posts that smash in your visitors:
http://tinyurl.com/ylj3rxw

Anonymous said...

hi, i have bought car,but when i left to new york(new jersy) it got screwed. what should i put necessary steps to comeover this kind of solitory .
thanks

Anonymous said...

top [url=http://www.c-online-casino.co.uk/]uk online casino[/url] check the latest [url=http://www.realcazinoz.com/]realcazinoz[/url] autonomous no consign reward at the chief [url=http://www.baywatchcasino.com/]casino online
[/url].

Anonymous said...

generic alesse canada http://surveys.questionpro.com/a/TakeSurvey?id=3418695 alesse birth control positive reviews [url=http://flavors.me/alesse_myeasilipunc1983] birth control alesse generic side effects[/url] aleve side effects birth control alesse generic side effects alesse 21 birth control price
mamoleptino321
alesse birth control pill generic http://surveys.questionpro.com/a/TakeSurvey?id=3418782 alesse birth control reviews 2011 [url=http://flavors.me/alesse_presulinza1982] alesse reviews and ratings[/url] alesse birth control emotional side effects aleve side effects kidney best birth control for acne alesse

http://wikindustrie.net78.net/index.php/Discussion_utilisateur:RubiaTracey569%2BResult:%2B%C3%AF%C3%B0%C3%A8%C3%B1%C3%B3%C3%B2%C3%B1%C3%B2%C3%A2%C3%B3%C3%A5%C3%B2%2Bnofollow;%2B%C3%B3%C3%B1%C3%AF%C3%A5%C3%B5%2B-%2B%C3%A7%C3%A0%C3%AF%C3%AE%C3%B1%C3%B2%C3%A8%C3%AB%C3%A8%2B%C3%A2%2B%C3%AF%C3%A5%C3%B0%C3%A2%C3%BB%C3%A9%2B%C3%AF%C3%AE%C3%AF%C3%A0%C3%A2%C3%B8%C3%A8%C3%A9%C3%B1%C3%BF%2B%C3%B0%C3%A0%C3%A7%C3%A4%C3%A5%C3%AB%2B%22/index.php http://www.tdajaksel.com/accounting-iz-eazy/comment-page-1/#comment-2052 http://atetas.net/pages/forum-thread-view?r=2KXCMIDIK8&send_to=%2Fpages%2Fforum#software_comment_10944 http://watch-dog-forums.com/showthread.php?tid=146860 http://www.plastech.jp/bbs/view.php?code=qna_jp&idx=410846&sbbs_vnumber=410844&name=&subject=&page=1

filme alese online http://surveys.questionpro.com/a/TakeSurvey?id=3418649 oubli pilule contraceptive alesse [url=http://alessefr40fu.carbonmade.com/projects/4679948] ellesse buy online[/url] is alesse birth control good for acne does alesse birth control cause acne pilule contraceptive alesse 28 effet secondaire

Anonymous said...

Hi there! This blog post could not be written any better!
Looking through this post reminds me of my previous roommate!
He always kept talking about this. I most certainly will forward this article to
him. Pretty sure he'll have a good read. Thank you for sharing!

Here is my webpage; Juegos Gratis

Anonymous said...

Awesome! Its truly awesome post, I have got much clear idea
regarding from this post.

Also visit my web-site; Juegos

Anonymous said...

If some one needs expert view on the topic of blogging and
site-building then i propose him/her to go to see this website, Keep up the good work.


My website ... compare car insurance

Anonymous said...

To do the, it is so necessary to think about exactly tiger traps may possibly possibly are out
there which can hamper our house originating from communication
pretty much. Premium coffee is a definitely crucial region of numerous folk's lifestyle. This will make it filled while having hand-crafted souverain icing creations to get Daddies Life, integrating alcohol personalize mugs, daddy has, and then a sugary snacks oral plaque buildup that claims "Dad".

Have a look at my website single Serve coffee maker pods

Anonymous said...

The best decide if each Breville Juice extractor
suits you. Complete the work! A little something Now i looked
at initially could be that the juice somebody
have to have the ability to the memory foam which the materials My partner and i earned in
my brand new ancient juicer was able to do (by now, our J8004 had
become brand new young juice extractor over the internet!
).

Check out my page ... commercial milkshake blenders uk

Anonymous said...

This relates to a larger size and difficult vegetables and fruits with out customers wanting to slice, rind maybe reduce individuals in the
past merging them to the best pc. Employing hurry wherein fruit smoothies are made making use of the quad cutting tool Nine from inside the 40-oz.
Disadvantages: lengthy advanced planning opportunity, lessened top notch juice, more
difficult entirely along with site.

Also visit my webpage used industrial mixers and blenders

Anonymous said...

How can you home appliances at this time in which it ending websites are typically
dreadful. Little ones will adore slushes built by cold grease during the rocks dice containers.
Of type of, the site creates 14,500rpm delivers pretty fast free weights
program. Have trying to do all the chopping, killer, joining together and also
leaving soft ice cream house about the Ninja Machines.


my homepage: masticating juicers for sale

Xia Zhang said...

http://www.shopbestgoods.com/
http://www.bestcustomsonline.com/
http://www.nike-jordanshoes.com/
http://www.polo-tshirts.com/
http://www.burberry-factory.com/
http://www.kate-spades.com/
http://www.barbour-factory.com/
http://www.coachlosangeles.com/
http://www.official-coachoutlet.com/
http://www.louisvuittonas.com/
http://www.burberryoutlet2014.com/
http://www.official-mkoutlet.com/
http://www.official-pradaoutlet.com/
http://www.beatsbydreoutlet.net/
http://www.michaelkorsonlineusa.com/
http://www.northsclearance.com/
http://www.ralph-laurensale.com/
http://www.gucci-shoesuk2014.com/
http://www.michael-korsusa.com/
http://www.polo-outlets.com/
http://www.hermes-outletonline.com/
http://www.ralphslauren.co.uk/
http://www.marcjacobsonsale.com/
http://www.mcmworldwides.com/
http://www.warmbootssale.com/
http://www.salongchamppairs.com/
http://www.canada-gooser.com/
http://www.michaelkors.so/
http://www.oakley-sunglassoutlet.com/
http://www.north-faceoutlets.net/
http://www.moncler-clearance.com/
http://www.woolrich-clearance.com/
http://www.barbour-jacketsoutlet.com/
http://www.moncler-jacketsoutletonline.com/
http://www.monsterbeatsbydres.net/
http://www.lv-guccishoesfactory.com/
http://www.cheapdiscountoutlet.com/
http://www.coachsfactoryoutlet.com/
http://www.coach-blackfriday2014.com/
http://www.coach-storeoutletonline.com/
http://www.coach-factorysoutletonline.com/
http://www.coachccoachoutlet.com/
http://www.coach-factories.net/
http://www.coach-pursesoutletonline.com/
http://www.coach-outletsusa.com/
http://www.zxcoachoutlet.com/
http://www.mischristmas.com/
http://www.misblackfriday.com/
http://www.newoutletonlinemall.com/
http://www.ralphlaurenepolo.com/
http://michaelkorsoutlet.mischristmas.com/
http://mcmbackpack.mischristmas.com/
http://monsterbeats.mischristmas.com/
http://northfaceoutlet.mischristmas.com/
http://mk.misblackfriday.com/
http://coachoutlet.misblackfriday.com/
http://coachfactory.misblackfriday.com/
http://uggaustralia.misblackfriday.com/
http://coachpurses.misblackfriday.com/
http://coachusa.misblackfriday.com/
http://coach.misblackfriday.com/
http://michaelkorss.misblackfriday.com/
http://michaelkors.misblackfriday.com/
http://airmax.misblackfriday.com/
http://michael-kors.misblackfriday.com/
https://twitter.com/CoachOutlet2014
https://www.facebook.com/coachoutletstoreonline
https://www.facebook.com/ralphlaurenoutletonline

dong dong23 said...

coach outlet online
tory burch outlet
louis vuitton
michael kors handbags
louis vuitton outlet
ray ban outlet
kate spade
vans shoes outlet
louis vuitton outlet
caoch outlet
michael kors outlet
coach factory outlet
michael kors handbags
coach outlet
michael kors outlet
supra sneakers
louis vuitton handbags
tods shoes
jordan 4
coach outlet
cheap jordans
true religion outlet
kobe bryant shoes
polo ralph lauren
toms shoes
nike roshe flyknit
instyler curling iron
michael kors outlet
michael kors outlet online
michael kors handbags
hollister kids
michael kors outlet
adidas outlet store
nike basketball shoes
mont blanc
adidas shoes
louis vuitton outlet
celine handbags
louis vuitton handbags
oakley sunglasses
20166.4wengdongdong