As an alternative to auditing logons/logoffs, you might as well create LOGON and LOGOFF triggers that populate a regular history table. I myself prefer it. It's far simpler:

create table log_trail (name varchar2(30), time date, action varchar2(10));

create or replace trigger tr_logon
after logon on database
begin
insert into log_trail values (user, sysdate, 'LOGON');
commit;
end tr_logon;

create or replace trigger tr_logoff
before logoff on database
begin
insert into log_trail values (user, sysdate, 'LOGOFF');
commit;
end tr_logon;

Notes:

- Beware of logon triggers. If they are not working, you may not logon to Oracle.

- You must have the CREATE (ANY) TRIGGER and ADMINISTER DATABASE TRIGGER privileges to implement DATABASE triggers.

Just a quick comment. As Tom said, while you cannot prevent the application from being copied, you can make it very difficult to 'get working'. As an example, your application could check the SID of the database, and only work against a certain database. Other things to check could include: The IP range of the client machines, the domain of the database, something pulled out of the client registry, etc...

The problem is handling of application-users wich could be connected from diferent places and through multiple sessions. It is possible for example with:
-- Trigger LOGONAUDITING
CREATE OR REPLACE TRIGGER logonauditing
AFTER LOGON ON application.schema
DECLARE
machinename VARCHAR2(64);
osuserid VARCHAR2(30);
session_id_part1 NUMBER;
session_id_part2 NUMBER;
CURSOR c1 IS
SELECT osuser, machine, sid, serial#
FROM v$session WHERE audsid = userenv( 'sessionid' );
BEGIN
OPEN c1;
FETCH c1 INTO osuserid, machinename, session_id_part1, session_id_part2;
INSERT INTO LOGON_table(osuser, machine, logon_time, session_id_1, session_id_2)
VALUES ( osuserid, machinename, sysdate,session_id_part1, session_id_part2);
CLOSE c1;
COMMIT;
END;
/
- similar is needed for log off, and now You have all elements for corect counting of spent time of any user

Using a database trigger just adds two more objects (the trigger and the table) into the database. Also, it won't work for the user's 7.x databases. It won't track unsuccessful logon attempts (which may or may not be important). Andre rightly points out that the trigger may be able to prevent logons if it is not working...

Yes, Tom You are right audit connect is the easiest way,
this will help me in the My new security Application but
could you please how to find audit_trail table I tried but I didn't find it ,or you mean we have to create it .

For the trigger above I think he created it by hime self it not oracel system table .

First, I have worked a lot with Oracle auditing in very controlled environments, and managing audit trails is not definately something you would like to do on a sunday morning.

If you are auditing myriads of events, they get interleaved in the views and it takes little time before you burn out brains out, believe me.

Auditing is an art. Carefully defining its scope may be one of the most difficult things to do against the database.

Rule of thumb: Keep the audit trail as simple as possible. Focus on the events your organization procedures have defined as relevant.

If you think LOGON/LOGOFF event are part of your organization's auditing scope, then go ahead...

Creating these triggers and the table adds too little complexity to your environment, and you can customize the code your own way.

Yes, indeed, you cannot audit successful/not successful events, but you have to consider that you can also improve your code in other directions by programming additional features, like the other example above with multiple sessions.

As to the mantra, sorry but, despite being an Oracle afficionado, there are a lot of features that are terribly implemented in the DBMS and you cannot just think they are always better than some eventual code tailored to your needs.

There is a lot of extremely unfriendly stuff in Oracle, and I like to arrange my own environment.

> The end result from both: the same.

In 8i and up - but doesn't the original poster indicate that they have version *7* and version 8x? (Maybe the version 8x means 8i maybe it means 8.0.5 - doesn't say.) Seems to me that the question could use a bit more detail. For example, maybe they just want to track logon/logoff times of their Forms application only and not logon/logoff for all users of the database in general. In any case, the trigger option won't work (unless this feature snuck back into 7.x and 8.0.x) for all of the poster's databases, so the only solution that meets the needs of the original poster is to use database auditing as Tom is suggesting. I know I am being picky, but...

tom
I tried both options
audit connect is working fine for auditing logon and logoff of users. but how to know through which application user logged in at the same time.
There is one problem using logon and logoff triggers when a user have multiple instances, the session id that is captured during logon trigger is diferent from the session id that i am catching during logoff trigger, can u tell me why it is so.

I am not against your solution of course, but I would like to highlight power hidden in logon/logoff trigger solution and additional flexibility.
Our usage include loading of some additional fields (from user interface) in logon_table for e.g. country code and language code which brings very powerfully setup of cubs needed for user reports.

Hi Tom,
I tried a "after logon/before logoff" trigger method. It works fine when every user logoff normally. However, when a user logoffs abnormally, eg., logoffing by clicking the 'X' on SQLPLUSW interface, the logoff information, say, the time (date) will not be recorded by the trigger. When the user use "EXIT", all work as expected. So I tried the "audit connect" method.
When I was trying to use the "audit connect" method to audit user logon and logoff so that I know how a user had logoned to the database. After reading the document on this topic, I still have problem in getting the audit results. I guess I am missing somthing. I did the following:
1. Logon to the database as sys and issue command: audit connect;
2. connect to the database as any users;
3. select from dba_audit_trail (as sys user or any dba_user);

No rows returned. I even run the CATAUDIT.SQL. What else do I need to do to use the "audit connect" correctly? Can you please help me with the steps I need to go through to audit the connection (logon/logoff)?

Thank you in advance.

Tom,
Please ignore the above question (How can I get audit data after enable the audit connect ?). Metalink has the detail steps (Note:99786.1).
Thank you again.

I am finding that when a remote database connects to my database (via dblink) to refresh (complete) a snapshot, the audit trail shows LOGOFF BY CLEANUP for these sessions. Both DB's are 8.0.5.

Is it normal for these sessions to not explicitly disconnect?

Does it make any difference?

Thanks in advance.

on the subject of auditing, I would like to know if there is a way to trap login failures. i.e have an audit of failed attempts to log on to the database (invalid username or password)

thanks a lot. just what I needed.

I would also like to know if there is any way of setting up an environment where logon failures are recorded automatically... instead of having to run the 'audit connect whenever not successful' statement each time the database is restarted

thanks

please ignore the earlier question.

it remains persistent untill we explicitly say NOAUDIT

sorry... must be a slow day for me.. hee hee...

Tom,

I was under the impression that you can't commit in triggers?

e.g.
system@Dev9i> CONNECT SYS AS SYSDBA
Enter password: ********
Connected.
system@Dev9i> create table user_logon
2 (
3 user_name varchar2(30),
4 last_logon date,
5 constraint user_logon_pk primary key (user_name)
6 )
7 organization index
8 /

Table created.

system@Dev9i>
system@Dev9i> create or replace trigger logon_trigger
2 after logon on database
3 begin
4 insert into user_logon values ('test',sysdate);
5 commit;
6 end;
7 /

Trigger created.

-------------------------------------------
Then tried to logon :

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at line 3


Steven

Tom,

Thanks, that works.

Steven

Hello Tom !

I have requirement to have the log of logons & logouts if:

(1) The time is >= 9:00 PM
OR
(2) The day is Sunday.

Should i do it using auditing connect for that (if yes then how ) or just use the logon and logoff triggers ? In my opinion, triggers will be better.

Note that procedures at my remote client side are like :

select 1 into a from dual@DBLink_to_my_database;
if a=1 then
insert into mytab@DBLink_to_my_database
......
......
select ....
...
from LocalTable
where ,.....
;

update mytab@DBLink_to_my_database
set ....
....
...;

etc etc.

My understanding is that the statement:

select 1 into a from dual@DBLink_to_my_database

will be considered as two transactions (i.e; two records in my audit table): one for logon and one for logout. And for each statement using insert/select/update, there will be two rows in audit table. Am i true ?

Plz Advise

Riaz Shahid

Hi

I have following trigger:

create or replace trigger sa2_logon_trigger
after logon on database --when (user='SA2')
declare
iworkgroup varchar2( 150 );
begin
if (user = 'SA2' ) then
sa.set_context_sa2;
else
begin
select work_group
into iworkgroup
from table_employee
where objid =
( select objid from table_user where upper( login_name ) = user );
exception when no_data_found then null;
end;

if ( iworkgroup = 'Concert' ) then
raise_application_error ( -20001, 'Login denied.' );
end if;
end if;
end;
/

When specific user connects with sqlplus, gets:
<quote>
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Login denied.
ORA-06512: at line 17
</quote>

It's nothing wrong with that but unfortunately Clarify client shows just first line and it doesn't tell too much about real error:
"ORA-00604: error occurred at recursive SQL level 1"

I have no idea how to show just specific error message?

Igor

Hi tom,
in your solution,

quote:
create table audit_trail
as
select sysdate x$timestamp, rpad('*',20,'*') x$action, v.*
from v$session v
where 1=0
/
create or replace trigger logon_trigger
after logon on database
begin
insert into audit_trail
select sysdate, 'LOGON', v.*
from v$session v
where sid = ( select sid from v$mystat where rownum = 1 );
commit;
end;
/
create or replace trigger logoff_trigger
before logoff on database
begin
insert into audit_trail
select sysdate, 'LOGOFF', v.*
from v$session v
where sid = ( select sid from v$mystat where rownum = 1 );
commit;
end;
/
quote end.

