How to track user logon in Oracle 7.3.4

How to track user logon in Oracle 7.3.4

Post by Tom Cho » Sun, 23 Jun 2002 04:49:49



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

 
 
 

How to track user logon in Oracle 7.3.4

Post by Sybrand Bakke » Sun, 23 Jun 2002 05:07:28



Quote:> 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 do think Oracle 6 already has the feature
Just change/set audit_trail :
audit_trail = db
in init.ora and bounce the database.
Then
isssue
audit connect;
and look in dba_audit_session for results.

Disable the account would imply in Oracle 7.3.4 revoke create session
privilege.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address

 
 
 

How to track user logon in Oracle 7.3.4

Post by Martin Haltmaye » Fri, 05 Jul 2002 21:20:01


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

 
 
 

How to track user logon in Oracle 7.3.4

Post by Martin Haltmaye » Fri, 05 Jul 2002 21:44:13


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

 
 
 

How to track user logon in Oracle 7.3.4

Post by Howard J. Roger » Sat, 06 Jul 2002 08:06:21


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

 
 
 

How to track user logon in Oracle 7.3.4

Post by Kare » Sat, 06 Jul 2002 08:49:44


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

 
 
 

How to track user logon in Oracle 7.3.4

Post by Howard J. Roger » Sat, 06 Jul 2002 08:59:29


I'll buy it.

I don't like it, though. Time to move AUD$. Time to make sure it doesn't
grow and grow and grow.

Etc etc etc.

But yes, it'll work.

Regards
HJR


> 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

 
 
 

1. Logon activity tracking

I want to keep track of on-line activity. R83 let you put "U" in attribute #10
of the SYSTEM entry (for D-ptr or Q-pointed user) to update the transaction
file.

I am trying to do this with AP/Pro and can not seem to find out how to get the
ACCOUNTS file updated. I tried editing the SYSTEM entry for the MD or USERS
file for the user and I can get the activity to update.

I know this has to be easy so give me the easy answer.

Bruce Gover
Los Angeles, CA

2. copy dbgrid cells

3. Oracle ODBC Logon Screen in Access defualt to prior user ID

4. etc/brand no such file in SQL Linux install

5. Free Bug Tracking Tool For Oracle Users

6. Application Error of Dr Watson when reopening Access 97 linking SQL tables

7. OLAP Logon Failure NT AUTHORITY\ANONYMOUS LOGON - HELP!

8. Data View in VB6

9. logon failure when logon as admin with service rights

10. Logon Failed For User NULL

11. Need Help with user logon

12. logon failed for user '(null)'

13. ODBC SQL 6.5 User Logon Failed