It has helped greatly for my review

Hi Tom,

How can I audit specific user selecting on one of my tables?

I tried:

AUDIT SELECT ON T1 BY access by user1;

which doesn't work. And I don't want to use
"AUDIT SELECT TABLE BY user1 BY ACCESS WHENEVER SUCCESSFUL". Since it will audit every SINGLE table, which is too much.

Thanks,

Tom, I read this on manual:
</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/audit.htm#1633 <code>

Caution:
The NOAUDIT statement only specifies auditing options; it does not disable auditing as a whole. To turn auditing off and stop Oracle from generating audit records, set the initialization parameter AUDIT_TRAIL in the database's initialization parameter file as described in "Enabling and Disabling Auditing".

Well, if I issued NOAUDIT any... what was being audit -- What do I do? I mean, Oracle keeps generating records on database? I thought the audid was turned off.

thks, Marcio

Tom,

i wanted to post this query in a separate thread - but i am not geetting chance for the past 1 week.. here it goes.

06:22:31.734 SQL t@2200 select UPPER(value) from nls_database_parameters where parameter=UPPER('NLS_CHARACTERSET')
06:22:31.750 SQL t@2200 select * from mxSchema
06:22:31.750 SQL t@2200 -->select
06:22:31.750 SQL t@2200 -->select
06:22:31.750 SQL t@2200 -->select
06:22:31.750 SQL t@2200 select * from mxVer6 where mxOid = :va
06:22:31.765 SQL t@2200 :va=1
06:22:31.765 SQL t@2200 -->select
06:22:31.765 SQL t@2200 select * from mxAdmin where mxType = :va
06:22:31.765 SQL t@2200 :va=5
06:22:31.765 SQL t@2200 -->select
06:22:31.781 SQL t@2200 select * from mxVer6 where mxOid = :va
06:22:31.781 SQL t@2200 :va=1
06:22:58.984 SQL t@2200 -->select
06:22:58.984 SQL t@2200 select * from V_Prod_Code@TSTEENWL V_Prod_Code,V_Order_DocQuote4@TSTEENWL V_Order_DocQuote4 where V_Order_DocQuote4.order_row_id = :va and (V_Prod_Code.sub_requisition_num = V_Order_DocQuote4.sub_requisition_num)
06:22:58.984 SQL t@2200 :va15L-6DQ
06:23:02.953 SQL t@2200 --select
06:23:02.968 SQL t@2200 select * from lxRO_4de5d474 where lxToLat = :va and lxToId = :vb
06:23:02.968 SQL t@2200 :va=1306907764
06:23:02.968 SQL t@2200 :vb=117440512
06:23:02.984 SQL t@2200 -->select
06:23:02.984 SQL t@2200 select * from lxRO_4de5d474 where lxOid in (:va)
06:23:02.984 SQL t@2200 :va=201326592
06:23:02.984 SQL t@2200 -->select
06:23:03.078 SQL t@2200 select * from lxBO_4de5d474 where lxOid = :va
06:23:03.093 SQL t@2200 :va=33554432
06:23:03.093 SQL t@2200 -->select
06:23:03.093 SQL t@2200 select * from V_Order_DocQuote4@TSTEENWL where order_row_id = :va
06:23:03.093 SQL t@2200 :va15L-6DQ
06:23:04.140 SQL t@2200 --select
06:23:04.156 SQL t@2200 select * from lxBO_4de5d474 where lxOid = :va
06:23:04.156 SQL t@2200 :va=16777216
06:23:04.156 SQL t@2200 -->select
06:23:04.156 SQL t@2200 select * from V_Prod_Code@TSTEENWL where r1 = :va
06:23:04.156 SQL t@2200 :va20288-281-11407-T
06:23:06.937 SQL t@2200 --select
06:23:06.937 SQL t@2200 select mxOid,mxFlags,mxParent from mxBusType where mxName = :va
06:23:06.937 SQL t@2200 :va=LINKS Line Item
06:23:06.937 SQL t@2200 -->select
06:23:06.953 SQL t@2200 select * from mxPolicy where mxName = :va
06:23:06.953 SQL t@2200 :va=LINKS Line Item
06:23:06.953 SQL t@2200 -->select


the above lines are generated by one of the tool which we are using ...not sure how it does the search..I understand the select staements and bind variables (one of your favourite!!) but dont know how they are holding the resultset of each query and processing the whole process ...and getting the output.
this is just a search operation on an object.
Can you throw some light on this, so i can understand better


Thanks for your time and patience.

Tom,

Short of change the init parameter for audit and bounce the database, how can I stop auditing for any client? I can stop auditing on all other users except when the user_name is any client. I tried NOAUDIT delete table by any client, of  course it returned no such user.
 

SQL> select * from sys.dba_stmt_audit_opts;

USER_NAME                      PROXY_NAME                     AUDIT_OPTION                             SUCCESS    
------------------------------ ------------------------------ --------------------------------------
ANY CLIENT                                                    SELECT TABLE                             BY SESSION 
ANY CLIENT                                                    INSERT TABLE                             BY SESSION 
ANY CLIENT                                                    DELETE TABLE                             BY SESSION 
ANY CLIENT                                                    EXECUTE PROCEDURE                        BY SESSION 
ANY CLIENT                                                    UPDATE TABLE                             BY SESSION 


Thanks.
 

The version is Oracle 8.1.7.4.1. To be honest, I don't remember how I enabled that. Since sys.dba_stmt_audit_opts is just a view, I found those records were actually in SYS.AUDIT$ table, so I went in to delete them. Now I kind of stopped all auditing actions except it still generating auditing records for SYSTEM, I guess that is built in.

