This content has been marked as final. Show 8 replies
Why don't you want to turn on auditing ? This is the way I usually identify inactive accounts.
# enable session audit
sqlplus '/ as sysdba'
alter system set audit_trail=db scope=spfile;
# 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
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
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
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.
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 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
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 || '
job => jobno,
what => 'begin LASTLOGON.EXPIRE; end;',
next_date => SYSDATE,
interval => 'SYSDATE+1');
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);
Is there anything built into 11g that performs this kind of function? Has the script been refined further?