錯誤描述:

在存儲過程中調用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.

?

總結

以上是生活随笔為你收集整理的在存储过程中调用execute immediate 执行 create table语句报TBR-17004: Permission denied的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。