使用dbms_sql程序包的用户需要什么权限？

Question

把存储过程的代码贴上来，也许不是执行dbms_sql的权限问题

Answer 1

可是在sys用户下执行没问题啊
CREATE OR REPLACE PROCEDURE CreateSysTable(SysName varchar2)
IS
  v_Cursor NUMBER;
  n_Rec INTEGER ;
  v_CreateStr VARCHAR2(2000);
  v_NumRows  INTEGER;
BEGIN
  v_Cursor:=DBMS_SQL.OPEN_CURSOR;
  BEGIN
     select Count(1) 
      into n_Rec 
      from dual
     where exists(select 1 
                    from cat
                   where TABLE_NAME = upper('Menu'||SysName)) ;
    IF n_Rec = 0 THEN
     v_CreateStr:='CREATE TABLE Menu';
     v_CreateStr:=v_CreateStr||SysName;
     v_CreateStr:=v_CreateStr||'(
                      MenuName varchar2(20) NOT NULL ,
                      MenuTitle varchar2(50),
                      Visi integer default 0 NOT NULL,
                      Position varchar2(10),
                      MenuLevel integer,
                      NewOld varchar2(2),
                      DllName varchar2(100),
                      DllParams varchar2(100),
                      ModeName varchar2(20),
                      GroupName varchar2(512),
                      ButtonNum varchar2(2),
                      ButtonBmp varchar2(20),
                      ButtonCaption varchar2(10)
                )';
      DBMS_SQL.PARSE(v_Cursor,v_CreateStr,DBMS_SQL.V7);
      v_NumRows:=DBMS_SQL.EXECUTE(v_Cursor);
    END IF;
  END;
  BEGIN
     select Count(1) 
      into n_Rec 
      from dual
     where exists(select 1 
                    from cat
                   where TABLE_NAME = upper('OldMenu'||SysName)) ;
    IF n_Rec = 0 THEN
        v_CreateStr:='CREATE TABLE OldMenu';
        v_CreateStr:=v_CreateStr||SysName;
        v_CreateStr:=v_CreateStr||'(
                  MenuName varchar2(20) NOT NULL 
  )';
      DBMS_SQL.PARSE(v_Cursor,v_CreateStr,DBMS_SQL.V7);
     v_NumRows:=DBMS_SQL.EXECUTE(v_Cursor);
    END IF;
  END;
  BEGIN
     select Count(1) 
      into n_Rec 
      from dual
     where exists(select 1 
                    from cat
                   where TABLE_NAME = upper('Operator'||SysName)) ;
    IF n_Rec = 0 THEN
      v_CreateStr:='CREATE TABLE Operator';
      v_CreateStr:=v_CreateStr||SysName;
      v_CreateStr:=v_CreateStr||'(
                          userid varchar2(24) NOT NULL ,
                          username varchar2(12),
                          psw varchar2(32),
                          administrator integer NOT NULL ,
                          GroupName varchar2(200),
                          Area varchar2(20) NOT NULL,
                         PRIMARY KEY(userid,Area)
                   )';
      DBMS_SQL.PARSE(v_Cursor,v_CreateStr,DBMS_SQL.V7);
      v_NumRows:=DBMS_SQL.EXECUTE(v_Cursor);
    END IF;
  END;
  BEGIN
     select Count(1) 
      into n_Rec 
      from dual
     where exists(select 1 
                    from cat
                   where TABLE_NAME = upper('Group'||SysName)) ;
    IF n_Rec = 0 THEN
       v_CreateStr:='CREATE TABLE Group';
       v_CreateStr:=v_CreateStr||SysName;
       v_CreateStr:=v_CreateStr||'(
                GroupName varchar2(30) NOT NULL ,
                describe char(60),
                UserID varchar2(512) 
        )';
      DBMS_SQL.PARSE(v_Cursor,v_CreateStr,DBMS_SQL.V7);
      v_NumRows:=DBMS_SQL.EXECUTE(v_Cursor);
    END IF;
  END;
  BEGIN
     select Count(1) 
      into n_Rec 
      from dual
     where exists(select 1 
                    from cat
                   where TABLE_NAME = upper('ControlPro')) ;
    IF n_Rec = 0 THEN
      v_CreateStr:='CREATE TABLE ControlPro (
dwmc varchar2(32),
modename varchar2(32),
modetype varchar2(1),
buttonName varchar2(20),
ControlType varchar2(10),
ControlName varchar2(20),
fLeft varchar2(4),
fTop varchar2(4),
fHeight varchar2(4),
fWidth varchar2(4),
fColor varchar2(20),
FontName varchar2(16),
FontSize varchar2(4),
FontColor varchar2(20),
FontType varchar2(2),
fText varchar2(50),
fItems varchar2(100),
fCaption varchar2(100),
fShape varchar2(1)
)';
      DBMS_SQL.PARSE(v_Cursor,v_CreateStr,DBMS_SQL.V7);
      v_NumRows:=DBMS_SQL.EXECUTE(v_Cursor);
    END IF;
  END;
END;

Answer 2

执行动态sql所需权限必须是被直接授予的，不能通过角色授予
执行
grant create table to 用户名
或
grant create any table to 用户名
再执行你的存储过程

Answer 3

dbms_sql包试属于sys用户的，但他已被置于public，即任何用户都可以使用该包而且不用在包前代sys.

Answer 4

black_dragon(半仙)  is right.

Answer 5

1。使用DBMS_SQL需要EXECUTE权限，通常这是由SYS用户拥有，创建该包的
安装脚本通常会将该包的EXECUTE权限赋予PUBLIC,所以要保证确实有此权限
2。由于内置过程的内部所有角色都是失效的（DISABLED),但是调用DBMS_SQL
的用户需要权限执行该动态命令(你的例子中的串），同时也需要DBMS_SQL上的EXECUTE权限，所以需要显示的授予执行动态命令的权限，如上面半仙所示
grant create table to UserA(假设为你执行动态sql的用户);