或許你也遇到過同樣的問題:
用sql語句建表沒問題，但是放到儲存過程裡執行就報沒有許可權;
用sql查詢或dml操作也是沒問題，放到儲存過程就報錯許可權不足,或表不存在.

在官方文件上看到有這麼一段話 ,文件地址

How Roles Work in PL/SQL Blocks
The use of roles in a PL/SQL block depends on whether it is an anonymous block or a named block (stored procedure, function, or trigger), and whether it executes with definer's rights or invoker's rights.
Roles Used in Named Blocks with Definer's Rights
All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights. Roles are not used for privilege checking and you cannot set roles within a definer's rights procedure.
The SESSION_ROLES view shows all roles that are currently enabled. If a named PL/SQL block that executes with definer's rights queries SESSION_ROLES, then the query does not return any rows.
Roles Used in Named Blocks with Invoker's Rights and Anonymous PL/SQL Blocks
Named PL/SQL blocks that execute with invoker's rights and anonymous PL/SQL blocks are executed based on privileges granted through enabled roles. Current roles are used for privilege checking within an invoker's rights PL/SQL block. You can use dynamic SQL to set a role in the session.

其大意就是說使用者角色的許可權在命名的pl/sql塊裡是不可用的，包括儲存過程、觸發器、函式，但是在匿名的pl/sql塊裡是沒問題的。
什麼是角色的許可權呢?
可以透過dba_roles檢視所有角色:select * from dba_roles;
可以透過user_role_privs及role_sys_privs檢視使用者有那些角色，及角色具有的許可權。使用者在這兩個檢視中的許可權在儲存過程、觸發器、函式中是不起作用的。

下面演示DDL的實驗，用scott使用者建一個儲存過程來刪除表和建立表

SQL> set linesize 1000
SQL> show user;
USER is "SCOTT"
SQL> col text format a80
SQL> select line,text from user_source where type='PROCEDURE' and name='P_EMP1' ORDER BY LINE;
LINE TEXT
---------- --------------------------------------------------------------------------------
1 procedure p_emp1
2 as
3 begin
4 execute immediate 'drop table emp1';
5 execute immediate 'create table emp1 as select * from emp';
6 end;
6 rows selected.
SQL> drop table emp1;
Table dropped.
SQL> create table emp1 as select * from emp;
Table created.
SQL>
SQL> exec p_emp1;
BEGIN p_emp1; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.P_EMP1", line 5
ORA-06512: at line 1

發現用sql語句直接執行create table emp1 as select * from emp 是沒有問題的，但是在儲存過程中提示許可權不足。
來查一下scott使用者有哪些系統許可權

SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
SQL> select * from role_sys_privs order by role;
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE SESSION NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE TYPE NO
9 rows selected.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT UNLIMITED TABLESPACE NO

scott有CONNECT和RESOURCE兩個角色的許可權，還有一個UNLIMITED TABLESPACE的系統許可權。
接下來，給scott分配create table的許可權，在看看效果

SQL> conn /as sysdba
Connected.
SQL> grant create table to scott;
Grant succeeded.
SQL> conn scott/scott
Connected.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT UNLIMITED TABLESPACE NO
SCOTT CREATE TABLE NO
SQL> exec p_emp1;
BEGIN p_emp1; END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SCOTT.P_EMP1", line 4
ORA-06512: at line 1
--這裡報錯是因為剛剛執行儲存過程的時候已經drop掉emp1表了，但是create時候報錯，所以這是會報表不存在
SQL> create table emp1 as select * from emp;
Table created.
SQL> exec p_emp1;
PL/SQL procedure successfully completed.

給scott分批create table的許可權後，我們可以透過user_sys_privs檢視看到scott擁有的許可權，這時候儲存過程正常了。

*********************************************************************************************************

接下來測試下dml操作,給scott使用者dba的角色,然後讓scott使用者操作test使用者下的表

