This discussion is archived
8 Replies Latest reply: Apr 25, 2013 11:47 PM by 1005672 RSS

Lock database accounts if not accessed for last 30 days

user632098 Newbie
Currently Being Moderated
I am using Oracle 10.2.0.4 on HP UNIX. I have to lock database accounts, which have
not been accessed in last 30 days. I cannot find anyway to get information from data dictionary when was the last time a user connected to that account unless I turn database
auditing on. I do not want to turn auditing on just to get this information.

Oracle’s profile mechanism will expire a password after PASSWORD_LIFETIME but there is no provision in PROFILE to lock account if it was not accessed for x days.

Is there any other way to get the account. Logon time?
  • 1. Re: Lock database accounts if not accessed for last 30 days
    RobertGeier Oracle ACE
    Currently Being Moderated
    Why don't you want to turn on auditing ? This is the way I usually identify inactive accounts.

    E.g :-

    # enable session audit

    sqlplus '/ as sysdba'
    alter system set audit_trail=db scope=spfile;
    shutdown immediate;
    startup
    audit session;

    # after a few months of normal database operation find the users who have not logged in.

    select username, created from dba_users where account_status = 'OPEN' and username not in
    (select distinct userid from aud$ where TIMESTAMP# > sysdate -30)
    order by username;

    Edited by: Robert Geier on Sep 8, 2008 12:44 PM

    Edited by: Robert Geier on Sep 8, 2008 12:48 PM
  • 2. Re: Lock database accounts if not accessed for last 30 days
    521217 Journeyer
    Currently Being Moderated
    Logon trigger?
  • 3. Re: Lock database accounts if not accessed for last 30 days
    595286 Explorer
    Currently Being Moderated
    When you create the user, assign an expiration in 30 days with grace time = 0

    after logon, make an alter user to modify the expire time to sysdate+30
  • 4. Re: Lock database accounts if not accessed for last 30 days
    164043 Explorer
    Currently Being Moderated
    What is the practical difference between locked password and locked account. User will not be able to establish a session, either way. Why do you need
    account locked?
  • 5. Re: Lock database accounts if not accessed for last 30 days
    user632098 Newbie
    Currently Being Moderated
    Thanks a lot to all of you.


    LOcking account aand expring password two different requirements. USer's password must be changed every 90 days, that's weher expiration comes into. If user did not user account for 30 days, lock the account.
  • 6. Re: Lock database accounts if not accessed for last 30 days
    Simon Kissane Newbie
    Currently Being Moderated
    Hi everyone

    One way of doing this is using a table which records when an account was last logged in,
    a logon trigger to keep the table up to date, and a database job which locks accounts
    which have not been logged in to for too long. Below is an example script which does this.

    This script has a few limitations, and could be improved in some ways:
    1) Every time the job runs, it tries to lock accounts which are already locked.
    It needs a test which determines whether the user account is already locked,
    and to not try to lock it if it is already.
    2) It only counts the last login from the first time the user logs in.
    So if an account is created, but the user never logs in, it will never
    be locked. One solution is to join against DBA_USERS, and when the user
    is missing from the last logon records, use their CREATED date instead.
    3) It will only lock accounts when the job is run. Maybe should add a
    check to the login trigger, to check if the account trying to log in
    should be locked as well?

    So consider this script a base to get you started, rather than a complete solution.

    [One final note: I work for Oracle, but this is NOT supported
    by Oracle. If you choose to use it, you do so at YOUR OWN RISK.]

    Regards
    Simon Kissane
    ==================================
    CREATE USER LASTLOGON identified by LASTLOGON;
    GRANT RESOURCE, ALTER USER TO LASTLOGON;

    CREATE TABLE LASTLOGON.RECORDS (USERNAME VARCHAR2(30) NOT NULL, LAST_LOGON DATE,
    CONSTRAINT PK_LAST_LOGON PRIMARY KEY (USERNAME));

    create or replace
    trigger LASTLOGON.LASTLOGON_TRIGGER AFTER LOGON ON DATABASE
    declare
    v_count pls_integer;
    begin
    select count(*) into v_count from RECORDS WHERE USERNAME = USER;
    IF v_count > 0 THEN
    UPDATE RECORDS SET LAST_LOGON = SYSDATE WHERE USERNAME = USER;
    ELSE
    INSERT INTO RECORDS (USERNAME,LAST_LOGON) VALUES (USER,SYSDATE);
    END IF;
    end;
    /

    CREATE PROCEDURE LASTLOGON.EXPIRE IS
    CURSOR c1 IS
    SELECT USERNAME
    FROM RECORDS
    WHERE LAST_LOGON < SYSDATE - 30 AND USERNAME
    NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP');
    BEGIN
    FOR r_username IN c1 LOOP
    EXECUTE IMMEDIATE ('ALTER USER ' || r_username.username || '
    ACCOUNT LOCK');
    END LOOP;
    END;
    /

    DECLARE
    jobno NUMBER;
    BEGIN
    DBMS_JOB.SUBMIT(
    job => jobno,
    what => 'begin LASTLOGON.EXPIRE; end;',
    next_date => SYSDATE,
    interval => 'SYSDATE+1');
    COMMIT;
    END;
    /
  • 7. Re: Lock database accounts if not accessed for last 30 days
    858466 Newbie
    Currently Being Moderated
    Please see the below revised script that addresses items 1 and 2 in Simon Kissane's example and also provides some helpful checks.
    --------------------------------------------------------------------------------------------------------------------------------


    <tt>
    CREATE USER LASTLOGON IDENTIFIED BY LASTLOGON;
    GRANT RESOURCE, ALTER USER TO LASTLOGON;
    GRANT SELECT ON DBA_USERS TO LASTLOGON;
    <tt>
    CREATE TABLE LASTLOGON.RECORDS
        (
          USERNAME VARCHAR2(30) NOT NULL,
          LAST_LOGON DATE,
          CONSTRAINT PK_LAST_LOGON PRIMARY KEY (USERNAME)
        );
    /

    <tt>
    -- Note the double quotes around the username which allow for mixed case usernames
    CREATE OR REPLACE PROCEDURE LASTLOGON.EXPIRE IS
      CURSOR C1 IS
        SELECT DBA_USERS.USERNAME
        FROM LASTLOGON.RECORDS
        RIGHT OUTER JOIN DBA_USERS
        ON LASTLOGON.RECORDS.USERNAME       = DBA_USERS.USERNAME
        WHERE (COALESCE(LAST_LOGON, CREATED) < SYSDATE - 30)
        AND DBA_USERS.USERNAME NOT                             IN ('SYS','SYSTEM','SYSMAN','DBSNMP')
        AND DBA_USERS.USERNAME NOT                             IN
          (SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS LIKE '%LOCKED%');
    BEGIN FOR R_USERNAME IN C1 LOOP
        EXECUTE IMMEDIATE ('ALTER USER “' || R_USERNAME.USERNAME || '” ACCOUNT LOCK');
      END LOOP;
    END;
    /

    <tt>
    -- Creates job using DBMS_SCHEDULER
    BEGIN
      DBMS_SCHEDULER.create_job (
        job_name        => 'LASTLOGON_DAILY',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN LASTLOGON.EXPIRE; END;',
        start_date      =>  SYSTIMESTAMP,
        repeat_interval => 'freq=daily',
        end_date        => NULL,
        enabled         => TRUE,
        comments        => 'Daily execution of LASTLOGON.EXPIRE procedure, which locks accounts which have not been used in 30 days.');
    END;
    /

    <tt>
    -- Check the status of the job
    select log_id, job_name, status, to_char(log_date, 'DD-MON-YYYY HH24:MI') log_date from dba_scheduler_job_run_details where job_name = 'LASTLOGON_DAILY';

    <tt>
    -- To run job manually, use the following
    BEGIN
      DBMS_SCHEDULER.run_job (job_name            => 'LASTLOGON_DAILY',
                              use_current_session => FALSE);
      END;
    /
  • 8. Re: Lock database accounts if not accessed for last 30 days
    1005672 Newbie
    Currently Being Moderated
    Is there anything built into 11g that performs this kind of function? Has the script been refined further?

    Thank you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points