one can find the pair of logon and logoff by using sid and serial#. But it's not as easy as a select statement to review the information.
First of all, I want to claim that I'd like to using user-defined-trigger instead of audit connect because I need to combine restrict logon time frame and restrict users.
I'm thinking the following solution.
create table audit_trail
as
select sysdate x$timestamp, rpad('*',20,'*') x$action, v.*, sysdate x$logoff_time
from v$session v
where 1=0
/
create or replace trigger logon_trigger
after logon on database
begin
insert into audit_trail
select sysdate, 'LOGON', v.*, null
from v$session v
where sid = ( select sid from v$mystat where rownum = 1 );
commit;
end;
/
create or replace trigger logoff_trigger
before logoff on database
begin
update audit_trail set x$logoff_time=sysdate
where sid = ( select sid from v$mystat where rownum=1 );
commit;
end;
/
the audit_trail table looks like pretty similiar with sys.aud$ now.
but logoff trigger will update all the rows having the same sid. How to solve it?

Thank you very much,

I am 100% agreed with Mr. TOM that we should use the functions if we have with the DB. For example if we have count(*) function then why we should code for it. We can utilize that time to some other activities. I just like to code if I don't find any solution from the DB which is very rare and happens when I don't have enough knowledge about that functionality.

For example earlier i was not using analytical functions but now I came to know from the discussion of Mr. TOM and his book that these are very useful and really these are very useful.

Now if someone is using 7,8 then to get extra features they should migrate to the new one. If someone is still using 7 then I don't think this is the fault of Oracle Co.

Dear Sikandar !

AOA

I wanna know from where u found the book of Tom ? Since its not available in pakistan (acccording to my knowledge)

Regards

You are right that the book is not available in Pakistan. I had checked from Islamabad, Lahore and Karachi. Then I purchased it from India which is Indian reprint but of good quality.

sikandar_h@hotmail.com

Being able to use the audit trail to keep track of user connections is wonderful. But what about when we're using a J2EE/Web Cache installation of iAS? The application server can create multiple connections for the user. Is there a way of setting the audit trail to keep only one entry, and not twelve?

In that case then, perhaps a trigger would be the better alternative? True, it asks us to code something which the database already can do, but it would also allow us to do only one insert, instead of twelve - say, if there is already a connection log within the hour. It would prevent the table from getting too big?

logoff trigger is only way to get v$pq_tqstat for the session which did pq work thru an application, correct? unless you are running your parallel query in sqlplus and have access after main job. Please clarify.

Thanks

Tom,
I would like to track when a user connects to a schema-based application. Since my db has multiple schemas, how would I use the "alter logon on schema" trigger to capture that userA connected to appA, appB, and appc?

It seems that the "after logon on schema" trigger is meant to track schema owners. How would I track non-schema owners that have been granted private synonyms to access the app?

Thanks,
ht

In Pakistan you can get this book from vengaurd Karachi.

Tom,
If I have 10 schemas in 1 instance and want to track when a non-schema owner user logons to the schema, can that be done with an after logon schema trigger?
Thanks,
ht

Tom,
Thanks for the clarification. In going through the doc, it seems that I need to modify my stored procs to track an application's usage instead of implementing triggers.

It seems that triggers cannot be used to track the stored procs a user has executed in an app. Instead, I'll modify the stored proc to write the user's name and sysdate to a table.

I had thought that "after logon on schema" triggers provided a way to track when a user accessed a certain schema.
Thanks,
ht

Thanks, I'll try packages but is there a way to keep my PSP env (loadpsp ...) if I use packages. In other words, would I still be able to load my psps if they're wrapped by packages?
Thanks,
ht

Tom,
Thanks again for the info. Would it be better to use packages if I call 4 psps to insert an entity? For example, the first psp displays a form that accepts the data to be entered and calls a second psp that displays session info in a header. The third psp parses the array entered and calls the fourth psp that does the actual insert.

If I should be using packages, is there a way to implement without abandoning the "loadpsp ..." method I've been using. It's nice to see the html and pl/sql commands in 1 readable file. If the benefits outweigh this development method, I will gladly move to packages.

Your advice is appreciated.

ht

Tom,
I took your advice and enabled auditing. 2 questions:

1. Is there a way to turn on auditing for a specific schema's objects? I'm getting way too much info by tracking "audit execute procedure by access" and would like to only see when my schema is accessed.
2. How would one prevent the %AUD% tables from fragmenting my system tablespace? Can I redefine the tablespace for the aud$ table to a non-system tablespace or is there another recommended method to handle this issue?

Thanks,
ht

Tom,
I did search your site, Metalink, and the doc and posed the question because it didn't seem that one can turn on auditing for a schema without explicitly auditing each object.
I will contact support about figuring out a way to get the *aud* tables off of the system tbsp.
ht

Hi Tom
When I try to access this topic
</code> http://download-east.oracle.com/docs/cd/A81042_01/DOC/server.816/a76956/audit.htm#1108

I am getting this error and the topic is unreadable  and it give this error message

http://otn.oracle.com/errors/404.html <code>
would you please help me how to get this topic display Properly

Best regards
Jamil Shaibani

Hi Tom,

If there are multiple logon triggers in the database, how do Oracle decide the execution order?

For example, one logon trigger sets sql_trace to false, and another one sets it to true. What will be the result?

Thanks.

We tried using Audit Connect and OEM's NUMEROUS logons to the database essentially filled up the sys.aud$ table and rendered an inability to connect to the instance. Of course this took a few weeks, but there must not have been any oem job that was setup to detect this situation. At that point, we changed the datafiles, and turned it off in favor of what we really wanted to do which was just know the last time a user logged into the system.

That and we were too lazy to figure out what should have been done with it. (oops)

Tom:

"audit connect" only able to get log on/log off time. However, my requriement is " record all the activities done by DBA role, and ignore any other users." e.g. If users in DBA role (inclued sys, and system), when they update/delete/modify table or tablespace .... whatever they did to the database must be record in details. Can Oracle support that? Thank you for your answer.

Tom,
I turn the AUDIT SESSION on and here is what i get after running a few days over the weekend (i updated the actual account names for my privacy). Acct5 is the application owner account and used by the middle tier. 
1)Could you explain what LOGOFF, LOGOFF BY CLEAN UP are?
2)Why is LOGON much smaller than LOGOFF? Isnt LOGOFF supposed be smaller than LOGON, otherwise where does LOGOFF come from if there is no corresponding LOGON?
3)It seems Oracle 9i document isnt clear about this, i checked REFERENCE and DBA Guide, no detail information was found with regard to ACTION_NAME. If you have a link for the apporiate document, could you please post here.

SQL> select ACTION_NAME, count(*) from dba_audit_session
  2  where username='ACCT05'
  3  group by action_name;

ACTION_NAME                   COUNT(*)
--------------------------- ----------
LOGOFF                          878651
LOGOFF BY CLEANUP                  474
LOGON                               13 

Quote "(indicating the middle tier
is connecting ONCE and keeping the circut open -- but auth/deauth over and over
for each request) "

It's hard to understand. Doesn't auth or deauth close the session? If the session is closed, how come there is a v$process entry(dedicated server)? If there is not SERVER process, how to keep the circuit open?

Thanks Tom!

Quote
A session can use many processes (shared server)
A single process can have many (0, 1, or more) sessions using it.


Tom,
I can understand one server process can serve more than one session in shared server mode. How can a session use many server processes in this mode?

My case is dedicated server mode, and my understanding is session vs. server process is 1 to 1 relationship. (here the session is the middle tier to the database , not the session of the end user to the middle tier).

Tom,
My first half question was how a session used more than one process?
Thanks

I was wondering how came a session could use more than one server process at the SAME time.

Hi Tom,
i need to identify the unknown user logon attempts my date.

i actually enabled audit session by user1,user2 ..... for all unknown user .

i was not sure i need to use audit session or audit connect.

kindly could you clarify what's different between these two and how to track the user after enabling the audit.

Thanks,

Tom,
I have a mult-tier applications. The users suppose to login through application, not by others such as sqlplus.
Can the audit trail (audit connect) show who used sqlplus to login the databases?
Thank you so much, you are great.

Mike

"
but short of that, you would capture the 'program' in v$session in a logon
trigger -- but you would have to just be aware that i can fake the program
simply by copying sqlplus to some other application name easily.
"
We have been inforcing the application login.
But we want to audit if there were any other ways intruders' logining.

"you would capture the 'program' in v$session in a logon
trigger"
Do you mean the functionality built with audit trail? Or I have to write it? I want to take advantage of exist of it.

Hi Tom,
i would like to track the user turnon create session.
iam trying to audit create session by access.

1)how to turn this for specific users.
2)if they succesfully logged in then need to track there info to disable the user.

Thanks in advance

Hi tom,
I will make clear, I read the chapter in admin guide.

i need to audit the create session.

audit create session by access;

but i need to do that for specific users like, tom,tomkyte,kyte.

how to do that.

Kindly could you explain with example.

Thanks in Advance

Tom,

Thanks i got it.

Audited successfully.

audit created session by user1 by access;

Thanks,

Hi tom,
i have enabled audit create session by unknownuser1,2 by access;

now i need to track who are the unknown user logged in.

how to do that.

Kindly could you pls give it with example tom

Thanks,

Hi Tom,
iam trying to get logged on unknown user by

select * from dba_audit_session
where action_name = 'LOGON';

is that correct way, if not not kindly correct me.

Thanks,

Hi Tom ,
Yes unknown user means , currently we are creating user with there emp id. in oldder day we did with out that some are using some or not . some user are out from company ex. emp. user apart from ex. emp and current users are all unknown user. we would like to track who is using the unknown ids.

Hope it was clear now.

thanks,

Hi Tom,

Ok as you said think that there are 1000 users currently.

500 users active user with emp id attached with there handle.
300 users already left the organisation.

remaining 200 users are unknown users. In that 200 handle may be the old handle of active users and some may be old handle of ex-emp.

