8 Replies Latest reply on Apr 26, 2013 6:47 AM by 1005672

    Lock database accounts if not accessed for last 30 days

    user632098
      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
          Robert Geier
          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
          • 3. Re: Lock database accounts if not accessed for last 30 days
            595286
            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
              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
                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-Oracle
                  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
                    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
                      Is there anything built into 11g that performs this kind of function? Has the script been refined further?

                      Thank you.