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?
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.
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.]
CREATE USER LASTLOGON identified by LASTLOGON;
GRANT RESOURCE, ALTER USER TO LASTLOGON;
create or replace
trigger LASTLOGON.LASTLOGON_TRIGGER AFTER LOGON ON DATABASE
select count(*) into v_count from RECORDS WHERE USERNAME = USER;
IF v_count > 0 THEN
UPDATE RECORDS SET LAST_LOGON = SYSDATE WHERE USERNAME = USER;
INSERT INTO RECORDS (USERNAME,LAST_LOGON) VALUES (USER,SYSDATE);
CREATE PROCEDURE LASTLOGON.EXPIRE IS
CURSOR c1 IS
WHERE LAST_LOGON < SYSDATE - 30 AND USERNAME
NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP');
FOR r_username IN c1 LOOP
EXECUTE IMMEDIATE ('ALTER USER ' || r_username.username || '
Please see the below revised script that addresses items 1 and 2 in Simon Kissane's example and also provides some helpful checks.
CREATE USER LASTLOGON IDENTIFIED BY LASTLOGON;
GRANT RESOURCE, ALTER USER TO LASTLOGON;
GRANT SELECT ON DBA_USERS TO LASTLOGON;
CREATE TABLE LASTLOGON.RECORDS
USERNAME VARCHAR2(30) NOT NULL,
CONSTRAINT PK_LAST_LOGON PRIMARY KEY (USERNAME)
-- Note the double quotes around the username which allow for mixed case usernames
CREATE OR REPLACE PROCEDURE LASTLOGON.EXPIRE IS
CURSOR C1 IS
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');
-- Creates job using DBMS_SCHEDULER
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.');
-- 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';
-- To run job manually, use the following
DBMS_SCHEDULER.run_job (job_name => 'LASTLOGON_DAILY',
use_current_session => FALSE);