reating users with the DMBS_SQL package (HELP!)

[Quoted] Hi there.....

I'm working on an Oracle application whch has the following requirement :

A requirement of the system is to design a forms 4.5 user screen that will allow certain priviliged managers to create new users and grant the necessary roles/privileges automatically.

Problem relates to the supplied package DBMS_SQL and stored procedures.

Oracle version 7.3.... On Aix
Development Forms 4.5.7 (ish)

To achieve this we have created a stored procedure utilising the dbms_sql package which does the 'create user fred identified by bloggs' SQL normally usedto create users, and calling this from within Forms 4.5

Unfortunately when testing this procedure from Sqlplus we get the following message...
NB The account this was run from had the DBA priviliege granted on it.

SQL> execute create_new_user ('FRED','BLOGGS'); begin create_new_user ('FRED','BLOGGS'); end;

*
ERROR at line 1:

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
ORA-06512: at "SYS.DBMS_SQL", line 25
ORA-06512: at "MARK.CREATE_NEW_USER", line 6
ORA-06512: at line 1

The code in the stored procedure is......
>>>>>>>

create or replace procedure create_new_user( uname in varchar2,

                                             passwd in varchar2) is

begin

  c2 := dbms_sql.open_cursor;
  dbms_sql.parse(c2,'create user '||uname||' identified by '||passwd, 0);
  result := dbms_sql.execute(c2);
  dbms_sql.close_cursor(c2);

end;
<<<<<<<<

If the stored procedure is run from the sys account then it works. Please can you advise us on a solution/better approach....

Cheers

Mark Smith

Email : SmithM_at_logica.com

Home Email : mark_at_midascs.demon.co.uk