Oracle logins and passwords

When did a user logon in Oracle last time? When did a user change his/her password?

Compared to many operating system, Oracle seems not to offer the possibility of listing the last login, and when the password was changed last time. The well know view DBA_USERS does not offer these fields


SQL> descr dba_users
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 PROFILE                                   NOT NULL VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
 EXTERNAL_NAME                                      VARCHAR2(4000)

The underlying table sys.user$ contains the field ptime, which keeps the time when the password was changed the last time over. Do not confound it with ctime, which is the "creation time", nor with ltime, which is the time the account has been locked (if any).


SQL> descr sys.user$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER#                                     NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(30)
 TYPE#                                     NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 DATATS#                                   NOT NULL NUMBER
 TEMPTS#                                   NOT NULL NUMBER
 CTIME                                     NOT NULL DATE
 PTIME                                              DATE
 EXPTIME                                            DATE
 LTIME                                              DATE
 RESOURCE$                                 NOT NULL NUMBER
 AUDIT$                                             VARCHAR2(38)
 DEFROLE                                   NOT NULL NUMBER
 DEFGRP#                                            NUMBER
 DEFGRP_SEQ#                                        NUMBER
 ASTATUS                                   NOT NULL NUMBER
 LCOUNT                                    NOT NULL NUMBER
 DEFSCHCLASS                                        VARCHAR2(30)
 EXT_USERNAME                                       VARCHAR2(4000)
 SPARE1                                             NUMBER
 SPARE2                                             NUMBER
 SPARE3                                             NUMBER
 SPARE4                                             VARCHAR2(1000)
 SPARE5                                             VARCHAR2(1000)
 SPARE6                                             DATE

SELECT name, ptime AS "Last time changed" FROM user$ WHERE ptime IS NOT NULL;

NAME                           Last time changed
------------------------------ --------------------
SYSTEM                         08:00:09 27-mai-2003
RJAMES                         22:32:10 28-feb-2004
OPS$ORACLE                     13:56:54 06-sep-2003
X23                            14:40:26 16-jan-2004

How to monitor and list the Oracle logons

Still there is no information about the last login. Possible solutions are:

Write a login trigger that stores login information in some table
Use the dba_audit_sessions (sys.aud$), which requires the audit being enabled

Logon triggers are always a risk; if something goes wrong (and things can go wrong) logins could get blocked; for example, if the logon table is unavailable, or a record is locked etc. etc. On the other hand, if the audit is enables you already have the records in sys.aud$.
If the table sys.aud$ is truncated regularly, then you can store the records somewhere else.


rem FRS 4-JUN-2004
rem
create table t_simple_user as select name, spare6 as last_login FROM user$;
rem
INSERT INTO t_simple_user 
(SELECT name, NULL FROM user$ 
WHERE name NOT IN (SELECT NVL(name,'  ') FROM t_simple_user));
rem
UPDATE t_simple_user u SET last_login = (
    SELECT max(timestamp)
    FROM dba_audit_session a
    WHERE a.username = u.name 
    AND a.timestamp = (select distinct max(b.timestamp) 
	FROM dba_audit_session b where a.username = b.username))
WHERE name IN (SELECT username FROM dba_audit_session);
commit;

The last WHERE clause is not redundant, because it prevents updates with NULL values coming from the inner query!!!



NAME                           LAST_LOGIN
------------------------------ --------------------
SYSTEM                         22:00:05 05-jun-2004
RJAMES                         02:00:20 05-jun-2004
OPS$ORACLE                     00:11:54 06-jun-2004
X23                            02:47:37 06-jun-2004

[Home] [Web Design] [HTML tutorials] [Javascript] [PSP] [About us] [Links] [Anonymous email] [Best hosting] [Daily Oracle Life] [IT jobs in Switzerland] [Web Submission] [Web traffic]

Rate this article ...
Very poor	Poor	Average	Good	Very good