i would like to audit for those 200 user.
1)when they logon.
2)How many times they are logging on.

Thats it.
"Clear like pure water now"

Not sure I am following, but here is a stab at it...

Is this close?

1000 users in the user population

500 active users have an id that is known
300 users with a known id leave the organization

You know that the user ids for those 300 individuals are "inactive" or can be accounted for (excuse the dangling preposition).

You are not sure about the remaining 200 user ids. It could be that "User 101" currently has a user id that previously belonged to "User 201" before "User 201" left the organization? Are user ids being recycled? You want to audit for logon of a set of user ids?

- Mark

Not sure if I got it right but anyway..
There are 1000 users. The known users are
500 active users which were created using the emp-ids
300 users that left the company ( created using emp_id)

Out of the remaining 200 users, it could be
users created without using emp_id but those guys are still working for the company
or
users created without using emp_id but those guys have left the company.


So, I think he wants to find out those people still using their old ids or ids that are still being used even after those employees have left the company.

Sounds like all users which are not linked to emp_ids to me.

Is that any good? :-)

Cheers

Tom,
1) Is it possible to audit failed logon/off ONLY? I mean that I dont want to have successful logon/off data in the audit trail.

2) If 1) is not possible, what are the options to clean the successful logon/off data in the audit trail since it grows too fast. Now, I'm using scheduled job to delete them, i.e.

SQL> select what from user_jobs;

WHAT
-----------------------------------------------------------------------
-- This job is used to clean AUDIT TRAIL
begin
 -- remove sussessful actions like LOGOFF, LOGFF BY CLEAN UP
 delete aud$ where returncode=0 AND action# IN (101,102);
 commit;
end;

do you have any better options with better performance? I was thinking about TRUNCATE but since the system logon/off is pretty active, it's not practical to lock the table for TRUNCATE.

Thanks! 

ops$tkyte@ORA9IR2> select count(*) from system.aud$;
 
  COUNT(*)
----------
         0
 
ops$tkyte@ORA9IR2> audit connect whenever not successful;
 
Audit succeeded.
 
ops$tkyte@ORA9IR2> connect /
Connected.
ops$tkyte@ORA9IR2> select count(*) from system.aud$;
 
  COUNT(*)
----------
         0
 
ops$tkyte@ORA9IR2> connect foo/bar
ERROR:
ORA-01017: invalid username/password; logon denied
 
 
Warning: You are no longer connected to ORACLE.
ops$tkyte@ORA9IR2> connect bar/foo
ERROR:
ORA-01017: invalid username/password; logon denied
 
 
ops$tkyte@ORA9IR2> connect /
Connected.
ops$tkyte@ORA9IR2> select count(*) from system.aud$;
 
  COUNT(*)
----------
         2
 
 

audit session
or
audit connect ?

what is the difference ? which is better ?

i see only audit session in documented in the admin guide. i can see a subset of the audit trail in dba_audit_session.

I'm using "audit session".

How to purge audit records older than, say, 3 months ?

Thanks

Please take a look:

  1  select ACTION_NAME, username, count(*) from dba_audit_session
  2  where username like 'DB_SRV%'
  3* group by action_name, username
SQL> /


ACTION_NAME                 USERNAME                         COUNT(*)
--------------------------- ------------------------------ ----------
LOGON                       DB_SRV_SAS                              2
LOGON                       DB_SRV_CONF                             6
LOGOFF                      DB_SRV_SAS                          38791
LOGOFF                      DB_SRV_CONF                         96120
LOGOFF BY CLEANUP           DB_SRV_SAS                           2405
LOGOFF BY CLEANUP           DB_SRV_CONF                         43499

6 rows selected.

This is an "AUDIT SESSION" running for 1 week. The applications above are purely client/server in perl using oci, no middle tier. I'm using dedicated servers only. 

1) Why so few logons/logoffs ?

2) The number of LOGOFF BY CLEANUP seems high to me. What can be causing it ? It means the processes is terminated abnormally ( like kill -9 ) ?

3) I have a rollback percentage close to 50 % in statspack. Maybe there is some relation ? 

1) there are 40,000 plus for db_srv_sas and almost 140,000 for db_srv_conf.  why so few?  don't understand how that is "few".

You do understand that each logon is turned into a logoff right.  consider:

  1  select ACTION_NAME, username, count(*) from dba_audit_session
  2* group by action_name, username
ops$tkyte@ORA9IR2> save a rep
Wrote file a.sql
ops$tkyte@ORA9IR2> @a
 
ACTION_NAME                 USERNAME                         COUNT(*)
--------------------------- ------------------------------ ----------
LOGON                       OPS$TKYTE                               1
 
ops$tkyte@ORA9IR2> connect /
Connected.
ops$tkyte@ORA9IR2> @a
 
ACTION_NAME                 USERNAME                         COUNT(*)
--------------------------- ------------------------------ ----------
LOGON                       OPS$TKYTE                               1
LOGOFF                      OPS$TKYTE                               1
 
ops$tkyte@ORA9IR2> connect /
Connected.
ops$tkyte@ORA9IR2> @a
 
ACTION_NAME                 USERNAME                         COUNT(*)
--------------------------- ------------------------------ ----------
LOGON                       OPS$TKYTE                               1
LOGOFF                      OPS$TKYTE                               2
 
ops$tkyte@ORA9IR2> connect /
Connected.
ops$tkyte@ORA9IR2> @a
 
ACTION_NAME                 USERNAME                         COUNT(*)
--------------------------- ------------------------------ ----------
LOGON                       OPS$TKYTE                               1
LOGOFF                      OPS$TKYTE                               3


see how logoff goes up, but logon does not (logon is showing how many are logged on right now)

2) anything that does not call OCISessionEnd would cause this.  could be an issue with your perl driver, or a crashed program.  For example, I crashed sqlplus using ctl-\ and logged back in:


ops$tkyte@ORA9IR2> /home/tkyte/bin/plus: line 6: 13609 Quit                    sqlplus /
[tkyte@tkyte-pc tkyte]$ sqlplus /
 
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Apr 12 10:41:24 2004
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
 
ops$tkyte@ORA9IR2> @a
 
ACTION_NAME                 USERNAME                         COUNT(*)
--------------------------- ------------------------------ ----------
LOGON                       OPS$TKYTE                               1
LOGOFF                      OPS$TKYTE                               3
LOGOFF BY CLEANUP           OPS$TKYTE                               1
 


3) I don't think so....  but if you do transactions that modify data -- you are doing tons of work only to undo tons of work (not very 'good') 

Congratulations on this awsome site ! ;)

Tom, I have "taken over" a database environment as a DBA and one of the problems we are facing is devising a way to track sessions as follows:

Issue #1: We have myriad clients all over the company connecting to my database. We have to make an inventory of all the scripts, machines, users, etc. coming in. We have turned on auditing (audit connect) and we are looking at dba_audit_trail and dba_audit_session. However, we are missing the program name and other info in the fashion it appears in v$session (like "sqlplus.exe"). It would be great !

Q1) How could we get the info that is in v$session and is not in the audit trail ? My hunch would be... logon triggers :)) ? Right ? I would end up creating a "custom" audit trail, and I HATE re-inventing the wheel ! :)

Just a comment: in time we plan on instrumenting all of these apps with dbms_client_info.

Issue #2: Once in possesion of all the allowed app names, we would like to create a table like:

Client hostname Username Os_username Program name
=============== ======== =========== ============

With 2 purposes:
1) Only registered users/apps could logon
2) Maintaing an on-line inventory of the client locations

We would need the program name above to authenticate the incoming connection.

Q2) We would have to do this with logon triggers too to validade it against the table, right ?

Q3) What do you think of this "authentication table" solution ?

Issue #3: This seems to be the worst part. Our manager wants us to enforce password complexity and expiring, which I too think is a necessary policy. However, all client apps have their passwords hard-coded in configuration files and the like. I don't like to have harcoded passwords around the company.

Q4) Do you know of any method to "hide" them ? Would you give me any ideas or point me to any docs ?

Q5) Besides, how could I deal with the fact of having to change those passwords in so many places ?

Thank you very much for your advice, Tom !

Dear Tom:
You are doing a great job. I always learn many things when i come to your site. My question is:

How can I get the information from my Database, that some user is using Forms 6i or Forms 9i? Previously V$SESSION was usefull but currently it doesnt show in the column PROGRAM in Oracle 9i Database.

I'm using 9.2. How do I get the program name (and possibly other columns) in v$session from the audit trail ? I'm using "audit session". If not possible, any workaround ?

Dear Tom,

I would like to get some of the information from v$session and store it in my Historical table. What is the best way to get the info without have a great performance impact on my database? I have thaught of writing database trigger (on insert) to get the info but doesn't know what is the performance impact like. Based on your exeperience, what would be the best way to retrieve the data from dict tables. Thanks.

Rgds
SHGoh

Do you sell Effective Oracle by Design book in Malaysia? I can only get Expert one-on-one Oracle.

Dear Tom,

sql address in v$session. I am trying to create a audit trail to keep track all the SQL that were performed by the session. Some suggestion from metalink on Doc ID: 72460.1 which recommend to turn on the oracle audit trail and write a trigger to get the sql address info when there is a update. I feel that this method might not suitable for OLTP environment as insert a lots IO. I wonder if you have any better suggestion on this matter. Thanks.

Rgds
SHGOh

Dear Tom,

Thanks for the info. However, ora_sql_txt and dmms_fga feature is only available in Oracle 9i++. Thanks.

Rgds
SHGoh

Hello,

Logon triggers with an insert statement can be very dangerous if you have a physical standby database that you need to open read only. It opens fine, but you can't login ;-).

