Re: How to track user logon in Oracle 7.3.4

I liked Sybrand's answer. Enable audit on logons and have a plsql job that checks the timestamp in dba_audit_session. The code can be sophisticated enough and also check for sessions that are currently connected. An account can be disabled by resetting the password with ALTER USER .. IDENTIFIED BY VALUES. This is not as graceful as in Oracle8 but still can be done in Oracle7.

"Howard J. Rogers" wrote:

> Martin:
>
> idle_time is server process idle time. For your suggestion to mean anything,
> it implies that we are worried about someone who has been connected to the
> database, with the same session, yet doing sod-all for 60 days.
>
> I would have thought this highly unlikely ever to occur, and feel sure (don
> my cloak of clairvoyance) that the original poster wanted to be able to spot
> users who haven't logged on for 60 days -that is, their *account* has been
> "idle" (ie, unused) for that length of time.
>
> It's also the case that password_life_time was invented in Oracle 8.0 (as
> were all the password management parts of resource profiles). Therefore,
> even that but of advice won't help our original poster who is stuck, as he
> said, on 7.3.4.
>
> *My* short answer to the original poster is that without upgrading, you
> can't do what you want. The view common to all versions of Oracle would be
> dba_users, and that tells us if an account has been locked out, but it
> doesn't say anything about 'last logon'. Not even in 9i. 8i would permit you
> to do an 'after logon on database' trigger, which could be used to populate
> some sort of logging table. But that's 8i, not 7.
>
> Regards
> HJR
>
> "Martin Haltmayer" <Martin.Haltmayer_at_d2mail.de> wrote in message
> news:3D24431D.D5A5AC2F_at_d2mail.de...
> > I forgot to mention you must ensure that resource_limit = true for the
> idling
> > timeout to work.
> >
> > To make sure that the first password aging is hitting as well, you must
> issue an
> > "alter user ... identified by values '...'" where you get the passwords
> from
> > dba_users.
> >
> > Martin
> >
> >
> >
> > Martin Haltmayer wrote:
> > >
> > > Hi Tom,
> > >
> > > you can achieve this via modifying their profile. I assume they have the
> > > "DEFAULT" profile assigned. In sqlplus you do "alter profile DEFAULT
> limit
> > > password_life_time 60 idle_time 86400;". This makes sure that (a) they
> have to
> > > change their password after 60 days (they can change it to the same they
> already
> > > have but they have to do something) and (b) their session will be kicked
> out
> > > after 86400 minutes (= 60 days * 1440 minutes/day) of idling. If there
> is
> > > anybody connected to your database disconnect and reconnect them. The
> easiest
> > > way to enforce this is to bounce the instance.
> > >
> > > Martin
> > >
> > > Tom Chow wrote:
> > > >
> > > > Hi,
> > > >
> > > > I need to track which user is inactive last 60 days and disable the
> account
> > > > if user is inactive. What is the best way to do this and which
> table/view
> > > > needed to get this information. I know Oracle 8i has the feature but
> we are
> > > > stuck with 7.3.4
> > > >
> > > > Thanks
> > > > Tom
Received on Thu Jul 04 2002 - 18:49:44 CDT