Re: Check new user's profile in password_verify_function

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

Cheers

Stefan

Stefan P Knecht
CEO & Founder
s_at_10046.ch

10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland

Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info_at_10046.ch
http://www.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
>
>
>

--
http://www.freelists.org/webpage/oracle-l