Home » RDBMS Server » Security » Help - Password expired with grace period does not raise an error
Help - Password expired with grace period does not raise an error [message #36332] Fri, 16 November 2001 09:08 Go to next message
Mahesh Rao
Messages: 2
Registered: November 2001
Junior Member
I am trying to catch the error from Visual basic when password expires. Oracle does not raise error before password expire and with in the grace period. Oracle 8.0.5 client throws a message "ORA-28002 Password will expire in x number of days". But this is just a message not an error. Oracle 8.1.6 client does not throw this message. How can you catch this error from client. Is there a way to raise an application_error. If so where do you write the code

----------------------------------------------------------------------
Re: Help - Password expired with grace period does not raise an error [message #37171 is a reply to message #36332] Tue, 22 January 2002 04:31 Go to previous messageGo to next message
Mahesh Rao
Messages: 2
Registered: November 2001
Junior Member
I waited for a reply and did not get any. Finally I found a work around and thought to share,if any body else is looking for. Please let me know if any body has a better solution.
Create a procedure in SYS schema and grant execute on this procedure to all users. this procedure will query dba_users view and return an error_number when the password is about to expire. execute this procedure soon after you connect to database (very next line after connect to database). and catch the error_code and message from your front end and handle it.

you may want to give another number for ORA-28003 in the procedure code. since this may mean some thing in real oracle error codes. You may choose to give a different codes all together like APP-28001,APP-28002 and APP-28003.

Code for the procedure follows.

/*
##########################################################

This procedure should be in SYS schema

This will query dba_users view and return error code
if the password is about to expire or expired

##########################################################
*/

create or replace procedure password_expire_notice(
user in varchar2,err_code out number,
err_Msg out varchar2) as

-- DECLARE VARIABLES
num_days number;

-- USER DEFINED EXCEPTIONS
user_expires exception;
user_expired exception;
user_expiring exception;

-- DECLARE CURSOR TO HOLD EXPIRY DATE AND SYSDATE
cursor rec_expdate is
select expiry_date,sysdate
from dba_users
where username = upper(user);
row_expdate rec_expdate%rowtype;

begin
open rec_expdate;
fetch rec_expdate into row_expdate;
num_days :=(row_expdate.expiry_date - row_expdate.sysdate);
close rec_expdate;

if num_days <=0 then
raise user_expired;
elsif num_days < 1 then
raise user_expiring;
elsif num_days <10 then
raise user_expires;
end if;

exception
when user_expires then
err_code := 28002;
err_Msg:='ORA-28002 Your password will expire in ' ||trunc(num_days)||' day(s).';

when user_expired then
err_code := 28001;
err_Msg:='ORA-28001 Your password is expired.';
when user_expiring then
err_code := 28003;
err_Msg:='ORA-28003 Your password is expired. You must change now.';
when others then
err_code := sqlcode;
err_Msg := substr(sqlerrm, 1, 80);

end;
/

/* ########### end of the procedure code ############
Re: Help - Password expired with grace period does not raise an error [message #38244 is a reply to message #36332] Thu, 04 April 2002 03:17 Go to previous messageGo to next message
ssg
Messages: 1
Registered: April 2002
Junior Member
this seems to be the problem for versions of oracle client above 8.0.5 and below 9.0.1 .
one way to get arount this would be to check the user_users table as soon as the application logs in to the oracle database. if the ACCOUNT_STATUS field is EXPIRE(GRACE) then compare the EXPIRE_DATE field with the sysdate and generate an error message " the password will expire within num days" when num>=0 and num=EXPIRE_DATE-SYSDATE
Re: Help - Password expired with grace period does not raise an error [message #39349 is a reply to message #36332] Tue, 09 July 2002 14:03 Go to previous message
Maran Parker
Messages: 1
Registered: July 2002
Junior Member
Should we not include the password grace time in the equation to calculate the number of days in which the password will expire ? The expiry date does not seem to include the grace time.
Previous Topic: Database Security
Next Topic: Checking Oracle Users Password
Goto Forum:
  


Current Time: Mon Oct 18 06:37:38 CDT 2021