Re: reating users with the DMBS_SQL package (HELP!)
Date: 1997/04/11
Message-ID: <5im7v2$i16_at_inet-nntp-gw-1.us.oracle.com>#1/1
You need to GRANT CREATE USER directly to the owner of this stored procedure. All roles are disabled inside stored procedures, including the system privileges granted via the DBA role. For more information, see the Application Developer's Guide or Chapter 10 of _Oracle PL/SQL Programming_ (info below, in my signature).
In article <334EF2A1.53B0_at_midascs.demon.co.uk>, Mark <mark_at_midascs.demon.co.uk> writes:
|> 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
|> 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.
|> 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
|> --------------------------------------------------
Scott Urman Oracle Corporation surman_at_us.oracle.com
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2 Published by Oracle Press - http://www.osborne.com/oracle/index.htm
"The opinions expressed here are my own, and are not necessarily that of Oracle Corporation"
Received on Fri Apr 11 1997 - 00:00:00 CEST