How will Logon/Logoff triggers behave in connection pooling environment, where pre-defined number of connections are already established with the database? Will it fire for each login and logout?

We have a database running on 8.1.7.4 with a profile assigned to all the application users. We also have audit on for the logon and logoff.The requirement is to capture the "evidence" that a user's particular session was indeed terminated when it exceeded the idle time.. Please help.

Tom,
Thanks for the reply. Yes, it makes sense to run a dummy process and illustrate the sniped session, because of the profile.

Tom, I had another question. Can we move SYS.AUD$ to a different tablespace other than SYSTEM ? What will be the impact? Oracle version is 8.1.7.4.

Database: 9.2.0.5
OS: HP-UX 11i
Tom,
We had an issue couple of days ago..where we saw ORA-7445 errors in alert log. We opened a tar for it and came to a find out that somebody is trying (over and over!!) to connect to our database with a 7.3 client. They have finally stopped after trying a dozen times!!!.
I thought "audit connect" might help in this situation to tell
me where the connection is coming from (in the future)..

Question: Will audit connect still work considering this connection won't just fail but also is creating core dumps.

Thanks.

I dont know how to execute it will please forward the desire program it will be very useful for me in my project

is it possible to get macine name when there is only one schema and user is connecting from 5 different terminals
i have a problem to track update and deletes on schema objects but problem is of single user schema

SQL> audit ....;

the terminal is captured via audit.  look at dba_audit_trail, it is right there.

 

Thanks again!

Hi Tom,
"You do understand that each logon is turned into a logoff"
If the same entry of logon is turned into a logoff, how can we trace logons and logoffs separately ?

Is there any other way to track logon and logoff times ?

Thanks

look at the full record.

when you have a logon event, it looks like this:

-----------------
OS_USERNAME                   : "tkyte"
USERNAME                      : "OPS$TKYTE"
USERHOST                      : ""
TERMINAL                      : ""
TIMESTAMP                     : "28-jul-2005 09:10:33"
ACTION_NAME                   : "LOGON"
LOGOFF_TIME                   : ""
LOGOFF_LREAD                  : ""
LOGOFF_PREAD                  : ""
LOGOFF_LWRITE                 : ""
LOGOFF_DLOCK                  : ""
SESSIONID                     : "5942"
RETURNCODE                    : "0"
CLIENT_ID                     : ""
SESSION_CPU                   : ""


when that logs off -- it updates it to this:

OS_USERNAME                   : "tkyte"
USERNAME                      : "OPS$TKYTE"
USERHOST                      : ""
TERMINAL                      : ""
TIMESTAMP                     : "28-jul-2005 09:10:33"
ACTION_NAME                   : "LOGOFF"
LOGOFF_TIME                   : "28-jul-2005 09:12:10"
LOGOFF_LREAD                  : "2738"
LOGOFF_PREAD                  : "278"
LOGOFF_LWRITE                 : "18"
LOGOFF_DLOCK                  : "0"
SESSIONID                     : "5942"
RETURNCODE                    : "0"
CLIENT_ID                     : ""
SESSION_CPU                   : "26"


You have the logon logoff times tracked for you already. 

Hi Tom,

How to interpret the values of the column "OBJ_PRIVILEGE" ?

Thanks.

Hi Tom,
In the document you pointed, it says;
"OBJ_PRIVILEGE VARCHAR2(16) Object privileges granted or revoked by a GRANT or REVOKE statement"

But what are meaning of the values in that column ? How can we say what are privileges granted or revoked by looking at the value of this column ?
For eg. here are values in my dba_audit_trail.OBJ_PRIVILEGE;

---------N------
------G---------
----------G-----
G--G-GG--GGG----


Thanks in advance.

Ahh, now I understand, they map to the same thing as ses_actions

one for each action type in the order ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, REFERENCES, and EXECUTE. Positions 14, 15, and 16 are reserved for future use.


ops$tkyte@ORA10GR1> audit grant on t by access;

Audit succeeded.

ops$tkyte@ORA10GR1> grant alter on t to scott;

Grant succeeded.

ops$tkyte@ORA10GR1> select obj_privilege from dba_audit_trail;

OBJ_PRIVILEGE
----------------
Y---------------

ops$tkyte@ORA10GR1> grant delete on t to scott;

Grant succeeded.

ops$tkyte@ORA10GR1> select obj_privilege from dba_audit_trail;

OBJ_PRIVILEGE
----------------
Y---------------
---Y------------

ops$tkyte@ORA10GR1> grant index on t to scott;

Grant succeeded.

ops$tkyte@ORA10GR1> select obj_privilege from dba_audit_trail;

OBJ_PRIVILEGE
----------------
-----Y----------
Y---------------
---Y------------

ops$tkyte@ORA10GR1> grant insert on t to scott;

Grant succeeded.

ops$tkyte@ORA10GR1> select obj_privilege from dba_audit_trail;

OBJ_PRIVILEGE
----------------
-----Y----------
------Y---------
Y---------------
---Y------------

ops$tkyte@ORA10GR1> grant lock on t to scott;
grant lock on t to scott
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege


ops$tkyte@ORA10GR1> select obj_privilege from dba_audit_trail;

OBJ_PRIVILEGE
----------------
-----Y----------
------Y---------
Y---------------
---Y------------

ops$tkyte@ORA10GR1> grant select on t to scott;

Grant succeeded.

ops$tkyte@ORA10GR1> select obj_privilege from dba_audit_trail;

OBJ_PRIVILEGE
----------------
-----Y----------
------Y---------
---------Y------
Y---------------
---Y------------

ops$tkyte@ORA10GR1> grant update on t to scott;

Grant succeeded.

ops$tkyte@ORA10GR1> select obj_privilege from dba_audit_trail;

OBJ_PRIVILEGE
----------------
-----Y----------
------Y---------
---------Y------
----------Y-----
Y---------------
---Y------------

6 rows selected.

ops$tkyte@ORA10GR1> grant references on t to scott;

Grant succeeded.

ops$tkyte@ORA10GR1> select obj_privilege from dba_audit_trail;

OBJ_PRIVILEGE
----------------
-----Y----------
------Y---------
---------Y------
----------Y-----
-----------Y----
Y---------------
---Y------------

7 rows selected.
 

This is the information I was looking for. I searched through the documentations for more than 2 hours to get this information without any luck.

Hi Tom,

This site really a boon to us Oracle users...

through OEM can we see changed passwords
ie new password as well as old password

desc logonaudittable
Name Null? Type
----------------------------------------- -------- --------------------------
EVENT VARCHAR2(10)
SID NUMBER
SERIAL# NUMBER
TIMESTAMP DATE
USERNAME VARCHAR2(30)
OSUSERID VARCHAR2(30)
MACHINENAME VARCHAR2(64)
PROGRAM VARCHAR2(80)

Event can be 'LOGON' or 'LOGOFF'

If I use the following SQL for getting the logon and logoff details

select a.sid,a.serial#,a.timestamp LOGON_TIME,b.timestamp LOGOFF_TIME,trunc(a.timestamp) LOGON_DATE,trunc(b.timestamp) LOGOFF_DATE,
(b.timestamp-a.timestamp)*24*60*60 "Total_Duration(in seconds)", a.username,a.osuserid,a.machinename,a.program
from logonaudittable a, logonaudittable b where a.SID=b.sid and a.SERIAL#=b.SERIAL# and
a.EVENT='LOGON' and b.event='LOGOFF'

Some of the records are showing more than 1 year since I guess sid,serial# are getting repeated. How to get around this problem ?

thanks

select a.sid,a.serial#,a.timestamp LOGON_TIME,b.timestamp LOGOFF_TIME,trunc(a.timestamp) LOGON_DATE,trunc(b.timestamp) LOGOFF_DATE,
(b.timestamp-a.timestamp)*24*60*60 "Total_Duration(in seconds)", a.username,a.osuserid,a.machinename,a.program
from logonaudittable a, logonaudittable b where a.SID=b.sid and a.SERIAL#=b.SERIAL# and
a.EVENT='LOGON' and b.event='LOGOFF' and a.username=b.username and a.osuserid=b.osuserid

Even the above SQL gives a total duration of more than 1 year for some users

Hi Tom
I am little bit confuse on no of concurrent users
Can you tell me how can we found out the no of concurrent users at a given point of time from the DB

rgds
Praful

Hi Tom,

The V$license shows the following output

SESSIONS_MAX 0
SESSIONS_WARNING 0
SESSIONS_CURRENT 3275
SESSIONS_HIGHWATER 3444
USERS_MAX 0

Does this mean that 3275 are the concurrent users ??

Also the v$session shows the active sessions as 165
Does this mean that there are 165 concurrent users??

Hi Tom

thanx a lot
The info you provide is helpful to me

Thanx a lot once again

Tom,
Is there anyway without using audit to tell the last time a user logged in?

Is adding an index sys.aud$ in general an safe thing to do, if performance has been tested beforehand?

We saw hundreds of logon and logoff in the short period (within a minute) by looking at dba_audit_session. We would like to know from which program these sessions logon. V$session has the program column to provide such information. Is there similar information in the audit dictionary?

Thanks so much for help.

Sean

Hi Tom,

Is it normal for inacitve sessions to hang around for hours on end and not be reused in Oracle?

We have between 300-550 concurrent DEDICATED Server sessions in our application at any one time. Sometimes we will see sessions shoot up in numbers, yet there will be dozens and dozens of INACTIVE sessions that seem to never be reused.

I get this information from

