Check new user's profile in password_verify_function
- From: Yong Huang <yong321@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 27 Feb 2009 14:03:09 -0800 (PST)
I'd like to create one password_verify_function for multiple profiles. In the
function, I'd like to check what profile this "alter user" or "create user"
action is acted on. For example,
CREATE OR REPLACE FUNCTION verify_function (username varchar2, password
varchar2, old_password varchar2)
...
select profile into prof from dba_users where username =
verify_function.username;
IF prof = 'APPLICATION' THEN
IF length(password) < 16 THEN
raise_application_error(-20002, 'Password length less than 16 for
APPLICATION user');
END IF;
ELSE
IF length(password) < 8 THEN
raise_application_error(-20002, 'Password length less than 8 for regular
user');
END IF;
END IF;
It works fine for "alter user". But for "create user", dba_users obviously
doesn't have him yet and 'No data found' is thrown. So I add code to check the
existence of the user first. The problem is, if it's a new user, there's no way
to capture the profile in the "create user ... profile ..." statement to apply
my specific rule to. I don't think there's a workaround other than creating two
verification functions, one for APPLICATION and one for the rest. Comments are
appreciated.
Yong Huang
--
//www.freelists.org/webpage/oracle-l
Other related posts:
