动态DDL执行与Schema Objects显示授权问题

王朝other·作者佚名  2008-07-26
窄屏简体版  字體: |||超大  

这是一个大家常见的问题,也是很容易忽略的问题,尤其是在Procedure里面动态执行DDL语句的时候发生. 我们必须使用显示授权方式来保证DDL的执行权限, 而不能通过role的传递方式,下面是一个简单的测试过程来验证这一点.

参考说明:

If the referenced item is indeed declared and you believe that you have privileges to refer to that item, check the privileges; if the privileges were granted only via a role, then this is expected and documented behavior. Stored objects (packages, procedures, functions, triggers, views) run in the security domain of the object owner with no roles enabled except PUBLIC. Again, you will be notified only that the item was not declared

Test steps:

SQL> create user chris identified by pass;

User created.

SQL> grant dba to chris;

Grant succeeded.

SQL> connect chris/pass;

Connected.

SQL> create table test1 (name varchar2(10));

Table created.

--Success here.

SQL> drop table test1;

Table dropped.

SQL> create or replace procedure chris_cr_tbl

2 IS

3 begin

4 execute immediate 'create table test1 (name varchar2(10))';

5 end;

6 /

Procedure created.

SQL> exec chris_cr_tbl;

BEGIN chris_cr_tbl; END;

*

ERROR at line 1:

ORA-01031: insufficient privileges

ORA-06512: at "CHRIS.CHRIS_CR_TBL", line 4

ORA-06512: at line 1

--Failed Here

SQL> connect test/oracle

Connected.

SQL> grant create table to chris;

Grant succeeded.

--Explicit Grant

SQL> connect chris/pass

Connected.

SQL> exec chris_cr_tbl;

PL/SQL procedure successfully completed.

SQL> select table_name from all_tables where owner='CHRIS';

TABLE_NAME

------------------------------

TEST1

SQL> drop table test1;

Table dropped.

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航