Re: Can't create an object through a Stored Procedure ?

From: Ghassan Salem <salem.ghassan_at_gmail.com>
Date: Fri, 8 Aug 2014 11:13:08 +0200
Message-ID: <CALEzESj3+X3hghp-4FzBhqR2WxC0Pxp9m1UNiDyNuP4qe9XQyg_at_mail.gmail.com>



Hermant,
it's probably related to privileges that are granted to the user, directly no thru roles:

  1 create or replace procedure execute_ddl(ddl_in varchar2)   2 authid definer
  3 as
  4 begin
  5 execute immediate ddl_in;
  6* end;
11:10:48 SQL> / Procedure created.

Elapsed: 00:00:00.09
11:11:13 SQL> exec execute_ddl('create table t0 (c1 number)'); BEGIN execute_ddl('create table t0 (c1 number)'); END;

*
ERROR at line 1:

ORA-01031: insufficient privileges
ORA-06512: at "TESTS.EXECUTE_DDL", line 5
ORA-06512: at line 1


Elapsed: 00:00:00.00
11:11:18 SQL> grant create table to tests;

Grant succeeded.

Elapsed: 00:00:00.04
11:11:35 SQL> exec execute_ddl('create table t0 (c1 number)');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

On Fri, Aug 8, 2014 at 11:06 AM, Chitale, Hemant K <Hemant-K.Chitale_at_sc.com> wrote:

> This has me stumped / stymied. I can DROP objects using a Stored
> Procedure but I can’t 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 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
> .
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Aug 08 2014 - 11:13:08 CEST

Original text of this message