08:59:39 HT4:PHYDB001082758:08:27 - LIVE>
08:59:58 HT4:PHYDB001082758:08:27 - LIVE> SELECT sid, serial#
08:59:59 2 ,TO_CHAR(logon_time, 'YYYY-MON-DD HH24:MI:SS') logon_time
08:59:59 3 ,REPLACE(
08:59:59 4 TO_CHAR(FLOOR(last_call_et / 3600), '00') || ':' ||
08:59:59 5 TO_CHAR(FLOOR(MOD(last_call_et, 3600) / 60), '00') ||
08:59:59 6 ':' ||
08:59:59 7 TO_CHAR(MOD(MOD(last_call_et, 3600), 60), '00'), ' ', ''
08:59:59 8 ) last_call_et
08:59:59 9 ,status
08:59:59 10 FROM v$session s
08:59:59 11 ORDER BY logon_time ASC
08:59:59 12 /

SID SERIAL# LOGON_TIME LAST_CALL_E STATUS
---------- ---------- -------------------- ----------- --------
1 1 2006-MAY-04 00:11:16 08:47:38 ACTIVE
2 1 2006-MAY-04 00:11:16 08:47:38 ACTIVE
3 1 2006-MAY-04 00:11:16 08:47:38 ACTIVE
4 1 2006-MAY-04 00:11:17 08:47:38 ACTIVE
5 1 2006-MAY-04 00:11:17 08:47:38 ACTIVE
6 1 2006-MAY-04 00:11:17 08:47:38 ACTIVE
7 1 2006-MAY-04 00:11:17 08:47:38 ACTIVE
11 1 2006-MAY-04 00:11:19 08:47:38 ACTIVE
12 15 2006-MAY-04 00:11:57 08:46:59 INACTIVE
40 1812 2006-MAY-04 03:39:12 05:19:41 INACTIVE
27 636 2006-MAY-04 03:39:19 05:19:35 INACTIVE
23 3968 2006-MAY-04 03:39:26 05:19:27 INACTIVE
43 1267 2006-MAY-04 03:39:26 05:19:27 INACTIVE
17 2890 2006-MAY-04 04:40:19 04:18:38 INACTIVE
15 2563 2006-MAY-04 04:55:21 04:03:35 INACTIVE
41 1982 2006-MAY-04 04:56:56 04:02:00 INACTIVE
29 4837 2006-MAY-04 05:52:09 03:06:47 INACTIVE
26 4319 2006-MAY-04 05:55:29 03:03:27 INACTIVE
21 11171 2006-MAY-04 06:15:26 02:43:33 INACTIVE
55 331 2006-MAY-04 06:19:11 02:39:44 INACTIVE
73 62 2006-MAY-04 06:25:39 02:33:23 INACTIVE
81 214 2006-MAY-04 06:28:53 02:30:02 INACTIVE
70 371 2006-MAY-04 06:33:45 02:25:08 INACTIVE
22 3133 2006-MAY-04 06:35:22 02:23:32 INACTIVE
45 2802 2006-MAY-04 06:38:19 02:20:32 INACTIVE
50 2019 2006-MAY-04 06:41:16 02:17:38 INACTIVE
57 1255 2006-MAY-04 06:41:16 02:17:38 INACTIVE
93 947 2006-MAY-04 06:43:33 02:15:28 INACTIVE
34 3590 2006-MAY-04 06:43:43 02:15:13 INACTIVE
91 570 2006-MAY-04 06:44:25 02:14:37 INACTIVE
59 1512 2006-MAY-04 06:49:26 02:09:27 INACTIVE
39 6805 2006-MAY-04 06:54:42 02:04:13 INACTIVE
69 1377 2006-MAY-04 06:54:49 02:04:05 INACTIVE
99 1148 2006-MAY-04 06:56:05 02:02:50 INACTIVE
20 5824 2006-MAY-04 06:56:42 02:02:12 INACTIVE
85 1289 2006-MAY-04 06:56:42 02:02:12 INACTIVE
61 2254 2006-MAY-04 07:00:12 01:58:42 INACTIVE
49 2835 2006-MAY-04 07:00:39 01:58:17 INACTIVE
88 1186 2006-MAY-04 07:01:58 01:56:55 INACTIVE
46 3568 2006-MAY-04 07:04:59 01:53:57 INACTIVE
120 402 2006-MAY-04 07:05:39 01:53:16 INACTIVE
130 451 2006-MAY-04 07:06:13 01:52:42 INACTIVE
117 586 2006-MAY-04 07:09:11 01:49:50 INACTIVE
67 1978 2006-MAY-04 07:14:50 01:44:03 INACTIVE
136 296 2006-MAY-04 07:16:00 01:42:59 INACTIVE
32 10429 2006-MAY-04 07:18:26 01:40:23 INACTIVE
33 4829 2006-MAY-04 07:20:04 01:38:53 INACTIVE
36 3828 2006-MAY-04 07:20:27 01:38:29 INACTIVE
113 1352 2006-MAY-04 07:20:46 01:38:12 INACTIVE
122 744 2006-MAY-04 07:24:47 01:34:07 INACTIVE
147 752 2006-MAY-04 07:25:15 01:33:38 INACTIVE
56 4354 2006-MAY-04 07:25:25 01:33:31 INACTIVE
112 1965 2006-MAY-04 07:25:49 01:30:09 INACTIVE
126 1723 2006-MAY-04 07:28:29 01:30:27 INACTIVE
95 1776 2006-MAY-04 07:29:32 01:29:22 INACTIVE
76 2408 2006-MAY-04 07:29:36 01:29:21 INACTIVE
159 429 2006-MAY-04 07:30:02 01:28:52 INACTIVE
44 4410 2006-MAY-04 07:30:25 01:28:30 INACTIVE
72 5165 2006-MAY-04 07:30:34 01:28:19 INACTIVE
148 1273 2006-MAY-04 07:30:48 01:28:06 INACTIVE
127 1465 2006-MAY-04 07:36:08 01:22:45 INACTIVE
96 1817 2006-MAY-04 07:37:11 01:21:43 INACTIVE
172 78 2006-MAY-04 07:37:53 01:21:00 INACTIVE
105 2690 2006-MAY-04 07:39:12 01:19:42 INACTIVE
79 3217 2006-MAY-04 07:40:26 01:18:27 INACTIVE
128 3418 2006-MAY-04 07:40:32 01:18:23 INACTIVE
42 6290 2006-MAY-04 07:49:38 01:08:01 INACTIVE
139 4097 2006-MAY-04 07:51:09 01:07:44 INACTIVE
104 2845 2006-MAY-04 07:52:37 01:05:54 INACTIVE
37 9683 2006-MAY-04 07:52:39 01:06:16 INACTIVE
173 3307 2006-MAY-04 07:54:13 01:04:41 INACTIVE
119 3021 2006-MAY-04 07:54:33 00:00:00 INACTIVE
197 1084 2006-MAY-04 07:54:37 01:04:19 INACTIVE
141 3360 2006-MAY-04 07:57:38 01:01:21 INACTIVE
163 4462 2006-MAY-04 07:58:39 01:00:14 INACTIVE
218 171 2006-MAY-04 08:01:00 00:58:01 INACTIVE
180 2812 2006-MAY-04 08:01:19 00:57:47 INACTIVE
230 204 2006-MAY-04 08:10:30 00:48:23 INACTIVE
220 228 2006-MAY-04 08:11:57 00:46:56 INACTIVE
254 775 2006-MAY-04 08:12:04 00:46:52 INACTIVE
264 327 2006-MAY-04 08:12:20 00:46:36 INACTIVE
266 609 2006-MAY-04 08:12:26 00:46:27 INACTIVE
129 2798 2006-MAY-04 08:13:34 00:45:19 INACTIVE
124 4355 2006-MAY-04 08:15:56 00:42:56 INACTIVE
110 6545 2006-MAY-04 08:16:55 00:41:58 INACTIVE
280 2469 2006-MAY-04 08:17:36 00:41:19 INACTIVE
133 4974 2006-MAY-04 08:19:25 00:39:29 INACTIVE
169 1902 2006-MAY-04 08:20:34 00:38:22 INACTIVE
152 7278 2006-MAY-04 08:22:24 00:36:31 INACTIVE
258 878 2006-MAY-04 08:22:54 00:36:01 INACTIVE
190 6849 2006-MAY-04 08:27:57 00:00:00 ACTIVE
201 5046 2006-MAY-04 08:29:12 00:29:42 INACTIVE
214 5215 2006-MAY-04 08:29:49 00:29:03 INACTIVE
265 3399 2006-MAY-04 08:30:56 00:19:44 INACTIVE
132 5940 2006-MAY-04 08:30:58 00:19:41 INACTIVE
284 1881 2006-MAY-04 08:32:02 00:26:52 INACTIVE
38 9481 2006-MAY-04 08:32:27 00:26:22 INACTIVE
200 1827 2006-MAY-04 08:34:21 00:24:32 INACTIVE
144 6879 2006-MAY-04 08:36:07 00:22:46 INACTIVE
184 7137 2006-MAY-04 08:36:15 00:19:43 INACTIVE
174 3437 2006-MAY-04 08:36:17 00:19:41 INACTIVE
263 1844 2006-MAY-04 08:36:36 00:21:49 INACTIVE
167 4813 2006-MAY-04 08:41:15 00:17:39 INACTIVE
315 747 2006-MAY-04 08:42:35 00:16:17 INACTIVE
30 10298 2006-MAY-04 08:42:38 00:16:14 INACTIVE
318 256 2006-MAY-04 08:43:54 00:00:04 INACTIVE
115 10689 2006-MAY-04 08:44:43 00:14:10 INACTIVE
248 2418 2006-MAY-04 08:46:42 00:04:32 INACTIVE
83 9023 2006-MAY-04 08:47:14 00:04:39 INACTIVE
176 6612 2006-MAY-04 08:47:47 00:01:06 INACTIVE
231 2950 2006-MAY-04 08:47:47 00:01:08 INACTIVE
322 1641 2006-MAY-04 08:48:32 00:00:30 INACTIVE
188 3844 2006-MAY-04 08:48:43 00:00:11 INACTIVE
255 5114 2006-MAY-04 08:49:09 00:00:02 INACTIVE
262 5444 2006-MAY-04 08:49:09 00:00:00 ACTIVE
199 4621 2006-MAY-04 08:49:13 00:00:02 INACTIVE
217 3765 2006-MAY-04 08:49:13 00:00:15 INACTIVE
58 14404 2006-MAY-04 08:49:25 00:09:28 INACTIVE
288 6403 2006-MAY-04 08:49:36 00:04:07 INACTIVE
157 4275 2006-MAY-04 08:49:40 00:00:17 INACTIVE
82 11687 2006-MAY-04 08:49:41 00:00:17 INACTIVE
267 3677 2006-MAY-04 08:49:49 00:09:04 INACTIVE
287 1171 2006-MAY-04 08:49:55 00:00:37 INACTIVE
100 11594 2006-MAY-04 08:49:58 00:04:32 INACTIVE
308 2507 2006-MAY-04 08:49:58 00:00:04 INACTIVE
13 17802 2006-MAY-04 08:50:00 00:00:04 INACTIVE
151 6614 2006-MAY-04 08:50:01 00:00:17 INACTIVE
161 5644 2006-MAY-04 08:50:07 00:00:02 INACTIVE
213 6159 2006-MAY-04 08:50:09 00:04:33 INACTIVE
229 5805 2006-MAY-04 08:50:10 00:00:06 INACTIVE
261 7169 2006-MAY-04 08:50:11 00:00:03 INACTIVE
166 14714 2006-MAY-04 08:50:14 00:00:06 INACTIVE
272 2450 2006-MAY-04 08:50:19 00:00:00 INACTIVE
103 6561 2006-MAY-04 08:50:21 00:00:02 INACTIVE
118 10514 2006-MAY-04 08:50:21 00:00:00 INACTIVE
187 5279 2006-MAY-04 08:50:24 00:00:47 INACTIVE
319 422 2006-MAY-04 08:50:25 00:00:00 INACTIVE
244 4278 2006-MAY-04 08:50:26 00:00:00 INACTIVE
75 10822 2006-MAY-04 08:50:43 00:00:02 INACTIVE
219 11324 2006-MAY-04 08:50:43 00:00:37 INACTIVE
87 10479 2006-MAY-04 08:50:43 00:00:23 INACTIVE
270 5024 2006-MAY-04 08:51:11 00:07:44 INACTIVE
235 6340 2006-MAY-04 08:51:21 00:00:17 INACTIVE
238 6151 2006-MAY-04 08:51:21 00:00:00 INACTIVE
246 4019 2006-MAY-04 08:51:23 00:00:02 INACTIVE
154 7956 2006-MAY-04 08:51:36 00:07:16 INACTIVE
16 15864 2006-MAY-04 08:51:40 00:00:02 INACTIVE
135 10261 2006-MAY-04 08:51:40 00:00:11 INACTIVE
302 3167 2006-MAY-04 08:51:45 00:00:00 INACTIVE
277 2372 2006-MAY-04 08:51:56 00:00:23 INACTIVE
19 16522 2006-MAY-04 08:52:10 00:00:12 INACTIVE
89 7578 2006-MAY-04 08:52:20 00:00:02 INACTIVE
198 7770 2006-MAY-04 08:52:21 00:00:14 INACTIVE
301 1450 2006-MAY-04 08:52:21 00:00:04 INACTIVE
149 6670 2006-MAY-04 08:52:26 00:00:33 INACTIVE
279 3389 2006-MAY-04 08:52:26 00:00:02 INACTIVE
241 7266 2006-MAY-04 08:52:45 00:00:02 INACTIVE
192 3567 2006-MAY-04 08:52:46 00:00:02 INACTIVE
195 6841 2006-MAY-04 08:52:46 00:04:10 INACTIVE
208 4883 2006-MAY-04 08:52:46 00:04:10 INACTIVE
245 4073 2006-MAY-04 08:52:56 00:00:37 INACTIVE
54 7733 2006-MAY-04 08:53:07 00:04:36 INACTIVE
221 6433 2006-MAY-04 08:53:07 00:00:02 INACTIVE
131 11404 2006-MAY-04 08:53:15 00:00:00 INACTIVE
178 10000 2006-MAY-04 08:53:15 00:00:23 INACTIVE
243 1531 2006-MAY-04 08:53:15 00:00:23 INACTIVE
271 5303 2006-MAY-04 08:53:15 00:00:02 INACTIVE
323 1216 2006-MAY-04 08:53:15 00:04:32 INACTIVE
317 2361 2006-MAY-04 08:53:15 00:00:00 ACTIVE
269 6955 2006-MAY-04 08:53:15 00:00:14 INACTIVE
146 9329 2006-MAY-04 08:53:15 00:00:02 INACTIVE
71 7035 2006-MAY-04 08:53:16 00:04:32 INACTIVE
165 12860 2006-MAY-04 08:53:16 00:00:23 INACTIVE
171 5970 2006-MAY-04 08:53:16 00:04:16 INACTIVE
321 1105 2006-MAY-04 08:53:16 00:04:45 INACTIVE
297 2398 2006-MAY-04 08:53:16 00:00:33 INACTIVE
9 20762 2006-MAY-04 08:53:17 00:05:36 INACTIVE
162 9663 2006-MAY-04 08:53:17 00:00:03 INACTIVE
293 4569 2006-MAY-04 08:53:17 00:00:23 INACTIVE
175 8959 2006-MAY-04 08:53:17 00:00:00 INACTIVE
274 2590 2006-MAY-04 08:53:20 00:00:17 INACTIVE
111 3649 2006-MAY-04 08:53:21 00:00:06 INACTIVE
222 4347 2006-MAY-04 08:53:21 00:00:06 INACTIVE
305 504 2006-MAY-04 08:53:27 00:00:02 INACTIVE
332 592 2006-MAY-04 08:53:34 00:02:49 INACTIVE
108 7957 2006-MAY-04 08:53:39 00:00:41 INACTIVE
140 7999 2006-MAY-04 08:53:47 00:00:00 ACTIVE
234 6873 2006-MAY-04 08:54:00 00:00:02 INACTIVE
325 1183 2006-MAY-04 08:54:05 00:00:11 INACTIVE
77 13656 2006-MAY-04 08:54:10 00:00:23 INACTIVE
298 3726 2006-MAY-04 08:54:10 00:00:17 INACTIVE
196 5261 2006-MAY-04 08:54:11 00:00:23 INACTIVE
256 3121 2006-MAY-04 08:54:11 00:00:23 INACTIVE
186 7785 2006-MAY-04 08:54:14 00:00:00 INACTIVE
237 4703 2006-MAY-04 08:54:14 00:00:03 INACTIVE
304 1241 2006-MAY-04 08:54:14 00:00:00 INACTIVE
278 10098 2006-MAY-04 08:54:15 00:00:15 INACTIVE
28 15585 2006-MAY-04 08:54:17 00:00:02 INACTIVE
337 133 2006-MAY-04 08:54:20 00:00:07 INACTIVE
346 12 2006-MAY-04 08:54:21 00:00:11 INACTIVE
345 105 2006-MAY-04 08:54:22 00:00:17 INACTIVE
78 11289 2006-MAY-04 08:54:31 00:00:02 INACTIVE
342 530 2006-MAY-04 08:54:31 00:00:00 INACTIVE
143 5485 2006-MAY-04 08:54:31 00:00:19 INACTIVE
328 1100 2006-MAY-04 08:54:33 00:00:54 INACTIVE
247 6273 2006-MAY-04 08:54:35 00:00:04 INACTIVE
353 390 2006-MAY-04 08:54:49 00:00:59 INACTIVE
68 11606 2006-MAY-04 08:54:51 00:00:00 INACTIVE
207 7843 2006-MAY-04 08:55:11 00:00:19 INACTIVE
299 1010 2006-MAY-04 08:55:11 00:00:04 INACTIVE
360 4 2006-MAY-04 08:55:11 00:00:04 INACTIVE
355 8 2006-MAY-04 08:55:11 00:00:02 INACTIVE
309 3054 2006-MAY-04 08:55:11 00:00:04 INACTIVE
286 4275 2006-MAY-04 08:55:11 00:00:04 INACTIVE
377 802 2006-MAY-04 08:55:19 00:00:37 INACTIVE
378 502 2006-MAY-04 08:55:19 00:00:02 INACTIVE
379 755 2006-MAY-04 08:55:21 00:00:30 INACTIVE
102 6061 2006-MAY-04 08:55:29 00:00:02 INACTIVE
98 9499 2006-MAY-04 08:55:35 00:00:06 INACTIVE
10 16893 2006-MAY-04 08:55:42 00:00:00 ACTIVE
311 2843 2006-MAY-04 08:55:43 00:00:02 INACTIVE
344 1793 2006-MAY-04 08:55:45 00:00:00 INACTIVE
63 13249 2006-MAY-04 08:55:46 00:00:02 INACTIVE
121 7048 2006-MAY-04 08:55:46 00:00:02 INACTIVE
338 465 2006-MAY-04 08:55:47 00:00:00 INACTIVE
382 1352 2006-MAY-04 08:55:47 00:00:12 INACTIVE
183 7033 2006-MAY-04 08:55:54 00:00:15 INACTIVE
273 2593 2006-MAY-04 08:56:00 00:00:00 INACTIVE
296 1959 2006-MAY-04 08:56:00 00:00:02 INACTIVE
86 9980 2006-MAY-04 08:56:12 00:00:11 INACTIVE
362 259 2006-MAY-04 08:56:18 00:00:15 INACTIVE
357 310 2006-MAY-04 08:56:19 00:00:02 INACTIVE
285 6405 2006-MAY-04 08:56:20 00:00:30 INACTIVE
370 545 2006-MAY-04 08:56:30 00:00:17 INACTIVE
84 7991 2006-MAY-04 08:56:34 00:00:03 INACTIVE
335 343 2006-MAY-04 08:56:35 00:00:00 INACTIVE
276 3455 2006-MAY-04 08:56:38 00:00:37 INACTIVE
65 9210 2006-MAY-04 08:56:39 00:00:02 INACTIVE
356 611 2006-MAY-04 08:56:44 00:00:11 INACTIVE
106 7595 2006-MAY-04 08:56:50 00:00:17 INACTIVE
257 2790 2006-MAY-04 08:56:55 00:00:02 INACTIVE
142 6496 2006-MAY-04 08:56:56 00:00:04 INACTIVE
236 4147 2006-MAY-04 08:56:59 00:00:23 INACTIVE
373 464 2006-MAY-04 08:56:59 00:00:37 INACTIVE
206 4226 2006-MAY-04 08:57:00 00:00:54 INACTIVE
334 845 2006-MAY-04 08:57:04 00:01:50 INACTIVE
354 704 2006-MAY-04 08:57:06 00:00:02 INACTIVE
101 6725 2006-MAY-04 08:57:10 00:00:00 INACTIVE
8 16176 2006-MAY-04 08:57:15 00:00:04 INACTIVE
31 18342 2006-MAY-04 08:57:16 00:00:05 INACTIVE
177 9192 2006-MAY-04 08:57:18 00:00:00 INACTIVE
283 2935 2006-MAY-04 08:57:19 00:00:04 INACTIVE
204 4528 2006-MAY-04 08:57:22 00:00:04 INACTIVE
239 5954 2006-MAY-04 08:57:23 00:00:00 INACTIVE
359 416 2006-MAY-04 08:57:23 00:00:30 INACTIVE
66 13784 2006-MAY-04 08:57:25 00:00:37 INACTIVE
292 3206 2006-MAY-04 08:57:25 00:00:02 INACTIVE
25 18143 2006-MAY-04 08:57:26 00:00:19 INACTIVE
35 19443 2006-MAY-04 08:57:26 00:00:00 INACTIVE
160 4648 2006-MAY-04 08:57:26 00:01:26 INACTIVE
275 6607 2006-MAY-04 08:57:27 00:00:03 INACTIVE
249 3861 2006-MAY-04 08:57:30 00:00:02 INACTIVE
369 386 2006-MAY-04 08:57:32 00:00:15 INACTIVE
260 5671 2006-MAY-04 08:57:33 00:01:04 INACTIVE
224 6798 2006-MAY-04 08:57:37 00:00:17 INACTIVE
313 1508 2006-MAY-04 08:57:38 00:00:00 INACTIVE
251 706 2006-MAY-04 08:57:39 00:01:10 INACTIVE
371 748 2006-MAY-04 08:57:39 00:01:10 INACTIVE
52 13672 2006-MAY-04 08:57:40 00:01:09 INACTIVE
107 9710 2006-MAY-04 08:57:46 00:01:08 INACTIVE
158 8741 2006-MAY-04 08:57:48 00:00:02 INACTIVE
62 12389 2006-MAY-04 08:57:51 00:00:02 INACTIVE
155 7601 2006-MAY-04 08:57:51 00:01:04 INACTIVE
375 1071 2006-MAY-04 08:57:51 00:00:00 INACTIVE
300 3622 2006-MAY-04 08:57:52 00:00:02 INACTIVE
331 481 2006-MAY-04 08:57:54 00:00:06 INACTIVE
181 8039 2006-MAY-04 08:57:55 00:00:59 INACTIVE
189 5988 2006-MAY-04 08:58:01 00:00:00 INACTIVE
343 1992 2006-MAY-04 08:58:06 00:00:49 INACTIVE
339 975 2006-MAY-04 08:58:09 00:00:02 INACTIVE
145 5969 2006-MAY-04 08:58:18 00:00:37 INACTIVE
367 40 2006-MAY-04 08:58:18 00:00:02 INACTIVE
225 2169 2006-MAY-04 08:58:19 00:00:02 INACTIVE
333 1345 2006-MAY-04 08:58:19 00:00:05 INACTIVE
242 2696 2006-MAY-04 08:58:19 00:00:34 INACTIVE
253 7188 2006-MAY-04 08:58:19 00:00:11 INACTIVE
205 4896 2006-MAY-04 08:58:20 00:00:06 INACTIVE
138 12153 2006-MAY-04 08:58:22 00:00:06 INACTIVE
168 9324 2006-MAY-04 08:58:24 00:00:30 INACTIVE
326 86 2006-MAY-04 08:58:24 00:00:17 INACTIVE
212 2846 2006-MAY-04 08:58:25 00:00:15 INACTIVE
215 2027 2006-MAY-04 08:58:25 00:00:02 INACTIVE
341 284 2006-MAY-04 08:58:32 00:00:14 INACTIVE
348 585 2006-MAY-04 08:58:32 00:00:15 INACTIVE
281 6066 2006-MAY-04 08:58:33 00:00:11 INACTIVE
368 1134 2006-MAY-04 08:58:33 00:00:02 INACTIVE
361 1157 2006-MAY-04 08:58:33 00:00:19 INACTIVE
291 4363 2006-MAY-04 08:58:33 00:00:02 INACTIVE
18 16867 2006-MAY-04 08:58:34 00:00:15 INACTIVE
185 8366 2006-MAY-04 08:58:34 00:00:00 INACTIVE
53 9194 2006-MAY-04 08:58:35 00:00:19 INACTIVE
64 11061 2006-MAY-04 08:58:35 00:00:19 INACTIVE
223 5569 2006-MAY-04 08:58:35 00:00:15 INACTIVE
109 11947 2006-MAY-04 08:58:36 00:00:17 INACTIVE
226 5329 2006-MAY-04 08:58:36 00:00:17 INACTIVE
51 12519 2006-MAY-04 08:58:38 00:00:15 INACTIVE
191 11473 2006-MAY-04 08:58:38 00:00:00 INACTIVE
307 4156 2006-MAY-04 08:58:38 00:00:00 INACTIVE
60 8278 2006-MAY-04 08:58:47 00:00:06 INACTIVE
92 10795 2006-MAY-04 08:58:47 00:00:02 INACTIVE
294 1909 2006-MAY-04 08:58:47 00:00:02 INACTIVE
114 7253 2006-MAY-04 08:58:48 00:00:05 ACTIVE
193 4873 2006-MAY-04 08:58:48 00:00:06 ACTIVE
156 8571 2006-MAY-04 08:58:49 00:00:03 INACTIVE
182 6836 2006-MAY-04 08:58:49 00:00:00 INACTIVE
259 3775 2006-MAY-04 08:58:49 00:00:00 INACTIVE
327 1963 2006-MAY-04 08:58:49 00:00:04 INACTIVE
289 1549 2006-MAY-04 08:58:49 00:00:02 INACTIVE
194 5692 2006-MAY-04 08:58:49 00:00:04 INACTIVE
24 12298 2006-MAY-04 08:58:50 00:00:02 INACTIVE
374 897 2006-MAY-04 08:58:50 00:00:02 INACTIVE
372 552 2006-MAY-04 08:58:50 00:00:02 INACTIVE
363 1262 2006-MAY-04 08:58:50 00:00:00 INACTIVE
329 1760 2006-MAY-04 08:58:50 00:00:02 INACTIVE
320 4395 2006-MAY-04 08:58:50 00:00:03 INACTIVE
314 5760 2006-MAY-04 08:58:50 00:00:03 INACTIVE
268 5698 2006-MAY-04 08:58:50 00:00:02 INACTIVE
233 6124 2006-MAY-04 08:58:50 00:00:02 INACTIVE
232 5103 2006-MAY-04 08:58:50 00:00:02 INACTIVE
211 7256 2006-MAY-04 08:58:50 00:00:03 INACTIVE
209 5461 2006-MAY-04 08:58:50 00:00:03 INACTIVE
179 9533 2006-MAY-04 08:58:50 00:00:02 INACTIVE
137 10420 2006-MAY-04 08:58:50 00:00:02 INACTIVE
74 10624 2006-MAY-04 08:58:51 00:00:02 INACTIVE
164 10844 2006-MAY-04 08:58:51 00:00:00 INACTIVE
90 10368 2006-MAY-04 08:58:51 00:00:02 INACTIVE
228 10641 2006-MAY-04 08:58:51 00:00:02 INACTIVE
364 68 2006-MAY-04 08:58:51 00:00:02 ACTIVE
349 93 2006-MAY-04 08:58:51 00:00:02 INACTIVE
330 326 2006-MAY-04 08:58:51 00:00:00 INACTIVE
310 3480 2006-MAY-04 08:58:51 00:00:02 INACTIVE
282 7214 2006-MAY-04 08:58:51 00:00:02 INACTIVE
216 5381 2006-MAY-04 08:58:51 00:00:00 INACTIVE
210 8362 2006-MAY-04 08:58:51 00:00:02 INACTIVE
202 7021 2006-MAY-04 08:58:51 00:00:02 INACTIVE
47 15624 2006-MAY-04 08:58:52 00:00:00 INACTIVE
94 11325 2006-MAY-04 08:58:52 00:00:02 INACTIVE
340 528 2006-MAY-04 08:58:52 00:00:02 ACTIVE
150 7500 2006-MAY-04 08:58:52 00:00:00 INACTIVE
153 10776 2006-MAY-04 08:58:53 00:00:00 INACTIVE
240 3856 2006-MAY-04 08:58:53 00:00:00 INACTIVE
350 2037 2006-MAY-04 08:58:53 00:00:00 INACTIVE
312 3916 2006-MAY-04 08:58:53 00:00:02 INACTIVE

353 rows selected.

Elapsed: 00:00:01.06
09:00:01 HT4:PHYDB001082758:08:27 - LIVE> SELECT TO_CHAR(SYSDATE, 'YYYY-MON-DD HH24:MI:SS') AS vsysdate
09:00:01 2 FROM dual
09:00:01 3 /

VSYSDATE
--------------------
2006-MAY-04 08:58:57

As you can see, I ran this at 8:58:57, and a bunch of new sessions were created just beforer that, but I have a bunch of INACTIVE sessions sitting there not being reused. SID's 40, 27, 43, and 23 have been inactive for over 5 hours.

Most of the inactive sessions have this SQL for their last sql call (by hash):

SELECT COUNT(*)
FROM dual
WHERE EXISTS(SELECT NULL
FROM sys.user$ u1, sys.sysauth$ sa
WHERE u1.user# = sa.privilege#
AND sa.grantee# = UID
AND u1.name = UPPER(:b1))

which occurs in a LOGON trigger to set the environment based on a given ROLE to that USER (namely, cursor_sharing and current_schema are set).

It's a web application, Oracle 8.1.7.4 Standard.

Should I worry about this?

Regards,
Mark

Hi Tom,
We have 100 plus database users currently in our system. But all are not active,I want to get a list of users who are not connecting last 3 months. Please give me the solution if it is possible.
Another one When I create new user I want to set some PASSWORD validations of my own other than Oracle default checks. I don’t want to write any procedure I just want to set it as in the profile, please give us the solution with example