My further question regarding AUDIT is that based on this audit trail if I can built a monitoring procedure that keeps track of all users of their actions (actually all select statements on one schema, since this is a warehouse, that's the only thing they can do), capture those statements, get timing statistics and get an EXPLAIN PLAN for those statements. The purpose of it would be 1) to track the usage; and 2) to monitor performance. Is this practical or doable, if not, what is the better approach?

I would appreciate greatly your thought on this.

HI, TOM!

We have a production application which uses more than 400 tables. We are willing to have a better access control and allow auditing at the user level in a near future. Tell me, taking into account this number of tables, should we use Fine Grained Access Control? Or would it be better to develop another application to control access to other applications (transformed into modules)?

Thanks again.
Best regards.

hi tom

platform Windows NT

I set
audit_trial =DB in init.ora

and audit_file_dest=$oracle_home/rdbms/audit
which is also default
i have enabled auditing.
but nothing appears in audit directory.

This is how you get that 'ANY CLIENT' turned on if you're still interested:

SQL> audit create session by sys;

SQL> select * from dba_stmt_audit_opts;

USER_NAME  PROXY_NAME AUDIT_OPTION         SUCCESS    FAILURE
---------- ---------- -------------------- ---------- ---------
ANY CLIENT            CREATE SESSION       BY ACCESS  BY ACCESS

It might get turned on by other auditing statements for sys; I didn't play with it.  It does audit logon/logoff for sys, it doesn't get conn internal or conn as sydba. 

Tom,
Can you tell me how to enable auditing for Create Control File command? 

Version - 9.2.0.3

  1* select *from audit_actions where action=57
 
    ACTION NAME
---------- ---------------------------
        57 CREATE CONTROL FILE
 
SQL> audit create control file;
audit create control file
             *
ERROR at line 1:
ORA-00969: missing ON keyword
 
 

Ok, if that's the case, audit for sys operations is going to be written to the operating system file and not to the aud$ right? So what action=57 in aud$ represent or what is its significance? Also, I am able to audit create procedure but not create function or create package. Why is this so ?
What difference does it make between auditing procedure and other db objects like function/package?

SQL> audit create procedure;
 
Audit succeeded.
 
SQL> audit create function;
audit create function
             *
ERROR at line 1:
ORA-00969: missing ON keyword
 
 
SQL> audit create package;
audit create package
             *
ERROR at line 1:
ORA-00969: missing ON keyword
 

Let me rephrase my above question# 1, What does action=57 in Audit_actions represent or what is its significance? Sorry for the confusion!

Hi Tom,
In your Second book (Effective Oracle by design) you have mentioned (page 28) that "if the DBA mucked with the audit trail (not possible if you use operating system auditing and don't give the DBA operating system privileges), you can detect that (and get rid of her)"

Please explain how to do this.

Hi,

I am very clear with audit_trail=db and associated views/tables.

As I understand, irrespective of setting of AUDIT_TRAIL, auditing of sysdba logons, database startup / shutdown etc. is always done in Windows Event manager. Now if I set audit_trail=os, and then restart my database then what exactly happens and where do I look for details. Now so far from forums I have understood that along with audit_trail=os, we have to set audit_file_dest to the folder where the OS file would get generated. Then if you want to audit each stmt given by sys then set audit_sys_operations=true (9i and above). Now when I set
audit_sys_operations=true OR/AND audit_file_dest, my database does not start and produces following error. My init.ora has

audit_trail=OS
audit_sys_operations=TRUE
audit_file_dest=C:\Temp

The error which comes is and database does not open -
LRM-00101: unknown parameter name 'audit_sys_operations'
LRM-00101: unknown parameter name 'audit_file_dest'
ORA-01078: failure in processing system parameters

My question is - What is the meaning and how do we use the following -
audit_trail=OS
audit_sys_operations=TRUE
audit_file_dest=C:\Temp

Regards,

B Smith.

the event log is where the OS audit trail can go to as well.



On windows you only have:


ops$xp10gr1\tkyte@ORA10GR1> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE
ops$xp10gr1\tkyte@ORA10GR1>

and you only have audit_sys_operations if you are using current software. 

Is there a way to track what tables and indexes of a particular schema were accessed by different users while running their queries (SELECT statement)?

The users normally execute queries through a front end application.

We have lots and lots of indexes that are eating up huge amount of space. We need to figure the indexes that are not used and could be dropped.

Thanks,

Yes I would do that by altering the index.
Does monitoring the usage of index impacts performance?

Also, is there a way to monitor which tables are scanned full I mean full table scan?

Thanks,

Hi Tom,
I have enabled auditing on a table in 9i using
audit insert, update, delete on <table> by access;
When I insert data into this table through sql plus, dba_audit_trail is getting populated with appropriate audit information.

But if I submit a job using dbms_job and the job is inserting data into the same table using insert stmt, but dba_audit_trail is not recording any information.

Am I missing anything? Appreciate your help.
Thanks in advance.

How can we track change of attributes using Audit? (Oracle 8174)

what I have done is 

AUDIT_TRAIL = DB (init.ora) 

$ sqlplus system/********

SQL> audit alter user;
Audit succeeded.

SQL>alter user v1 identified by d234;

SELECT * FROM DBA_AUDIT_TRAIL;
SELECT * FROM AUD$;

works fine ... 

but when I use following sequence 

$ sqlplus v1/********

SQL>alter user v1 identified by d234;

SELECT * FROM DBA_AUDIT_TRAIL;
SELECT * FROM AUD$;

Nothing is logged in these views. Where I'm I going wrong? 

 

Tom,
we need to implement auditing on one of our tables and later we want to reuse the same logic to other tables if we need. We are working on Oracle 9i.

I think of two approaches.
can you suggest which one is better or any other better approach other than these two.

1) Have one common audit trail table which consists of few columns to identify a module/table and columns for auditing as follows:

Audit column 1 Name
Audit Column 1 Old Value
Audit Column 1 New Values
Audit column 2 Name
Audit Column 2 Old Value
Audit Column 2 New Values
.
.
.
we may have 5 to 6 such sets and this may increase depending on requirements.

In this case, for every record in a table we want to audit, we will have only 1 record in audit table.


2) Have a similar audit trail table. Instead of having such audit column 1,2 ,etc have only 3 columns.

Few Primary Key columns
.
.
.
Audit column Name
Audit column old value
Audit Column New value.

In this case, for every record in a table we want to audit, we will have more than 1 record in audit table.

As our requirement is to extend this auditing functionality for other tables in future, can you please advice which is better approach.

Thanks Tom.

Tom,
Thanks for the reply.
Does it mean that i need to capture the entire row
instead of specific columns in another table?
If so, i may need to have an audit table for every table i want to audit.Please correct me if i am wrong.

Hi Tom,

I have a question about how auditing works. Basically, I was looking how to audit everything by everyone. I thought it was as easy as setting audit_trail=DB. This apparently does nothing and you have to explicitly say what kind of thing it is you want to audit like "audit delete any table".

My question is, do we have to explicitly state for every possible action for every user "audit whatever..."? Is there any "audit all" type of thing?

ops$tkyte@ORA10GR2> audit all;

Audit succeeded.

Sorry Tom I retract my previous question. I thought about it more I don't want to track everything.

I am curious though why when I audit dml operations it shows up as a 103 action# (session rec) instead of action# 2,3,6 or 7 (audit_actions table).

Also, you tweaked a little above at the thought of deleting from aud$

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:404742183816#12661627954503

Thought you might like to know the documentation actually suggests it, and states further down it is the only sys object that show be modified.

http://download-west.oracle.com/docs/cd/B14117_01/network.101/b10773/cfgaudit.htm#1006479 <code>

Control-F for "DELETE FROM SYS.AUD$;"

Tom if we post two followups in a row because we made a mistake or whatever do you not see the second one? Is there any way to notify you that the most recent thing we posted is the question we want answered?

Ok that's cool, I got that impression.

I was interested to hear your thoughts on the above about deleting from aud$. I already made a costing mistake by playing around in sys once before...But if it's ok, then it will be helpful for me in testing auditing.

How the audit commands work is not very clear to me an requires experimentation. For example, "audit procedure;" would lead you to believe that would audit anything to do with procedures but so far creating them is the only thing that is showing up in aud$ and thus I'm filling up the table it would be easier to start from a clean slate by being able to delete.

Tom,

Lots of information/notes on metalink regarding how to move aud$ out of system tablespace, can i move it online as i am using 10.2.0.1.

Thanks.

Tom,

I was looking at this command "audit create procedure"
but my requirement needs auditing just one function ( if someone modifies or drops it). Is there any workaround
for DB Version 9.2.0.6 and up?

TIA.

Tom,

Maybe i wasn't clear enough the function i need audited belongs in the SYS schema please provide your valuable feedback.

TIA.

Weird audit requirement by auditors "how do you track if someone changes verify_function" :-)

These folks are most non technical with certification credits like CISSP or CISAA or etc etc ...

I'm looking for an minimum impact means of auditing update/insert/merge statements that DON'T come from a specific set of applications as a means to track unathorised data modifications.

I understand that this may not be foolproof, depending on how the application is identified. ie: If identified by V$session.program a smart "user" might think to change the name of the application executable to sidestep auditing.

Also helpful would be tracking user logons, to include time, application identifier and IP addresses.

We are in the process of reviewing our system security.

Regards,

We are running 9.2.

Each user logs on with their own credentials, so user name is unique per session - we can't filter by parsing schema. I think we must pick up some attributes of the application client(s) for identification, else set something from the application ( in a global context maybe? ) that can be picked up and interpretted by audit.

This is a client/server system.

We do not expect to have many audit records ( except for upgrades, and maintenance work ). The requirement is to track unauthorised data changes.

Cheers,

Hi Tom,

One of DBA setup following. Can you please sugegst is it valid?

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /data01/app/oracle/admin/KOUST
/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string NONE

AUDIT DELETE TABLE, INSERT TABLE, UPDATE TABLE, SELECT TABLE BY JAS BY ACCESS;

DB: 10g

I run the following sql to check the audit configurations.

DB: 10.1.0.5.0

SQL> select name, value from v$parameter
2 where name like 'audit%';

NAME VALUE
---------------------- ---------------------------------------------
audit_sys_operations FALSE
audit_file_dest /opt/db_1/rdbms/audit
audit_trail NONE


**********************************************
DB: 10.1.0.4.0

NAME VALUE
---------------------- ------------------------
audit_sys_operations FALSE
audit_file_dest /opt/db_2/rdbms/audit
audit_trail DB_EXTENDED


1) What are "audit_sys_operations " parameters?
Are they new to 10g?
If it is false, what is it mean?

2) In my DB #1 audit_trail is NONE and DB2 audit_trail equals to DB_EXTENDED.

what is the difference btw NONE & DB_EXTENDED values?

cheers,
Dawar

Hi Tom,

while I audit the database every entry has an errorcode ! What is the name of the table to resolve the code into an text ? Is there an table ?

Marcel

select action_name, returncode
from dba_audit_session;

ACTION_NAME RETURNCODE
--------------- ----------
LOGON 1017

At Unix prompt:

> oerr ORA 1017
01017, 00000, "invalid username/password; logon denied"

At SQL prompt, use sqlerrm function, for instance:

SQL> exec dbms_output.put_line(sqlerrm(-1017));
ORA-01017: invalid username/password; logon denied

Regards
Michel

Hi Tom,

when I try to use the audit options :

SQL> audit connect whenever not successful;
SQL> audit conext whenever successful;
SQL> audit session by <USER>;
SQL> audit all privileges by <user> whenever usccessful;

everything works until this point, but in the
DBA_AUDIT_OBJECT table aren't the entries generated by DROP, TRUNCATE and alter table move tablespace !

The views dba_priv_audit_opts and dba_stmt_audit_opts list
the audit command.

Thanks in advance
Marcel

Is ist possible that some statements only logged with the statement ID ?

Marcel

Hi Tom,

thanks for you're review...

I think I found the problem...

Until now I am only auditing with the all privileges option
The all privileges option audits only the create for the table audit.

audit all privileges by <USER> whenever successful;

because I need the CREATE TABLE for creating a table;

When I want to audit the DDL issued against the DB I must
audit the according DDL statement.

audit table by <USER> whenever not successful;
audit table by <USER> whenever successful;
audit sequence by <USER> whenever successful;
audit sequence by <USER> whenever not successful;
audit alter system by <USER> whenever successful;
audit alter system by <USER> whenever not successful;
audit procedure by <USER> whenever not successful;
audit procedure by <USER> whenever successful;
audit trigger by <USER> whenever successful;
audit trigger by <USER> whenever not successful;
audit view by <USER> whenever successful;
audit view by <USER> whenever not successful;
audit system grant by <USER> whenever successful;
audit system grant by <USER> whenever not successful;
audit create synonym by <USER> whenever successful;
audit create synonym by <USER> whenever not successful;
audit create synonym by <USER> whenever not successful;
noaudit select any table by <USER>;


I am right so far ?

Thanks again and Kölle alaaf :-)

Marcel

Hi Tom,

I am seeing the following and I'm trying to figure out if anyone besides sys is able to delete
"delete from sys.aud$" events from sys.aud$.

My setup is the following:

SQL> show parameter audit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/orcl/adu
mp
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB


I have the following auditing option turned on:

SQL> select * from dba_stmt_audit_opts;

USER_NAME PROXY_NAME
------------------------------ ------------------------------
AUDIT_OPTION SUCCESS FAILURE
---------------------------------------- ---------- ----------

CREATE SESSION BY ACCESS BY ACCESS


I am seeing the login/logout information as you would expect in sys.aud$. I am also seeing the
"delete from sys.aud$" records that I also expect. The problem I have is that "system" is able to
delete from sys.aud$ fine but it is unable to delete any of the "delete from sys.aud$" rows. Here
is an example:

devsol10: sqlplus system/******

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 20 17:05:36 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

-- show that there are rows in sys.aud$
SQL> select count(*) from sys.aud$;

COUNT(*)
----------
2

-- show I can delete the 2 rows
SQL> delete from sys.aud$;

2 rows deleted.

-- delete again
SQL> delete from sys.aud$;

0 rows deleted.

-- show that 2 delete from sys.aud$ events are still there.
-- shouldn't there just be 1?
SQL> select t2.name, t1.obj$creator || '.' || t1.obj$name
2 from sys.aud$ t1, audit_actions t2
3 where t1.action# = t2.action;

NAME
----------------------------
T1.OBJ$CREATOR||'.'||T1.OBJ$NAME
--------------------------------------------------------------------------------
DELETE
SYS.AUD$

DELETE
SYS.AUD$


SQL> select count(*) from sys.aud$;

COUNT(*)
----------
2

-- connect as sys and show that it can delete the rows
SQL> connect sys/oracle as sysdba;
Connected.
SQL> delete from sys.aud$;

2 rows deleted.

SQL> select count(*) from sys.aud$;

COUNT(*)
----------
0


I have 2 questions:
- Is this intended behavior?
- Is there a permission I can give to someone that is non-sys to delete these records from sys.aud$?

Thanks,
Sandeep

Tom,

I want to be able to capture any changes to the audit policies.
To "Audit The Auditor" if you will.
According to the documentation, the best you can do is set "audit_sys_operations=true".
But you can enable/disable auditing by users other than sys.

How can I audit the auditor to make sure all modifications, etc. to the audit policies are captured?

Thanks,

Robert.

Has anyone written something to parse the OS files created when you set audit_sys_operations=true (SQLLDR/Perl/shell/etc) ?

I want to move these files offsite and load them into another Oracle database.

To Dave from Des Moines,

Why not use dbms_lob to read the files?

Hi,

how can I disable these auditing options ? I am little bit confused !!!

It's Version 9.2.0.7.0

CREATE RULE
CREATE ANY RULE
ALTER ANY RULE
DROP ANY RULE
EXECUTE ANY RULE
MANAGE ANY QUEUE
ENQUEUE ANY QUEUE
DEQUEUE ANY QUEUE
ADMINISTER RESOURCE MANAGER
CREATE EVALUATION CONTEXT
CREATE ANY EVALUATION CONTEXT
ALTER ANY EVALUATION CONTEXT
DROP ANY EVALUATION CONTEXT
EXECUTE ANY EVALUATION CONTEXT
CREATE RULE SET
CREATE ANY RULE SET
ALTER ANY RULE SET
DROP ANY RULE SET
EXECUTE ANY RULE SET

Regrads
Marcel

Hey Tom,

Requirement: 
-------------------------
We want to audit all DML operations done on a table by the application users. There are several application users who do the DML and it is required to track which application user, did what DML operation. 

When connection is made to the database, the schema to which all the application users connect is the one and the same ¿ i.e. we use PUBLICUSER credentials for establishing DB connection for all different application users.

Therefore the detail of which application user is performing the DML is not available for oracle. It is available in the application code. Our primary requirement is that we need to have the detail of the application user doing the transaction to be passed into the database so that we can audit the DML operations done.

To take an example:

Let¿s say that the table to be audited is EMP which is in SCOTT schema:

SQL> desc EMP
 Name                                      Null?     Type
 -----------------------------------------   --------  ----------------------------
 EMPNO                              NOT NULL  NUMBER (4)
 ENAME                                               VARCHAR2 (10)
 JOB                                                 VARCHAR2 (9)
 MGR                                                 NUMBER (4)
 HIREDATE                                             DATE
 SAL                                                 NUMBER (7,2)
 COMM                                                NUMBER(7,2)
 DEPTNO                                              NUMBER(2)

Let¿s say that there are 4 application users; user1, user2, user3 & user4. Now when they connect to the database, the connection is to schema SCOTT. So all the 4 application users will connect to the same db schema `Scott¿.  The app users will do some transactions; they may roll it back etc. And we need to track that. ¿ The tricky part being that we need to track which application user has done the DML. 

We are planning to track the Edit Audits through Triggers. Before the actual Data record Insert into the EMP table the trigger should insert the Record into the Audit Table, along with the UserID.

Can you please let us know the alternative options?

We used the approach of using
SYS_CONTEXT 
Global Temporary Tables which are transaction specific.
to store the UserID.

But then, the approaches did not workout as we have distributed transactions (under the COM+ Environment).

Tom,

Thanks for your reply.

But then I had few questions on Set Identifier.

Let us say I have 3(insert) operations to be performed in a transaction.

Now, for every DML operation we explicitly get the Connection object, use it and release it. (Microsoft COM+ Distributed Environment).

Since we explicitly get a connection every time, and as we would have a different session every time do you think the set value will be retained across, since it is set in a session specific variable(CLIENT_IDENTIFIER).

Let me know at what level I have to set this identifier.

Please let me know your thoughts on using this in Microsoft OleDB ?

I have some problem using Audit Record.



Generally if we map a record to the audit Record, it will track the details of the table insert, update, delete.



While I update the record, it will insert two different row in audit record like Audit Action K and N.



But both are contain the same values..



I need the old and new value.



Can any one please give me the solution with this !

Tom ,

I would like to export the past 120 days records from AUD$ table in 10g . Which column should i use as a baseline for export based on sysdate ?
(like query=sysdate - .....)

-Vinod

Hello,

There are multiple Q/A appearing in a single entry when I search your site, so I am not able to write a review for the proper question.

In first follow up, you suggest Fine Grained Access Control for an auditing question. In other case, you correctly state that FGAC is not for auditing, but Fine Grained Auditing is.

I noticed you showed the noaudit command above with "noaudit create rule" ..

I'm curious, is there anything special needed for "ADMINISTER RESOURCE MANAGER" ?? or is it something I'm doing? or something else?

SQL > select * from DBA_PRIV_AUDIT_OPTS;

USER_NAME                      PROXY_NAME                     PRIVILEGE                                SUCCESS    FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
JORACLE                                                       ADMINISTER RESOURCE MANAGER              BY ACCESS  BY ACCESS

1 row selected.

SQL > noaudit ADMINISTER RESOURCE MANAGER;
noaudit ADMINISTER RESOURCE MANAGER
        *
ERROR at line 1:
ORA-00956: missing or invalid auditing option


SQL > noaudit ADMINISTER RESOURCE MANAGER by joracle;
noaudit ADMINISTER RESOURCE MANAGER by joracle
        *
ERROR at line 1:
ORA-00956: missing or invalid auditing option

Hi Tom,

When we use statement level auditing (sql_statement_clause from the doc http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4007.htm#g2274817 )
only the statements executed against the objects owned by the audited user are being logged in the audit trail. For example, when I say: "audit insert table, update table, delete table by SCOTT by access;" the audit trail records only inserts/updates/deletes done on tables owned by SCOTT. If scott inserts/updates/deletes on a table owned by another user, that does not get recorded in the audit trail. From the documentation, it appears that statement level auditing would be done on the particular statement issued by the audited user, without regard to the owner of the object, but that does not seem to be the case. Is it a misleading documentation or am I missing any syntax options here? If this is not possible by Oracle auditing, what is the other solution, triggers?

thanks,
RN

sys%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> drop table t;

Table dropped.

ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2> insert into t values ( 1 );

1 row created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> grant all on t to scott;

Grant succeeded.

ops$tkyte%ORA10GR2> connect / as sysdba
Connected.
sys%ORA10GR2> audit insert table,
  2  update table, delete table by SCOTT by access;

Audit succeeded.


sys%ORA10GR2> connect scott/tiger
Connected.
scott%ORA10GR2> delete from emp where 1=0;

0 rows deleted.

scott%ORA10GR2> delete from ops$tkyte.t;

1 row deleted.

scott%ORA10GR2> connect / as sysdba
Connected.
sys%ORA10GR2> column userid format a8
sys%ORA10GR2> column obj$creator format a10
sys%ORA10GR2> column obj$name format a15
sys%ORA10GR2> select statement, userid, obj$creator, obj$name from aud$;

 STATEMENT USERID   OBJ$CREATO OBJ$NAME
---------- -------- ---------- ---------------
        11 SCOTT    SCOTT      EMP
        16 SCOTT    OPS$TKYTE  T

Thanks for the reply Tom. Below is my testcase, any idea why I am getting different results?


SQL> sho user
USER is "SYS"

SQL> create user test_audit identified by test_audit;

User created.

SQL> create user test_schema identified by test_schema;

User created.

SQL> grant connect, resource to test_audit, test_schema;

Grant succeeded.

SQL> create table test_schema.t1 (n number);

Table created.

SQL> grant all on test_schema.t1 to test_audit;

Grant succeeded.

SQL> audit insert table, update table, delete table by test_audit by access;

Audit succeeded.

SQL> conn test_audit/test_audit
Connected.
SQL> insert into test_schema.t1 values (100);

1 row created.

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.
SQL> SELECT username,
extended_timestamp,
owner,
obj_name,
action_name
FROM dba_audit_trail
WHERE owner = 'TEST_AUDIT'
ORDER BY timestamp
/

no rows selected

SQL> conn test_audit/test_audit
Connected.
SQL> create table t2 (n number);

Table created.

SQL> insert into t2 values (100);

1 row created.

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.
SQL> SELECT username,
extended_timestamp,
owner,
obj_name,
action_name
FROM dba_audit_trail
WHERE owner = 'TEST_AUDIT'
ORDER BY timestamp
/

USERNAME EXTENDED_TIMESTAMP OWNER
------------------------------ ---------------------------------------------------------------------------
------------------------------
OBJ_NAME
------------------------------------------------------------------------------------------------------------------------
--------
ACTION_NAME
----------------------------
TEST_AUDIT 17-JUL-08 03.28.47.952368 AM +00:00
TEST_AUDIT
T2
INSERT


1 row selected.

==> AS YOU CAN SEE, IT RECORDS ONLY THE INSERT ACTIVITY TO THE TABLE OWNED BY TEST_AUDIT USER, NOT OTHERS. IS THERE
ANYTHING WRONG IN WHAT I AM DOING?


SQL> select * from dba_stmt_audit_opts where user_name = 'TEST_AUDIT';

USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS
FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
TEST_AUDIT INSERT TABLE
BY ACCESS BY ACCESS
TEST_AUDIT UPDATE TABLE BY ACCESS BY
ACCESS
TEST_AUDIT DELETE TABLE BY ACCESS BY ACCESS

3 rows selected.

SQL> select * from dba_priv_audit_opts where user_name = 'TEST_AUDIT';

no rows selected

Thank you verymuch.
RN

Thanks Tom! You are a genius, how can you spot it at one look? I was struggling with this since yesterday! Thanks so much for helping out!

Have a nice day!

Tom:

Do you like table designs that store audit data in same production table meaning after a user saves a record it becomes read-only. Any updates are keyed in as new records?

You sort of blocking the user from doing any updates to old records. but "old" includes any record saved.

Tom:

I have a requirement to implement audit trails for db applications to include: who accessed the db, what db was accessed, which records were accessed, changed or deleted.

1. Is there a way to implement this in oracle. That sounds like every select, insert, update, delete on any table needs to be audited which is going to affect performance in a big way.

2. for 1 user --> 1 db account I assume we can let oracle do this or write application code or create customized triggers? correct. How would do this with application table accounts.

Tom:

Can you summarize on when is it best to use oracle internal auditing versus creating custom tables/triggers. I am trying to decide which one to use for audit control.


It seems oracle dumps all audit info the admin defined in one big audit_trail table while custom editing requries table and a triger for each schema table that needs audit.

Tom:

There are two argumes to what you say in the industry:
1. When you use built-in features you locked yourself to that database vendor. in this case if you write in pl/sql you are still locked with oracle.
2. when you use built-in features you lower your potential sales and revenue that can generated by writing and maintaining code.


Do you know of general high level auditing needs that are not supported by Oracle. it sounds to me you can do everything with built-in features.

also, one key point is that we do not have DBA control over DB. this is done by another organization. We do have an account with DBA role though. If we went with Database builtin features are we going to be dependent on that organization. that would be bad decision as there are a lot of politics invloved. i think all they have to do is turn auditing on to DB and we can run the audut commands as needed. is this correct?

Tom:

I think the hickup that people have about locking themselves into he product are license costs. As you know Unix, Java, Language X are free of charge.

You know that Java has become the most popular language on planet because it is "portable" and "free". In theory it can run on any machine and any operating system, even though it is difficult to code in and pretty complex. so the industry did not pay attention to development and maintenance costs to Java thinking that my application can run with any database i can switch to.


For auditing, my needs are simple. I want to know when User A logged in to the database and what did he do. What data did he select, update or delete and when did he log out.

You can correct me if i am wrong. It seems I can simply set AUDIT_TRAIL=DB and audit all the tables in schema for all users (if i have one db account per user or set dbms_session.client_id if application user) and all the information is stored automatically for me in one audit trail table. All i have to do is query it.

The other alternative is to create one audit table and trigger for every stingle table which mean lots of tables and code for maintenance. This sounds a very poor and inefficient design that I can't ever imagine a need for.

here is the reference

http://www.devtopics.com/most-popular-programming-languages/

Tom:

I want to explore the builtin auditing features of oracle.
so i assume the first thing you do is set AUDIT_TRAIL=DB and then I can run the AUDIT command on users/tables etc to see how it works and check the different views oracle has. Is this correct?

2. I asked DBA to set the parameter up and he says they only do FULL auditing. Is there something called "FULL". never heard of this one.

3. if i set AUDIT_TRAIL=DB is it really usnig a lot of resourses or you use resourse when you actually run the AUDIT command and select what you want to audit.

Tom,

What would be the proper way to age out auditing records from aud$ if for argument's sake after awhile it starting using significant space?

Thanks.

Tom,
As I understand from the documentation, audit_trail='db,extended' will capture the SQL statement and bind information in addition to the standard audit information. We audit only failed attempts. Sometimes there is a need to audit statements. My thought was that changing audit_trail requires a database restart. If I can set audit_trail to db,extended, we will capture more information which might help in finding out what exactly was being done. We can always turn off writing to audit trail by noaudit command. Would setting audit_trail to db,extended be a good idea? Oracle 10gr2.

Thanks...

Hi Tom,

First of all, thanks a lot for sharing your wonderful ideas & solutions....!

I need your suggestion to finalize the approach for creating audit entries from PL/SQL procedures.

For auditing, our application stores entire row images (pipe delimited string) as pre-image & post-image in application audit tables. These audit tables have been created at module level, so one audit table has audit entries for multiple business tables (from same functional module). These audit tables have PREIMAGE, POSTIMAGE columns with VARCHAR2 (4000) limit.

Example can be: if for the employee table, job column is updated from 'Developer' to 'Manager',following entry will be created in application audit table(image in ordered sequence):
TABLENAME PREIMAGE POSTIMAGE
-----------------------------------------------------------
Employee 1|Clark|Developer 1|Clark|Manager


We may use Oracle audit vault in near future, but right now, this is the approach that has been implemented. And, we also do not want to use triggers as the main functionality i.e. UI application is already working fine. We need to implement this new approach only for business data updates from Procedures/Pro*C batches. For UI, we are using tool generated code, so whenever a new table is modelled, code is automatically generated to select entire row image as PREIMAGE/POSTIMAGE.
i.e. getimage function with following type of SQL is automatically generated:
<< EXEC SQL SELECT EMPLOYEE_ID ||'|'|| EMPLOYEE_NAME ||'|'||JOB INTO :h_Image FROM EMPLOYEE WHERE EMPLOYEE_ID = :h_EMPLOYEE_ID >>

Now, for our current requirement, I can think of following options(after reading your asktom links):
(a) `user-defined aggregate function (using the ODCIAggregate interface) with Analytic function¿ to return the pipe delimited list of column names. This can be used to create dynamic SQL to fetch entire row image.
(b) sys_connect_by_path
(c) Collect Function introduced in Oracle 10g
(d) write a procedure similar to your print_table procedure, which can return pipe delimited string for entire row image of a business table
(e) Create a new reference table. This new table can store TABLE NAME, pipe separated Column string (e.g. EMPLOYEE_ID||'|'|| EMPLOYEE_NAME ||'|'||JOB ) for all the application tables. So, we will be able to create a dynamic SQL by using this ref table.
(f) write separate functions(static SQLs to return row image) for each table..!! this seems a bit difficult ..i.e. to introduce so much of code in our application as we have 300-400 business tables.
(g) Hard-code the column names in procedures/batches. This may result in best performance, but it will not be recommended approach as we get a lot of change requests for our application and tables may be altered as per future requirements.

Which option do you suggest?? Also, are we taking the right approach..???

Some of the stats are:
(in development envt, stats did not differ much)
Option (a): takes 19 msec in Production like envt(less time because of parallel processing??)
SELECT
collist
FROM (
SELECT case when row_number() over (order by COLUMN_ID)=1
then agg_strcat_delim(concat_expr(COLUMN_NAME,'||''|''||'))
over (order by COLUMN_ID
rows between unbounded preceding
and unbounded following)
end collist
FROM user_tab_cols
WHERE table_name='EMPLOYEE'
and data_type NOT IN ('BLOB', 'CLOB','RAW')
and hidden_column<>'YES'
)
where collist is not null

Option (b): takes 94 msec in Production like envt.
WITH
col_data AS
(SELECT column_name, column_id, count(*) over() cnt
FROM user_tab_cols
where table_name = ('EMPLOYEE')
and data_type NOT IN ('BLOB', 'CLOB','RAW')
and hidden_column<>'YES'
)
SELECT ltrim(sys_connect_by_path(column_name, '||''|''||'), '||''|''||') collist
FROM col_data
WHERE column_id = cnt
START WITH column_id = 1
CONNECT BY PRIOR column_id = column_id-1

Hi Tom,

Thanks for your feedback and suggestions...

Ours is an old application.. and changing the auditing approach completely is in pipeline but it may take 1-2 years..!!

Missing "for update" should not be a concern for UI. I forgot to include the version column in my employee table example. We have implemented optimistic locking and all the business tables have a version column(i think, this method is also mentioned in your book). So, whenever business data is updated, this version check is used. If zero rows are updated, then transaction is rolled back. Also, all this code(version check, getpreimage etc.) is tool generated..so I think, right now, we do not have any major issues from UI..

Now, ok.. I think my actual requirement was lost in my long story..!!
Current requirement is to finalize the Audit approach for stored procedures.
Initially we did not have stored procedures in our application(only a few). But now,for the new funtionality being added to our application, we have also used stored procedures Right now, these procedures do not use standard auditing approach. Whenever audit entries need to be created(e.g. insert into HR_AUD where HR is module name), pipe delimited string is created by concatenating all the column values of a business table(for single row on basis of primary key). So, the column names are hard-coded in all the procedures.
e.g. if one business table is updated from 10 procedures, these 10 procedures will have the code to create preimage or postimage(something like EMPLOYEE_ID ||'|'|| EMPLOYEE_NAME ||'|'||JOB <<<column names hardcoded).
Now, if the table is altered because of some business change request, its difficult to change the code in all the procedures(nobody does actually). So, preimage & postimage in these cases will not be correct as the values for new columns will be missing. Also, if the column position changes, then also the row images will not be correct.
So, we want to use some standard approach to create this preimage/postimage (e.g. whenever we update a business table from stored procedure, we can call something like getpreimage/getpostimage on the basis of Primary key).
Also, we do not want to use triggers..
Options I have mentioned above(options from a to g mentioned above- in my last post) are different options (which I could think of) to meet this requirement and to select pipe delimited pre/post image from business table. Can you please refer to these options and recommend the best approach. Also, is there any better/easy way to meet this requirement??

Thanks again,
Anamika

Thank You so much.!!
I'll plan to implement "generate a procedure that "audits" table T". Thanks again.

Anamika

Hi Tom,
I am using bellow code to audit a table.
DBMS_FGA.ADD_POLICY (
OBJECT_SCHEMA => 'TEST',
OBJECT_NAME => 'MY_TABLE',
POLICY_NAME => 'FGA_AUDIT_TEST',
AUDIT_CONDITION => 'PROGRAM NOT LIKE ''PROGRAM1%''
audit_column_opts => DBMS_FGA.ALL_COLUMNS,
statement_types => 'SELECT,INSERT,UPDATE,DELETE');

The procedures completes successfully but when i run a query on audited table it returns error:
ORA-28112: failed to execute policy function

Can u please suggest what is wrong in this ??
Thanks in Advance
Faizan

We have a requirement where we want to boot the connections out if they are not coming from a specific server/IP. We also want to set a flag that will get saved in the audit trail (create session is audited)
We tried to use the DBMS_SESSION.SET_IDENTIFIER to set a flag, but this is not getting set since the session is stoped before even created "RAISE_APPLICATION_ERROR".

Is there any other way to achieve this with out having a custom audit trail table?

Here are the steps we followed (version 11.1.0.7 )

$: sqlplus /

SQL> create user user1 identified by user1;

User created.

SQL> grant create session to user1;

Grant succeeded.


SQL> CREATE OR REPLACE TRIGGER Verify_user_Source AFTER LOGON ON DATABASE
2 DECLARE
3 vMachine VARCHAR2(64);
4 vOSuser VARCHAR2(30);
5
6 BEGIN
7
8
9 IF UPPER(Ora_Login_User) = 'USER1' THEN
10 /* Get the machine name. Only connections coming from specified machines can get in. */
11 SELECT Machine,osuser
12 INTO vMachine,vOSuser
13 FROM V$Session
14 WHERE AudSID = USERENV('SESSIONID');
15 /* If they're not coming from an authorized host, then boot them out! */
16 IF LOWER(vMachine) not in ('server1') THEN
17 /* set the flag in the client_identifier field. */
18 DBMS_SESSION.SET_IDENTIFIER('Policy Violation');
19 RAISE_APPLICATION_ERROR(-20001, 'You are attempting to access a restricted account from a non-authorized host.
20 This action has been logged and notification sent to the DBA Group.');
21 END IF;
22 END IF;
23 END;
24 /

Trigger created.

SQL> exit

-- connect as user1
remote_serer:SQL> sqlplus user1/user1@RL1DB1

ORA-0064: error occurred at recursive SQL level 1

--connect back as dba and check the dba_audit_trail
$: sqlplus /
SQL>

select os_username, username, userhost, action_name, client_id from dba_audit_trail where username='USER1'

kml021 USER1 5N2VPD1 LOGON <null> --> ***the client_id is not set

--Modify the trigger and comment out the RAISE_APPLICATION_ERROR part to let the user connect

SQL> CREATE OR REPLACE TRIGGER Verify_user_Source AFTER LOGON ON DATABASE
2 DECLARE
3 vMachine VARCHAR2(64);
4 vOSuser VARCHAR2(30);
5
6 BEGIN
7
8
9 IF UPPER(Ora_Login_User) = 'USER1' THEN
10 /* Get the machine name. Only connections coming from specified machines can get in. */
11 SELECT Machine,osuser
12 INTO vMachine,vOSuser
13 FROM V$Session
14 WHERE AudSID = USERENV('SESSIONID');
15 /* If they're not coming from an authorized host, then boot them out! */
16 IF LOWER(vMachine) not in ('server1') THEN
17 /* set the flag in the client_identifier field. */
18 DBMS_SESSION.SET_IDENTIFIER('Policy Violation');
19
20 /*RAISE_APPLICATION_ERROR(-20001, 'You are attempting to access a restricted account from a non-authorized host.
21 This action has been logged and notification sent to the DBA Group.');*/
22
23 END IF;
24 END IF;
25 END;
26 /

Trigger created.



SQL> exit

--Now try to connect as user1

remote_server:SQL>sqlplus user1/user1
Connected to:

SQL> exit


--Check the audit trail now
$: sqlplus /
SQL> select os_username, username, userhost, action_name, client_id from dba_audit_trail where username='USER1'

kml021 USER1 5N2VPD1 LOGON <null>
<null> USER1 5N2VPD1 LOGOFF Policy Violation --> the client_id is populated (on the logoff event)

Dear Tom,
We have a JAVA application which is totally dependent on a Oracle 9i database. We don't have source code for that application. In fact we are finding descripancies in our data but don't know whats happening inside. Is it possible to track all the database operations being done by that JAVA application. Is using SQL_TRACE the correct solution or Oracle provided any other utility also.

Thanks in advance.

If you set audit_sys_operations to TRUE, how do you protect OS audit files stored in AUDIT_FILE_DEST being tampered with by Oracle admin users?

We're using Oracle 11g R2 and notice that there's even a DBMS_AUDIT_MGMT package which allows you to purge aud$ and OS audit files!

We're currently just using the directory pointed to by AUDIT_FILE_DEST, not writing to SYSLOG

Even if DBA or SYS don't have logon to the server where the directory resides, does DBMS_AUDIT_MGMT or indeed something like UTL_FILE can bypass the need for this as they can manipulate the files from the database? Can we prevent this?

If not, is writing to SYSLOG what you recommend?

Hi Tom,
What is needed to do to know the audited users from the database ?

I checked that this sql should do the answer:
SELECT distinct (USER_NAME) FROM DBA_STMT_AUDIT_OPTS;

But, when i check the dba_audit_trail we have more users than the query above returns.

Using Oracle 10.2.0.5

Thanks

Hi Tom;

Thanks for sharing your knowledge.

I have a question concerning COMMIT and ROLLBACK in audit trails.
The problem is, that any transaction issued and than rolled back, appears in the audit trails, however they have physically never been applied. On the other side, the ROLLBACK statement is not visible in the audit trails. So I can't say afterwards, whether the transaction has been commited or rolled back. I tried to check via transaction ID (XID column) however I didn't get it.
Is there any possibility to tell transactions, that have been commited, appart from rolled back transactions?

thanks,
Johann

Hello,

At the database level, the audit file max size is set to 100MB.  However, I see very small xml files generated on my audit file destination (size around 2KB).  Why is is this so? Our database is 11.2

select * from dba_audit_mgmt_config_paramsSQL> SQL> SQL> SQL>
  2  ;

PARAMETER_NAME                 PARAMETER_VALUE                AUDIT_TRAIL
------------------------------ ------------------------------ --------------------
DB AUDIT TABLESPACE            SYSAUX                         STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE            SYSAUX                         FGA AUDIT TRAIL
AUDIT FILE MAX SIZE            102400                         OS AUDIT TRAIL
AUDIT FILE MAX SIZE            102400                         XML AUDIT TRAIL

Some of the files in our folder:

-rw-r--r--   1 oracle     dba           2470 Oct 20 23:55 mdcp9p4_ora_991_3.xml
-rw-r--r--   1 oracle     dba           3472 Oct 20 08:12 mdcp9p4_ora_9920_1.xml
-rw-r--r--   1 oracle     dba           2405 Oct 21 08:10 mdcp9p4_ora_9920_2.xml
-rw-r--r--   1 oracle     dba           3472 Oct 21 15:04 mdcp9p4_ora_9920_3.xml
-rw-r-----   1 oracle     dba            734 Oct 22 17:09 mdcp9p4_ora_9921_1.aud
-rw-r--r--   1 oracle     dba           2504 Oct 20 08:12 mdcp9p4_ora_9921_1.xml
-rw-r-----   1 oracle     dba            734 Oct 22 18:18 mdcp9p4_ora_9921_2.aud
-rw-r--r--   1 oracle     dba           2504 Oct 21 02:56 mdcp9p4_ora_9921_2.xml

Thanks,

Yes they are from sysdba logins. In those file names, does the number 9921 stands for process ID?

Would the setting of xml file size to 100MB doesn't make sense? Can an XML file wait until it reaches 100MB (for example) and then spawn a new file? We are seeing 10's and 1000's of files generating each day and most of them are under 3KB.

Thanks,

Thanks. Over here, we all login as ourselves on UNIX box and then switch user to oracle. To get into the database, most likely we all do "sqlplus / as sysdba". There shouldn't be thousands of manual access but I wonder why thousands.


About the audit files - if we try the procedure below, I think it still doesn't make sense in terms of the size of the XML file. Right?

BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_property(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
audit_trail_property => DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,
audit_trail_property_value => 15000);
END;

Thanks,

Tom:

<<stop using sysdba - seriously - it is a really bad idea, a horrible practice. >>

Do you normaly set individual user accounts with DBA ROLE instead for DBAs? There are some features that are not even supported by DBA role and you must use log in as sysdba.

Also, I recall from one of the oracle classes that even though a system admin should not have access to oracle database he can log in as root and maniuplate files. He also has total control over the oracle account or can create other unix accounts. Can you really control that other than auditing O/S actitivites and writing logs to another machine that sysadm does not have control over.

Tom,

You have written many times about "things people think they need SYSDBA for but do not" etc. I would be very interested to read about your thoughts on "things people think they need to log onto a database server for but do not".

How about starting/stopping a listener? Adding instances to the server's tnsnames for database links or what have you.

This is kind of the point to my original question, many people may not realize these things.

However, continuing to play Devil's advocate, you're right I might only need to do these things 0.01% of the time. But guess what happens that time the listener stops running (it happens, seen it a handful of times in the last couple of years) and no one can log on to the box to fix it. The downtime then becomes infinity worse, and problem management is going to come looking for me as to why this happened and how to prevent it in the future.