Re: Check new user's profile in password_verify_function

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
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm 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.

Cheers

Stefan

Stefan P Knecht
CEO & Founder
s_at_10046.ch

On Fri, Feb 27, 2009 at 11:03 PM, Yong Huang <yong321_at_yahoo.com> 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 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