Re: Check new user's profile in password_verify_function

From: Yong Huang <>
Date: Mon, 2 Mar 2009 14:54:12 -0800 (PST)
Message-ID: <>

Hi Stefan,

Most likely we'll drop the requirement to create a single verify_function for two profiles. But to satisfy my curiosity, I looked at "System-Defined Event Attributes", Table 9-2 at There's no profile I can capture. I assume that's what I need to do if I create a "before create on database" trigger. There's ora_sql_txt function. Maybe I can parse that. Anyway, thank you for your idea.

Yong Huang

  • Stefan P Knecht wrote ---------

Hi Yong
You could do this with a "before create on database" trigger.



Stefan P Knecht
CEO & Founder

On Fri, Feb 27, 2009 at 11:03 PM, Yong Huang <> wrote:

> 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 length(password) < 16 THEN
> raise_application_error(-20002, 'Password length less than 16 for
> IF length(password) < 8 THEN
> raise_application_error(-20002, 'Password length less than 8 for
> regular user');
> 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 Mon Mar 02 2009 - 16:54:12 CST

Original text of this message