| Help - Password expired with grace period does not raise an error [message #36332] |
Fri, 16 November 2001 09:08  |
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  |
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 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
