Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: HELP!! creating users with the DBMS_SQL package

Re: HELP!! creating users with the DBMS_SQL package

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/04/11
Message-ID: <33529931.10756767@newshost>#1/1

Roles are never active in a stored object (procedure/view/packge/function).

The create user privelege is gotten via the DBA role.

You can create a user in sql*plus and even from an anonymous pl/sql block but not in a procedure.

This is because you need to have "grant create user to YOU' directly.

To see if some command will work in a stored procedure (to see if you have the needed priveleges) you should in sql*plus:

SQL> set role none
SQL> try that command....

If the command fails, you don't have sufficient priveleges.

On Fri, 11 Apr 1997 19:35:58 -0700, Mark <mark_at_midascs.demon.co.uk> 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
>--------------------------------------------------
>
>Email : SmithM_at_logica.com
>
>Home Email : mark_at_midascs.demon.co.uk
>--------------------------------------------------

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities



Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Apr 11 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US