Check new user's profile in password_verify_function

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-l

Other related posts: