Re: Can't create an object through a Stored Procedure ?
- From: Ghassan Salem <salem.ghassan@xxxxxxxxx>
- To: Hemant-K.Chitale@xxxxxx
- Date: Fri, 8 Aug 2014 11:13:08 +0200
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@xxxxxx>
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
> .
>
Other related posts:
