Forum Stats

  • 3,837,003 Users
  • 2,262,219 Discussions
  • 7,900,178 Comments

Discussions

find last time users logged in

Robeen
Robeen Member Posts: 2,209 Silver Badge
edited Nov 11, 2017 5:39PM in General Database Discussions

Oracle Database 11.2.0.4

Hi,

how do I find last time users logged in and those who did not login for past 180 days

USERNAME                                      PROFILE                        ACCOUNT_STATUS

--------------------------------------------- ------------------------------ ------------------------------

SYS                                           DEFAULT                        OPEN

SYSTEM                                        DEFAULT                        OPEN

OUTLN                                         DEFAULT                        EXPIRED & LOCKED

DIP                                           DEFAULT                        EXPIRED & LOCKED

MUZAMMER                                      DEFAULT                        OPEN

DBSNMP                                        DEFAULT                        OPEN

APPQOSSYS                                     DEFAULT                        EXPIRED & LOCKED

WMSYS                                         DEFAULT                        EXPIRED & LOCKED

XS$NULL                                       DEFAULT                        EXPIRED & LOCKED

EXFSYS                                        DEFAULT                        EXPIRED & LOCKED

XDB                                           DEFAULT                        EXPIRED & LOCKED

USERNAME                                      PROFILE                        ACCOUNT_STATUS

--------------------------------------------- ------------------------------ ------------------------------

ANONYMOUS                                     DEFAULT                        LOCKED

ORDSYS                                        DEFAULT                        EXPIRED & LOCKED

ORDDATA                                       DEFAULT                        EXPIRED & LOCKED

ORDPLUGINS                                    DEFAULT                        EXPIRED & LOCKED

SI_INFORMTN_SCHEMA                            DEFAULT                        EXPIRED & LOCKED

MDSYS                                         NICE_PROFILE                   OPEN

MDDATA                                        DEFAULT                        EXPIRED & LOCKED

SPATIAL_WFS_ADMIN_USR                         DEFAULT                        EXPIRED & LOCKED

PP2                                           DEFAULT                        OPEN

SPATIAL_CSW_ADMIN_USR                         DEFAULT                        EXPIRED & LOCKED

DBADM                                         NICE_PROFILE                   OPEN

Regards,

Roshan

PKRAndrewSayerDeepCRobeenjgarryBeefStu
«1345

Answers

  • Robeen
    Robeen Member Posts: 2,209 Silver Badge
    edited Jul 25, 2017 4:37AM

    last time users queried, I got the following query:

    select username,machine,terminal,logon_time from v$session;

    User_KNQFY
  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,383 Bronze Crown
    edited Jul 25, 2017 4:59AM

    you can use audit if you have, query dba_audit_Trail to see logon and off times.

    Robeen
  • PKR
    PKR Member Posts: 423 Silver Badge
    edited Jul 25, 2017 5:55AM

    Hi

    See MOS DOC

    How to Audit User Connection, Disconnection Date and Time (Doc ID 99786.1)

    Rgrds

    PKR

  • PKR
    PKR Member Posts: 423 Silver Badge
    edited Jul 25, 2017 6:17AM

    Hi

    DB version 12c only

    select USERNAME,LAST_LOGIN from dba_users

    Rgrds

    PKR

    Robeen
  • PKR
    PKR Member Posts: 423 Silver Badge
    edited Jul 25, 2017 7:04AM

    Hi,

    https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1830073957439

    also MOS DOC

    How to Audit Logon/Logoff Events with Triggers (Doc ID 70679.1)

    Rgrds,

    PKR

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Jul 25, 2017 7:42AM
    Robeen wrote:last time users queried, I got the following query:select username,machine,terminal,logon_time from v$session;

    v$session shows only current sessions.  If I logged of 1 ms before you query v$session, you wont see the 'last time I queried".

    Unless you are on 12c ( See PKR, msg #4) you will really have to role your own, by keeping a 'last logon' table, and populate it with a trigger.  Auditing is useful but not really a complete solution.  First, it can be turned off and back on, but more importantly, it will only give you the last logon of a user if that last logon was within the retention period of your audit trail.

    I took the approach of a custom 'last_logon' table and a  trigger.  My table looks like this:

    CREATE TABLE "LOCALDBA"."LAST_USER_LOGON"

      (    "USERNAME" VARCHAR2(30 BYTE),

           "OS_USERNAME" VARCHAR2(30 BYTE),

           "LAST_LOGON_DATE" DATE,

           "SOURCE_IND" CHAR(1 BYTE),

        CONSTRAINT "LAST_LOGON_PK" PRIMARY KEY ("USERNAME")

    the logon trigger updates the row for the user that is logging on.  For an initial load of the table, I joined dba_users with dba_audit_trail, using the date of the most recent audited logon (if available) to populate LAST_LOGON_DATE.  If no audit record existed for a given user, I populated LAST_LOGON_DATE with sysdate.  SOURCE_IND is an 'A' (Audit) if the last logon was from the audit trail on the initial load, a 'D' (Default) if from sysdate on the initial load, and when updated by the trigger, SOURCE_IND is set to 'T' (Trigger).

    BeefStu
  • DeepC
    DeepC Member Posts: 158 Blue Ribbon
    edited Jul 25, 2017 8:48AM

    Login time populated only when you connect using SQL*PLUS. Using -nologintime switched off login time and no login time recording for SYSDBA,SYSDG and SYSBACKUP

  • Unknown
    edited Jul 25, 2017 9:13AM

    "Login time populated only when you connect using SQL*PLUS"

    So this is a half-hearted, poorly conceived 'enhancement' to the data dictionary information?

    PKR
  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Jul 25, 2017 9:17AM
    Deep Chakraborty wrote:Login time populated only when you connect using SQL*PLUS. Using -nologintime switched off login time and no login time recording for SYSDBA,SYSDG and SYSBACKUP 

    Huh?

    What is that from, and what is it referencing?

    PKR
  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Jul 25, 2017 9:21AM
    EdStevens wrote:Deep Chakraborty wrote:Login time populated only when you connect using SQL*PLUS. Using -nologintime switched off login time and no login time recording for SYSDBA,SYSDG and SYSBACKUP Huh?What is that from, and what is it referencing?

    To confirm:

    If it means that you can disable LAST_LOGIN from being populated when you use the -nologintime switch, it's wrong. It still gets recorded.

    If it means that when you login with something like sql developer: it won't get populated, it's wrong. It still gets recorded.

    Can't confirm the SYS___ roles.

    User_XV173
This discussion has been closed.