Re: Oracle 8 and Changing Passwords

Create a stored procedure owned by a schema that has alter any user system privledge

Procedure SP_CREATETRAVELBASEUSER

   (

    a_user_name VARCHAR2,
    a_user_password VARCHAR2,
    a_admin VARCHAR2 := 'N'

l_user VARCHAR2(255);

BEGIN l_user := 'create user ' || a_user_name || ' identified by ' ||   a_user_password || ' temporary tablespace temp';

dbms_utility.exec_ddl_statement(l_user);

sp_AlterTravelBaseUser
  (

    a_user_name,
    a_user_password,
    a_admin

END; Procedure COMMON.sp_ALTERTRAVELBASEUSER

   ( a_user_name VARCHAR2,
     a_user_password VARCHAR2,
     a_admin VARCHAR2 := 'N')

l_user     VARCHAR2(255);
l_user_grants VARCHAR2(255);
l_user_default_role VARCHAR2(255);

BEGIN l_user := 'alter user ' || a_user_name || ' identified by ' || a_user_password;

• If they need roles granted l_user_grants := 'grant <role1>,<role2> to ' || a_user_name;

l_user_default_role := 'alter user ' || a_user_name ||   ' default role <role>';

dbms_utility.exec_ddl_statement(l_user);
dbms_utility.exec_ddl_statement(l_user_grants);
dbms_utility.exec_ddl_statement(l_user_default_role);

END; Received on Mon Sep 14 1998 - 01:40:34 CEST