SQL> conn /as sysdba
Connected.
SQL> grant dba to scott;
Grant succeeded.
SQL> conn scott/scott
Connected.
SQL>
SQL> select * from role_sys_privs order by role;
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE SESSION NO
DATAPUMP_EXP_FULL_DATABASE CREATE SESSION NO
DATAPUMP_EXP_FULL_DATABASE CREATE TABLE NO
DATAPUMP_IMP_FULL_DATABASE ALTER DATABASE NO
DATAPUMP_IMP_FULL_DATABASE ALTER PROFILE NO
DATAPUMP_IMP_FULL_DATABASE ALTER RESOURCE COST NO
DATAPUMP_IMP_FULL_DATABASE ALTER USER NO
DATAPUMP_IMP_FULL_DATABASE AUDIT ANY NO
DATAPUMP_IMP_FULL_DATABASE AUDIT SYSTEM NO
DATAPUMP_IMP_FULL_DATABASE CREATE PROFILE NO
DATAPUMP_IMP_FULL_DATABASE CREATE SESSION NO
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DATAPUMP_IMP_FULL_DATABASE DELETE ANY TABLE NO
DATAPUMP_IMP_FULL_DATABASE EXECUTE ANY OPERATOR NO
DATAPUMP_IMP_FULL_DATABASE GRANT ANY OBJECT PRIVILEGE NO
DATAPUMP_IMP_FULL_DATABASE GRANT ANY PRIVILEGE NO
DATAPUMP_IMP_FULL_DATABASE GRANT ANY ROLE NO
DATAPUMP_IMP_FULL_DATABASE SELECT ANY TABLE NO
DBA ADMINISTER ANY SQL TUNING SET YES
DBA ADMINISTER DATABASE TRIGGER YES
DBA ADMINISTER RESOURCE MANAGER YES
DBA ADMINISTER SQL MANAGEMENT OBJECT YES
DBA ADMINISTER SQL TUNING SET YES
...
--下邊有很多就不顯示了，我們可以看到dba角色的許可權已經有了
SQL> set linesize 100
SQL> desc test.t;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER
SQL> select * from test.t;
ID
----------
1
SQL>
SQL> select line,text from user_source where type='PROCEDURE' and name='P_INSERT_T' ORDER BY LINE;
LINE TEXT
---------- --------------------------------------------------------------------------------
1 procedure p_insert_t as
2 begin
3 insert into test.t values (2);
4 end;
SQL> insert into test.t values (2); --可以看到直接insert是沒有問題的
1 row created.
SQL> select * from test.t;
ID
----------
1
2
SQL> rollback;
Rollback complete.
SQL> exec p_insert_t;
BEGIN p_insert_t; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object SCOTT.P_INSERT_T is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
--這裡報了sql無效，我們編譯下儲存過程看看報什麼錯誤
SQL> alter procedure p_insert_t compile;
Warning: Procedure altered with compilation errors.
SQL> show errors;
Errors for PROCEDURE P_INSERT_T:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3 PL/SQL: SQL Statement ignored
3/20 PL/SQL: ORA-00942: table or view does not exist
--這裡報表不存在，說明沒有對test使用者的t表的select許可權，我們來賦下許可權

SQL>
SQL> conn test/test
Connected.
SQL> grant select on t to scott;
Grant succeeded.
SQL> conn scott/scott
Connected.
SQL> alter procedure p_insert_t compile;
Warning: Procedure altered with compilation errors.
SQL> show errors;
Errors for PROCEDURE P_INSERT_T:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3 PL/SQL: SQL Statement ignored
3/20 PL/SQL: ORA-01031: insufficient privileges
--這時候開始報沒有insert許可權，繼續賦權
SQL>
SQL>
SQL> conn test/test
Connected.
SQL> grant insert on t to scott;
Grant succeeded.
SQL> conn scott/scott
Connected.
SQL> alter procedure p_insert_t compile;
Procedure altered.
SQL> select * from test.t;
ID
----------
1
SQL> exec p_insert_t;
PL/SQL procedure successfully completed.
SQL> select * from test.t;
ID
----------
1
2
--最後我們查下scott都有那些表才操作許可權
SQL> select * from user_tab_privs;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --- ---
SCOTT TEST T TEST INSERT NO NO
SCOTT TEST T TEST SELECT NO NO

所以在處理儲存過程的時候要注意這些許可權問題，不要只看使用者的角色。

******************************************************************************************************************

我們總是想偷偷懶，每次都要賦許可權感覺好麻煩，有沒有其他好辦法呢？答案是：有。oracle提供了一個authid的屬性

SQL> conn /as sysdba
Connected.
SQL> revoke create table from scott;
Revoke succeeded.
SQL> conn scott/scott
Connected.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT UNLIMITED TABLESPACE NO
SQL> select line,text from user_source where type='PROCEDURE' and name='P_EMP1' ORDER BY LINE;
LINE TEXT
---------- --------------------------------------------------------------------------------
1 procedure p_emp1
2 authid CURRENT_USER --注意這裡，多了一句
3 as
4 begin
5 execute immediate 'drop table emp1';
6 execute immediate 'create table emp1 as select * from emp';
7 end;
7 rows selected.
SQL> exec p_emp1;
PL/SQL procedure successfully completed.

新增一個authid CURRENT_USER就可以了，當然這麼做也有一點風險，就是如果scott和test使用者都有一個emp表，那麼當test使用者執行的時候，新建的emp1表會在test使用者下

SQL> select line,text from user_source where type='PROCEDURE' and name='P_EMP1' ORDER BY LINE;
LINE TEXT
---------- --------------------------------------------------------------------------------
1 procedure p_emp1
2 authid CURRENT_USER
3 as
4 begin
5 execute immediate 'create table emp1 as select * from emp'; --這裡我們去掉了drop的語句
6 end;
6 rows selected.
SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL> select owner,table_name from dba_tables where table_name='EMP1';
OWNER TABLE_NAME
------------------------------ ------------------------------
SCOTT EMP1
SQL> drop table scott.emp1;
Table dropped.
SQL> grant resource to test;
Grant succeeded.
SQL> grant execute on scott.p_emp1 to test;
Grant succeeded.
SQL> conn test/test;
Connected.
SQL> create table emp (id number) ;
Table created.
SQL> exec scott.p_emp1;
PL/SQL procedure successfully completed.
SQL> conn /as sysdba
Connected.
SQL> select owner,table_name from dba_tables where table_name='EMP1';
OWNER TABLE_NAME
------------------------------ ------------------------------
TEST EMP1

ORACLE中儲存過程的許可權問題

相關文章