首页 » ORACLE » procedure调用DDL需显示授权

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>

打赏

,

目前这篇文章有1条评论(Rss)评论关闭。

  1. Winnie Sosaya | #1
    2011-12-21 at 09:21

    Good job right here. I actually enjoyed what you had to say. Keep going because you unquestionably bring a new voice to this topic. Not many people would say what youve said and still make it interesting. Properly, at least Im interested. Cant wait to see a lot more of this from you.