Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id 846E71960393 for ; Fri, 8 Aug 2014 11:12:29 +0200 (CEST) Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTP for ; Fri, 8 Aug 2014 11:12:29 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2CFB92BF45; Fri, 8 Aug 2014 05:12:27 -0400 (EDT) Authentication-Results: turing.freelists.org; dkim=fail (verification failed; insecure key) header.i=@gmail.com; dkim-adsp=none (insecure policy) X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id x7OmVN9v-MMJ; Fri, 8 Aug 2014 05:12:27 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3418F2B2B7; Fri, 8 Aug 2014 05:11:46 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 08 Aug 2014 05:11:05 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0BB62283F5 for ; Fri, 8 Aug 2014 05:11:05 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id G5Upr-EGssRk for ; Fri, 8 Aug 2014 05:11:04 -0400 (EDT) Received: from mail-lb0-f169.google.com (mail-lb0-f169.google.com [209.85.217.169]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 73FD228381 for ; Fri, 8 Aug 2014 05:10:37 -0400 (EDT) Received: by mail-lb0-f169.google.com with SMTP id s7so3633566lbd.0 for ; Fri, 08 Aug 2014 02:10:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=2msPagM3V42jH/5ThrZROH3/87+7i/C9lNy7pZUq3Ok=; b=Muwe8sdfTLKtoDpq74CZlUWiRoohBpz1G7CW9esvsqBR+TL2vz8kL43RJj9XpXdfml kcLXiRaJzfxwjTUY5P5WiAxJYJyf8FE17kUK7WclmXi+rp6zHXb3VTtTBJ4sQsqtXYQm SIqo2eb46rgoJMLqgWk7zmsAoCkxIa83/yKgNDxp01D9l7xYvkhZv0D/SK4NziB/zFS4 U6WThhxJEAVjz5jU5oSTvmYrPkS77wIMlgpQ0mtSNQF4rNnCqJldMnl0qNhpfBbC+36o +pxYU0DTCU8lkMbalaJMg8KOet4Su5uBkmq06M6h9zcjevOiYnhCY6DgVi3yQHEwmrRk kgMw== MIME-Version: 1.0 X-Received: by 10.152.184.234 with SMTP id ex10mr21175730lac.53.1407489036699; Fri, 08 Aug 2014 02:10:36 -0700 (PDT) Received: by 10.112.120.167 with HTTP; Fri, 8 Aug 2014 02:10:36 -0700 (PDT) In-Reply-To: <0BDF2A25A09ADD40908745EEFC0A0FB60220116C@HKJUMXMB103B.zone1.scb.net> References: <0BDF2A25A09ADD40908745EEFC0A0FB60220116C@HKJUMXMB103B.zone1.scb.net> Date: Fri, 8 Aug 2014 12:10:36 +0300 Message-ID: Subject: Re: Can't create an object through a Stored Procedure ? From: Maris Elsins To: Hemant-K.Chitale@sc.com Cc: ORACLE-L Content-Type: multipart/alternative; boundary=001a11345d660efb3305001a944b X-archive-position: 55858 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: elmaris@gmail.com Precedence: normal Reply-To: elmaris@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --001a11345d660efb3305001a944b Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi, The necessary CREATE privileges have to be granted directly to the user (not via role). Is it done? P.S. I think the same applies to DROP privileges too, but you probably have them for DROP, and not for CREATE --- Maris Elsins @MarisElsins www.facebook.com/maris.elsins On Fri, Aug 8, 2014 at 12:06 PM, Chitale, Hemant K wrote: > This has me stumped / stymied. I can DROP objects using a Stored > Procedure but I can=E2=80=99t CREATE them ?! > > SQL>select object_name, object_type from user_objects > > 2 where object_name like 'HKC%' > > 3 order by object_type, object_name > > 4 / > > OBJECT_NAME OBJECT_TYPE > > ------------------------------ ------------------- > > HKC_SOURCE_1_NDX_1 INDEX > > HKC_SEQ_1 SEQUENCE > > HKC_GTT_1 TABLE > > HKC_GTT_2 TABLE > > HKC_SOURCE_1 TABLE > > HKC_SOURCE_2 TABLE > > HKC_TARGET_1 TABLE > > HKC_TEST_100 TABLE > > HKC_TEST_X TABLE > > HKC_VW VIEW > > 10 rows selected. > > SQL>create or replace procedure execute_ddl(ddl_in varchar2) > > 2 authid definer > > 3 as > > 4 begin > > 5 execute immediate ddl_in; > > 6 end; > > 7 / > > Procedure created. > > SQL>execute execute_ddl('create table hkc_test_y (id_column number)'); > > BEGIN execute_ddl('create table hkc_test_y (id_column number)'); END; > > * > > ERROR at line 1: > > ORA-01031: insufficient privileges > > ORA-06512: at "GLELD1_O.EXECUTE_DDL", line 5 > > ORA-06512: at line 1 > > SQL>execute execute_ddl('drop table hkc_test_x'); > > PL/SQL procedure successfully completed. > > SQL>execute execute_ddl('create sequence hkc_seq_2 start with 1'); > > BEGIN execute_ddl('create sequence hkc_seq_2 start with 1'); END; > > * > > ERROR at line 1: > > ORA-01031: insufficient privileges > > ORA-06512: at "GLELD1_O.EXECUTE_DDL", line 5 > > ORA-06512: at line 1 > > SQL>execute execute_ddl('drop sequence hkc_seq_1'); > > PL/SQL procedure successfully completed. > > SQL>execute execute_ddl('create view hkc_vw_2 as select * from dual'); > > BEGIN execute_ddl('create view hkc_vw_2 as select * from dual'); END; > > * > > ERROR at line 1: > > ORA-01031: insufficient privileges > > ORA-06512: at "GLELD1_O.EXECUTE_DDL", line 5 > > ORA-06512: at line 1 > > SQL>execute execute_ddl('drop view hkc_vw'); > > PL/SQL procedure successfully completed. > > SQL>select object_name, object_type from user_objects > > 2 where object_name like 'HKC%' > > 3 order by object_type, object_name > > 4 / > > OBJECT_NAME OBJECT_TYPE > > ------------------------------ ------------------- > > HKC_SOURCE_1_NDX_1 INDEX > > HKC_GTT_1 TABLE > > HKC_GTT_2 TABLE > > HKC_SOURCE_1 TABLE > > HKC_SOURCE_2 TABLE > > HKC_TARGET_1 TABLE > > HKC_TEST_100 TABLE > > 7 rows selected. > > SQL> > > What am I missing here ? > > 11.2.0.3 > > Hemant K Chitale > > > This email and any attachments are confidential and may also be > privileged. If you are not the intended recipient, please delete all copi= es > and notify the sender immediately. You may wish to refer to the > incorporation details of Standard Chartered PLC, Standard Chartered Bank > and their subsidiaries at https://www.sc.com/en/incorporation-details.htm= l > . > --001a11345d660efb3305001a944b Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi,

The necessary CREATE privileges= have to be granted directly to the user (not via role). Is it done?
P.S. I think the same applies to DROP privileges too, but you probably h= ave them for DROP, and not for CREATE



On Fri, Aug 8, 2014 at 12:06 PM, Chitale= , Hemant K <Hemant-K.Chitale@sc.com> wrote:

This has me stum= ped / stymied.=C2=A0 I can DROP objects using a Stored Procedure but I can<= /font>=E2=80=99t CREATE them ?!

SQL>select object_name, obje= ct_type from user_objects

=C2=A0 2=C2= =A0 where object_name like 'HKC%'

=C2=A0 3=C2= =A0 order by object_type, object_name

=C2=A0 4=C2= =A0 /

OBJECT_NAME= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 OBJECT_TYPE

------------= ------------------ -------------------

HKC_SOURCE_1= _NDX_1=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 INDEX

HKC_SEQ_1=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 SEQUENCE

HKC_GTT_1=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_GTT_2=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_SOURCE_1= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_SOURCE_2= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_TARGET_1= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_TEST_100= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_TEST_X= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_VW=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 VIEW=

10 rows sele= cted.

SQL>creat= e or replace procedure execute_ddl(ddl_in varchar2)

=C2=A0 2=C2= =A0 authid definer

=C2=A0 3=C2= =A0 as

=C2=A0 4=C2= =A0 begin

=C2=A0 5=C2= =A0 execute immediate ddl_in;

=C2=A0 6=C2= =A0 end;

=C2=A0 7=C2= =A0 /

Procedure cr= eated.

SQL>execu= te execute_ddl('create table hkc_test_y (id_column number)');

BEGIN execut= e_ddl('create table hkc_test_y (id_column number)'); END;

*

ERROR at lin= e 1:

ORA-01031: i= nsufficient privileges

ORA-06512: a= t "GLELD1_O.EXECUTE_DDL", line 5

ORA-06512: a= t line 1


SQL>execu= te execute_ddl('drop table hkc_test_x');

PL/SQL proce= dure successfully completed.

SQL>execu= te execute_ddl('create sequence hkc_seq_2 start with 1');

BEGIN execut= e_ddl('create sequence hkc_seq_2 start with 1'); END;=

*

ERROR at lin= e 1:

ORA-01031: i= nsufficient privileges

ORA-06512: a= t "GLELD1_O.EXECUTE_DDL", line 5

ORA-06512: a= t line 1


SQL>execu= te execute_ddl('drop sequence hkc_seq_1');

PL/SQL proce= dure successfully completed.

SQL>execu= te execute_ddl('create view hkc_vw_2 as select * from dual');

BEGIN execut= e_ddl('create view hkc_vw_2 as select * from dual'); END;

*

ERROR at lin= e 1:

ORA-01031: i= nsufficient privileges

ORA-06512: a= t "GLELD1_O.EXECUTE_DDL", line 5

ORA-06512: a= t line 1


SQL>execu= te execute_ddl('drop view hkc_vw');

PL/SQL proce= dure successfully completed.

SQL>selec= t object_name, object_type from user_objects

=C2=A0 2=C2= =A0=C2=A0 where object_name like 'HKC%'

=C2=A0 3=C2= =A0=C2=A0 order by object_type, object_name

=C2=A0 4=C2= =A0=C2=A0 /

OBJECT_NAME= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 OBJECT_TYPE

------------= ------------------ -------------------

HKC_SOURCE_1= _NDX_1=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 INDEX

HKC_GTT_1=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_GTT_2=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_SOURCE_1= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_SOURCE_2= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_TARGET_1= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_TEST_100= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

7 rows selec= ted.

SQL>

What am I missin= g here ?

11.2.0.3<= /span>

Hemant K Chitale


This email and any attachments are confidential and may also be privileged.= If you are not the intended recipient, please delete all copies and notify= the sender immediately. You may wish to refer to the incorporation details= of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries = at https://www.sc.com/en/incorporation-details.html.

--001a11345d660efb3305001a944b-- -- http://www.freelists.org/webpage/oracle-l