Very Computer

> 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

> 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

> > 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

> 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

> > 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

> > > 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

> 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

> > 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

> > > 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

> > > > 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

> 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.

> > 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

> > > 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

> > > > 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

> > > > > 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