Re: HELP!! creating users with the DBMS_SQL package

Mark wrote:

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

You neet to explicitly grant the user the privileges to create users in order to use the dbms_sql package. The user cannot use the privileges inherited from a role.

Regards,

David Received on Mon Apr 14 1997 - 00:00:00 CDT