Check new user's profile in password_verify_function
Date: Fri, 27 Feb 2009 14:03:09 -0800 (PST)
Message-ID: <616942.5712.qm_at_web80601.mail.mud.yahoo.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Feb 27 2009 - 16:03:09 CST