Hi Yong You could do this with a "before create on database" trigger. Cheers Stefan ========================= Stefan P Knecht CEO & Founder s@xxxxxxxx 10046 Consulting GmbH Schwarzackerstrasse 29 CH-8304 Wallisellen Switzerland Phone +41-(0)8400-10046 Cell +41 (0) 79 571 36 27 info@xxxxxxxx http://www.10046.ch ========================= On Fri, Feb 27, 2009 at 11:03 PM, Yong Huang <yong321@xxxxxxxxx> 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 > > > > -- > //www.freelists.org/webpage/oracle-l > > >