Users not logged over 3 month
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Users not logged over 3 month

  1. #1
    Join Date
    Oct 2009
    Posts
    30

    Users not logged over 3 month

    Hi,

    Could someone please help me with a query to find out the users who have not logged into the database for 3 months or above??

    Thanks in advance.

    Regards,
    Aakriti

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    I believe there is no straight forward view is available to check the last log in date unless you enable auditing for that user.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    Oct 2009
    Posts
    30
    Hi,

    Thanks for the response.

    Will audit enabling help in getting the list of users logged into the database 3 months ago??

    Thanks in advance

    Regards

  4. #4
    Join Date
    Oct 2009
    Posts
    30
    Well, I think... enabling the auditing on the database will take care of the user's login time from current date.

    But, I would like to know if it is possible to determine the last login date and time by a user into an oracle instance at present,or say, users who logged in 3months ago.

    Could you please help

    Thanks in advance.

    Regards,
    Aakriti

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you have already been given your answer

  6. #6
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    if you don't want to enable auditing, create on log on trigger and store the date/time in a table and you can query from that table any time.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  7. #7
    Join Date
    Oct 2009
    Posts
    30
    Hi Vnktummala,

    Could you please guide me in creating the log on trigger and store the date/time in a table.

    Thanks in Advance.

  8. #8
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    some thing like this ... test before you drop in production.

    CREATE OR REPLACE TRIGGER SYS.ON_LOGON_USR
    AFTER LOGON ON DATABASE
    WHEN ( USER = '' )
    BEGIN
    execute immediate 'insert into table_name values (SYSDATE)';
    EXCEPTION
    WHEN OTHERS THEN
    NULL;
    END;
    Thanks,
    Last edited by vnktummala; 08-17-2010 at 01:35 AM. Reason: codes
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  9. #9
    Join Date
    Feb 2009
    Posts
    91
    Just stumbled upon this, it maybe of help.


    http://www.dba-oracle.com/art_builder_sec_audit.htm


    connect sys/manager;

    create table
    stats$user_log
    (
    user_id varchar2(30),
    session_id number(8),
    host varchar2(30),
    last_program varchar2(48),
    last_action varchar2(32),
    last_module varchar2(32),
    logon_day date,
    logon_time varchar2(10),
    logoff_day date,
    logoff_time varchar2(10),
    elapsed_minutes number(8)
    )
    ;


    Designing a logon trigger

    Once the table is designed, the next step is to create a system-level logon trigger that fills in as much information as possible at the time of the logon event. Listing B illustrates the logon audit trigger that I created.

    create or replace trigger
    logon_audit_trigger
    AFTER LOGON ON DATABASE
    BEGIN
    insert into stats$user_log values(
    user,
    sys_context('USERENV','SESSIONID'),
    sys_context('USERENV','HOST'),
    null,
    null,
    null,
    sysdate,
    to_char(sysdate, 'hh24:mi:ss'),
    null,
    null,
    null
    );
    END;
    /

    As you can see, I populated this table with values that are available at logon time:


    create or replace trigger
    logoff_audit_trigger
    BEFORE LOGOFF ON DATABASE
    BEGIN
    -- ***************************************************
    -- Update the last action accessed
    -- ***************************************************
    update
    stats$user_log
    set
    last_action = (select action from v$session where
    sys_context('USERENV','SESSIONID') = audsid)
    where
    sys_context('USERENV','SESSIONID') = session_id;
    --***************************************************
    -- Update the last program accessed
    -- ***************************************************
    update
    stats$user_log
    set
    last_program = (select program from v$session where
    sys_context('USERENV','SESSIONID') = audsid)
    where
    sys_context('USERENV','SESSIONID') = session_id;
    -- ***************************************************
    -- Update the last module accessed
    -- ***************************************************
    update
    stats$user_log
    set
    last_module = (select module from v$session where
    sys_context('USERENV','SESSIONID') = audsid)
    where
    sys_context('USERENV','SESSIONID') = session_id;
    -- ***************************************************
    -- Update the logoff day
    -- ***************************************************
    update
    stats$user_log
    set
    logoff_day = sysdate
    where
    sys_context('USERENV','SESSIONID') = session_id;
    -- ***************************************************
    -- Update the logoff time
    -- ***************************************************
    update
    stats$user_log
    set
    logoff_time = to_char(sysdate, 'hh24:mi:ss')
    where
    sys_context('USERENV','SESSIONID') = session_id;
    -- ***************************************************
    -- Compute the elapsed minutes
    -- ***************************************************
    update
    stats$user_log
    set
    elapsed_minutes =
    round((logoff_day - logon_day)*1440)
    where
    sys_context('USERENV','SESSIONID') = session_id;
    END;
    /

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width