procedure调用DDL需显示授权

存储过程中执行DDL与DML有很大的区别，比如你在存储过程中创建表，虽然你授于了建表的角色给它，即便是DBA，在调用时也是会提示ORA-01031: insufficient privileges，显然是权限问题，记住如果在存储过程中调用DDL要显示授权，通过ROLE传授的权限是被忽略的。下面做一个实验证明

SQL> conn test/test
Connected.
SQL>  create or replace procedure p_createtab(name varchar2)
2     is
3     v_sql varchar2(1000);
4     begin
5      v_sql := ‘create table ‘||name||'(id int,name varchar2(20))’;
6         execute immediate v_sql;
7     — dbms_output.put_line(v_sql);
8*    end;

Procedure created.

SQL> select * from session_roles;

ROLE
————————————————————
CONNECT
RESOURCE

SQL> exec p_createtab(‘test11’);
BEGIN p_createtab(‘test11’); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at “TEST.P_CREATETAB”, line 6
ORA-06512: at line 1

SQL> conn system/oracle
Connected.
SQL> grant create table to test;

Grant succeeded.

SQL> conn test/test
Connected.
SQL> exec p_createtab(‘test11’);

PL/SQL procedure successfully completed.

SQL> desc test11;
Name                                      Null?    Type
—————————————– ——– —————————-
ID                                                 NUMBER(38)
NAME                                               VARCHAR2(20)

SQL>