在存储过程中调用execute immediate 执行 create table语句报TBR-17004: Permission denied

错误描述:

在存储过程中调用execute immediate 执行 create table语句报错TBR-17004 权限不正确,但是直接执行该语句不报错.

SQL> conn sys/tibero
Connected to Tibero.

SQL> create user dhr identified by dhr;

User 'DHR' created.

SQL> grant connect,resource to dhr;

Granted.

conn dhr/dhr

 

create table test as select * from dual;

create or replace procedure p_create_tab
as
L_sql VARCHAR2(32767) ;
BEGIN
FOR i IN 1 .. 100
LOOP
l_sql :='CREATE TABLE TEST_DHR_' ||I || ' AS SELECT * FROM TEST';
-- dbms_output.put line(l_sql);
EXECUTE IMMEDIATE l_sql ;
end loop;
end ;
/

SQL> exec  p_create_tab;
TBR-17004: Permission denied.   
TBR-15163: Unhandled exception at DHR.P_CREATE_TAB,  line 9.   
TBR-15163: Unhandled exception at  line 1. 



原因:

根据问题可以发现用户确实有create table的权限，查询dba_role_privs 和 dba_sys_privs 发现用户有 resource权限，但并无显示的 create table权限。在存储过程中调用execute immediate执行sql语句，则用户必须有显示的赋权，而用户的角色权限在这里不起作用。所以会报TBR-17004错误。

SQL> select * from dba_role_privs where grantee='DHR';
GRANTEE    GRANTED_ROLE           ADMIN_OPTION DEFAULT_ROLE
------------ ------------------------------ ------------ -----------
DHR        CONNECT             NO           YES
DHR        RESOURCE            NO           YES

2 rows selected.

SQL> select * from dba_sys_privs WHERE GRANTEE='RESOURCE';

GRANTEE       PRIVILEGE             ADMIN_OPTION
-------------------- ---------------------------------------- ------------
RESOURCE      CREATE TABLE            NO
RESOURCE      CREATE SEQUENCE           NO
RESOURCE      CREATE PROCEDURE           NO
RESOURCE      CREATE TRIGGER           NO

4 rows selected.

解决方法:

1、可以显示的赋给用户 create table权限
grant create table to DHR;

2、或可以在存储过程上使用调用者权限。

create or replace procedure p_create_tab
AUTHID CURRENT_USER 
as
L_sql VARCHAR2(32767) ;
BEGIN
FOR i IN 1 .. 100
LOOP
l_sql :='CREATE TABLE TEST_DHR_' ||I || ' AS SELECT * FROM TEST';
-- dbms_output.put line(l_sql);
EXECUTE IMMEDIATE l_sql ;
end loop;
end ;
/

SQL> EXEC p_create_tab

PSM completed.