Pete Finnigan's Oracle Security Forum


    
      
        Pete Finnigan's Oracle Security Forum
        (http://www.petefinnigan.com/forum/yabb/YaBB.cgi)
      

        Oracle Security >> Oracle Security >> Could I use trigger to move 'EXPIRED' to 'LOCKED' 
        
(Message started by: Pete Finnigan on Feb 22^nd, 2008, 9:38pm)

Title: Could I use trigger to move 'EXPIRED' to 'LOCKED'
Post by Pete Finnigan on Feb 22^nd, 2008, 9:38pm

Hi,

I'm currently developing a password management policy for my organisation. I'm using Oracle 8i. I observed the following :

(1) The value in the 'account_status' field of table, dba_users,
is set to 'EXPIRED'(expired-grace time etc....) when a user attempts to logon, given that the password has expired.

(2) The value in the 'account_status' field of table, dba_users, is set to 'LOCKED' when user account is locked after x number of failed logn attempts etc..

These changes appears to be an Oracle- initiated actions, that is, they are executed automatically.
I would like to IMMEDIATELY change an 'EXPIRED' state to 'LOCKED' state whenever a user password expires.

Could this be done ??? I' thinking about a database trigger....that detects when the 'account_status' goes to 'EXPIRED' and changes it to 'LOCKED' immediately.
but I've read that these operate for insert, update, delete operations ONLY...could it be used for my purposes ??

I'm new to Oracle/triggers...I would appreciate any assistance please.....

Title: Re: Could I use trigger to move 'EXPIRED' to 'LOCK
Post by Pete Finnigan on Feb 24^th, 2008, 11:50pm

Wouldn't go with a trigger, but a daily DBMS_JOB that picks all the accounts from DBA_USERS and does an
EXECUTE IMMEDIATE 'ALTER USER '||v_blah||' ACCOUNT LOCK';