HELP!! creating users with the DBMS

From: Mark <mark_at_midascs.demon.co.uk>
Date: 1997/04/11
Message-ID: <334EF50E.5DC0@midascs.demon.co.uk>#1/1

Hi there.....

If anyone can help me with the following problem, I'd be very grateful....

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

Problem relates to the supplied package DBMS_SQL and stored procedures.

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.

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 used to 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

c2 integer;
result integer;

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.

HELP!! Cheers

Mark

Email : SmithM_at_logica.com

Home Email : mark_at_midascs.demon.co.uk

Received on Fri Apr 11 1997 - 00:00:00 CDT

HELP!! creating users with the DBMS_SQL package