Re: Can't create an object through a Stored Procedure ?
From: Maris Elsins <elmaris_at_gmail.com>
Date: Fri, 8 Aug 2014 12:10:36 +0300
Message-ID: <CABQhObu0dvaRq73cJ3VT9egmZwpHVRNWLU1GMF8hn7Xewz0JgQ_at_mail.gmail.com>
Hi,
Date: Fri, 8 Aug 2014 12:10:36 +0300
Message-ID: <CABQhObu0dvaRq73cJ3VT9egmZwpHVRNWLU1GMF8hn7Xewz0JgQ_at_mail.gmail.com>
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 _at_MarisElsins <https://twitter.com/MarisElsins> www.facebook.com/maris.elsins On Fri, Aug 8, 2014 at 12:06 PM, Chitale, Hemant K <Hemant-K.Chitale_at_sc.com> wrote:Received on Fri Aug 08 2014 - 11:10:36 CEST
> 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