Thanks
Ram

Hi tom
i am working as oracle dba and i have aproble can u pls help me.
i have two database one is "prod" and second one is "stby" standby database in same machine. i did all primary work both primary and standby database , when give alter system archive log current or commit statment in primary database which created one archive log , which has automaticaly transfer to standby database . but my problem is that archive log is not update standby database ,moreover when i see the standby database which is their
pls telme how can i slove it
moreover even if i did "recover managed standby database timeout 60" is not updating which shows old one. ie current archive log is not updating.

Ramesh dba

There are evil doers out there wasting my time (and others) posting responses to questions not intended for the thread.
Example: Thread titled "How to track logon & logout" was created, then during the course of the thread's history, someone asked you about archive logs on a standby database. I'm sure you have heard this complaint before, what can you do, right? (except not answer the question or tell them it is not relevant).

I on the other hand, have a followup question regarding the original thread intent and sadly cannot ask it.

Using statspack we have been tracking the number of logons in our system (logons cumulative).
We average approx. 4 logons per second, which looks bad but I'm having trouble to quantify exactly how bad it is.
Is there any statistic available in Oracle (or Solaris) that could indicate how big of an impact it has on CPU or IO or memory utilization?

Hi Tom,

Is it possible to track changes to the passwords Oracle 9i and later. I don't really care to know the passwords themselves (hashed or otherwise), but the fact the user changed the password and when. So the username or id, the date and time the password was changed and if possible the old and the new hashed/unhashed passwords.

Thanks
Ma$e

Hi Tom,

I reviewed the link but I don't seem to find the views or tables where the information I am after is mentioned.

Thanks

Regarding securing the database

let me explain the environment

Oracle version 10.2.0.3 EE
OS Solaris 5.10

We have 4 DBA in the OS dba group.
to login to the database in the server we doas follows
$>su - oracle
and then execute the sqlplus

This is because we do not have access to the sqlplus in our OS account.

Then we log in as sysdba connect as "/ as sysdba" or <oracle_username> as sysdba.
Now because we are authenticated we are able to log in.

Now if I look at the .aud files that is generated in the operating system

I can see the dbuser is "/" and client user is "oracle"

Now if I log in as myself (<oracledba_username> as sysdba) the .aud files have the same infomation
that is
dbuser is "/"
client user is "oracle"


I have locked SYS and SYSTEM account as you suggested.
and
O7_DICTIONARY_ACCESSIBILITY=FALSE



There is no way I can figure out which dba from the 4 of us logged in.

How can this be fixed. How an I figure out which client user or which db_user logged in as sysdba

Can I do the following without breaching security

1)Each of the user from the dba group should be able to execute the sqlplus from their own OS user account.
if this is the right route to go. What is the best way to do this without making a hole in the security.
Is runnng the changePerm.sh the right thing to do.
or is there another way to do this.
Fromwhat I read changePerm.sh is not right thing to do because all users will get access to sqlplus.

or

2)What other options do I have to be able to get the correct OS user information in the .aud file in the OS

I did read Arup Nandas Project lockdown ( http://www.oracle.com/technology/pub/articles/project_lockdown/index.html )
in which he suggested to change permissions on the oracle executable.
and always user listener to connect to the database.
I am not sure if I can do this at this point of time.

3)I would like to lock down the ORACLE OS user but then the dbas from the os dba group should be able to
execute the sqlplus from their own account. What is the safe way to do this.

I donot want to do an user identified externally yet .
we want the user to login as themself as sysdba with a password.

Please letme know if I have missed doing anything in auditing or setting up oracle.

Thanks for all you wonderful explanations.

...
Wed May 21 15:01:28 2008
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: tkyte
CLIENT TERMINAL: pts/3
STATUS: 0

......

Tom,
If I only need known the last time a user logged in, what is best:

1 - Enable auditing and write some code that each some time select the max logon date from each user from audit trail and update this in some last_logon_table, then purge the audit trail.

or

2 - Put a logon trigger that update the last logon time in the last_logon_table.

In two case if there are some "problem" the user cant login, and in the two case I need to write some code.

Thank you very much for your excelent help for all of us.

begin
  merge into my_audit_trail
  using (select ........ 
             <from v$ tables, sys_context values from dual, whatever>)
  on (join condition)
  when matched then update
  when not matched then insert
end;

Hi Tom,
thanks for you responce.

This logon trigger will be very used, and can produce some locks and problems to users.

What do you think about

begin
DBMS_JOB.SUBMIT(..... ,
merge into my_audit_trail
using (select ........
<from v$ tables, sys_context values from dual, whatever>)
on (join condition)
when matched then update
when not matched then insert
, ......);
end;

Thanks again.

The locking would be caused by a hang session of new user making the insert in my_audit_trail, or other not new makind the update.
I don't see any particular problem, I think about if table my_audit_trail lock then nobody can logon into the database, and I ask you if this hyphotetical situation can occurs. For your responce this is not likely.

Another question about this, the "last logon time" will be included in some version of Oracle database ? like a field in the dba_users or in some other way.

Hi Tom

I known that "the insert would happen and commit - it would not hang." but if hang nobody can logon to database.

I don't have no idea about how can happend, was the doubt and wanted to know your opinion.

Thanks

Nothing, thanks for all.

Sir,

Need your help as Oracle Detective.
After impdp action do, in range of 0 - 48 hours, drop table action happened.
When checking :
select DISTINCT ACTION_NAME from DBA_AUDIT_TRAIL or
select * from DBA_AUDIT_TRAIL where ACTION_NAME = 'DROP TABLE',
there is no DROP TABLE action. I don't know how could it be.

Very need your advice solving this mysterious event;

Thx.

Hi Tom,

Thanks for what you're doing here on forum - it helps me a lot.

I noticed that since version 11g there is no connect time presented in dba_audit... views after a session ends. I mean the (logoff_time - timestamp) = 0.

How can I see the connect time of a particular disconnected session ?

OK - sorry, I can see separate records with logon action...

tom,

is there a difference, audit create session Or audit connect?

Tom,

We want to NOT audit a specific user, is it possibly to do this;

audit create session by access;
noaudit create session by proxy_conn_user;

Or do you have to explicitly issue an AUDIT command for everyone except proxy_conn_user?

Is there are a way to populate v$session client_info column to AUD$ ?

Tom:

You said
<<You cannot know what application users are connecting from. The information is v$session is
suspect at best (just copy sqlplus.exe to blah.exe and see what happens -- you cannot rely on that
information in any way, shape or form.>>

OK, that is true but the session row has other client information

http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2088.htm

OSUSER VARCHAR2(30) Operating system client user name
PROCESS VARCHAR2(12) Operating system client process ID
MACHINE VARCHAR2(64) Operating system machine name
TERMINAL VARCHAR2(30) Operating system terminal name
PROGRAM VARCHAR2(48) Operating system program name

Is this reliable info or it can be changed by user too?

Of course, i am talking about client/server environemnt. for web oracle will capture the web server info.

<QUOTE>
the noaudit by user would only take away an audit by a user.

You would have to audit those you wish to audit.
</QUOTE>

Thanks Tom - for us, that means issuing an audit command 1000's of times as we have 1000's of users. Was hoping there was a command we can execute a few times for the few exceptions we have.

Hi Tom,

There is a requirement where we have to track the logins of reporting users(Users who connects only to Secondary db i.e READ_ONLY). We had tried collecting data from v$active_session_history, but data in that table is not stable and may disappear within few hours. Can you please provide a stable solution for this?

Thanks,
Sumanth