Check new user's profile in password_verify_function

From: Yong Huang <>
Date: Fri, 27 Feb 2009 14:03:09 -0800 (PST)
Message-ID: <>

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

Received on Fri Feb 27 2009 - 16:03:09 CST

Original text of this message