Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ORACLE 7.3 - Is There a way to detect a user connection when it happens

Re: ORACLE 7.3 - Is There a way to detect a user connection when it happens

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Fri, 12 Mar 1999 20:54:50 -0800
Message-ID: <36E9EF9A.7D65@oriolecorp.com>


grelaud_at_my-dejanews.com wrote:
>
> Hello everybody,
>
> For security reason, I have to detect when a user is connecting to a database
> and then to check the last time he change its password.
>
> After some search in the Oracle documentation I was thinking that the best
> way was to add a trigger on each insert into the "v$session" table... But
> this table is recovered by a view named also "v$session".
>
> I have tried as the "SYS" user to drop this view without success !
>
> Does anybody out there can help me !
>
> Thank you in advance for your help.
>
> Cordially, Jean-Michel GRELAUD
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Concerning your auditing, it depends on which Oracle version you are working with; with Oracle8 you can define some password expiration time in the profile associated with each user. With former Oracle versions you can do what I once did : 1) I created a private user table containing username, (encrypted) password as found in SYS.USER$, a date column for the password change and another one for the last connection. 2) I used the AUDIT on connections, to record each new connection.

Now, the trick is just to run a procedure every night which compares the contents of MY user table to both SYS.USER$ and the audit trail to have all the information you need, updating the private user table as the need arises. Moreover, when no connection was recorded for a rather long period, the password was forced to something else (using INDENTIFIED BY VALUES '*** DISABLED ***'). Quite efficient, especially when the Personnel Department doesn't keep you informed of resignations.

--
Regards,

  Stéphane Faroult
  Oriole Corporation Received on Fri Mar 12 1999 - 22:54:50 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US