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

*During a server call, when a DR unit is pushed onto the call stack, the database stores the currently enabled roles and the current values of CURRENT_USER and CURRENT_SCHEMA. It then changes both CURRENT_USER and CURRENT_SCHEMA to the owner of the DR unit, and enables only the role PUBLIC.*
If you're using definer's rights (DR), it doesn't matter what you are granted through roles other than PUBLIC because they will not be enabled when the DR unit is pushed onto the call stack (during execution). You have two choices:

1. Grant CREATE TABLE to PUBLIC (Don't do this)
2. Change the AUTHID to CURRENT_USER

Seth Miller

On Fri, Aug 8, 2014 at 4:16 AM, vijay sehgal <vijaysehgal21_at_gmail.com> wrote:

> Hemant,
>
> I am able to use your code and create the table below are details.
>
> Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
> Production On Solaris Box.
>
> SQL> desc t3;
> ERROR:
> ORA-04043: object t3 does not exist
>
>
> SQL> exec execute_ddl('create table t3(col1 number)');
>
> PL/SQL procedure successfully completed.
>
> SQL> desc t3;
> Name Null? Type
> ----------------------------------------- -------- -------------
> COL1 NUMBER
>
> SQL> exec execute_ddl('drop table t3 purge');
>
> PL/SQL procedure successfully completed.
>
> SQL> desc t3;
> ERROR:
> ORA-04043: object t3 does not exist
>
>
> Regards,
> Vijay Sehgal.
>
>
> On Fri, Aug 8, 2014 at 2:36 PM, 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