Re: Error while executing stored procedure as system user

In fact, the restriction on use of privileges granted via a role applies to *all* stored objects (e.g., views), not just stored PL/SQL code.

Paul Baumgartel
paul.baumgartel_at_aya.yale.edu
917 549-4717

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, May 30, 2001 8:55 AM

> When you use privileges in stored procedures, you need to have the
privilege
> explicitly. In other words, you cannot get the privilege from a role.
>
> If you do a grant create user to MyRole.
> Grant myRole to myUser.
>
> Then try to create user as myUser it will work, but if you try to create
> user from a procedure as myUser it will fail.
>
>
> "Walking on water and developing software from a specification are easy if
> both are frozen."
>
> Christopher R. Spence
> Oracle DBA
> Fuelspot
>
>
>
> -----Original Message-----
> Sent: Tuesday, May 29, 2001 6:50 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Dear DBA Gurus,
>
> I am able to execute the below code as an anonymous Pl/Sql block but when
I
> incorporate it in a stored procedure the procedure gets created but I am
> getting the below errors while executing the procedure as system user:
>
> Anonymous Pl/Sql block
>
> Declare
> name varchar2(4):='test';
> BEGIN
> EXECUTE IMMEDIATE 'create user ' ||name||' '||'identified by '||name||'
> '||
> 'default tablespace users temporary tablespace temp';
> EXECUTE IMMEDIATE 'grant connect, resource to ' ||name;
> EXECUTE IMMEDIATE 'create table '||name||'.aaa(a number)';
> EXECUTE IMMEDIATE 'create table '||name||'.bbb(b number)';
> END;
>
> Stored Procedure
>
> create or replace procedure create_user (name IN VARCHAR2)
> IS
> BEGIN
> EXECUTE IMMEDIATE 'create user ' ||name||' '||'identified by '||name||'
> '||
> 'default tablespace users temporary tablespace temp';
> EXECUTE IMMEDIATE 'grant connect, resource to ' ||name;
> EXECUTE IMMEDIATE 'create table '||name||'.aaa(a number)';
> EXECUTE IMMEDIATE 'create table '||name||'.bbb(b number)';
> END;
> /
>
> Procedure Created.
>
> Errors while executing the procedure
>
> exec create_user('test');
>
> ERROR at line 1:
> ORA-01031: insufficient privileges
> ORA-06512: at "SYSTEM.CREATE_USER", line 4
> ORA-06512: at line 1
>
> What might be the reason for the errors? Can anyone help me?
>
> TIA and Regards,
>
> Ranganath
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Ranganath K
> INET: ranganathk_at_subexgroup.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Christopher Spence
> INET: cspence_at_FuelSpot.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Paul Baumgartel
  INET: paulb_at_instipro.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).