ORA-01031 权限不足-过程中DBA 角色用户无法执行DDL
Oracle的存儲(chǔ)過程，是我們使用數(shù)據(jù)庫應(yīng)用開發(fā)的重要工具手段。在存儲(chǔ)過程中，我們大部分應(yīng)用場(chǎng)景都是使用DML語句進(jìn)行數(shù)據(jù)增刪改操作。本篇中，我們一起探討一下數(shù)據(jù)定義語句DDL在存儲(chǔ)過程中使用的細(xì)節(jié)和要點(diǎn)。
?
1、“借道而行”的DDL
?
從Oracle PL/SQL和存儲(chǔ)過程程序開發(fā)原則上，應(yīng)該是不鼓勵(lì)在SP中使用DDL語句的。首先一個(gè)表現(xiàn)，就是Oracle在編譯時(shí)就不允許直接在SP中使用DDL語句。下面我們使用Oracle 10gR2作為實(shí)驗(yàn)環(huán)境。
?
?
SQL> select * from v$version;
?
BANNER
----------------------------------------------------------------
Oracle Database10gEnterpriseEdition Release10.2.0.1.0 - Prod
PL/SQL Release10.2.0.1.0 - Production
CORE????10.2.0.1.0??????Production
?
TNS for 32-bit Windows: Version10.2.0.1.0 - Production
NLSRTL Version10.2.0.1.0 – Production
?
?
建立存儲(chǔ)過程p_test_nc，進(jìn)行簡(jiǎn)單的數(shù)據(jù)表創(chuàng)建。
?
?
SQL> create or replace procedure P_TEST_NC
?2?is
?3?begin
?4???create?table?t (id number);
?5?end P_TEST_NC;
?6?/
?
Warning: Procedure created with compilation errors
?
SQL> select name, text from user_errors;
?
NAME??????TEXT
---------- --------------------------------------------------------------------------------
P_TEST_NC?PLS-00103:出現(xiàn)符號(hào)"CREATE"在需要下列之一時(shí)：
???????????begin case declare exit
?????????????for goto if loop mod null pragma raise return select update
?????????????while with <an identifier>
?????????????<a double-quoted delimited-identifier> <a bind variable> <<
?????????????close current delete fetch lock insert open rollback
?????????????savepoint set?sql?execute commit forall merge pipe
?
?
顯然，在編譯時(shí)Oracle就報(bào)錯(cuò)不允許存儲(chǔ)過程創(chuàng)建。之后的實(shí)驗(yàn)drop和truncate table操作，也都是不允許直接在存儲(chǔ)過程中書寫DDL語句。說明起碼使用直接的DDL語句，存儲(chǔ)過程是不能編譯通過的。
?
那么，有沒有什么折中的方法呢？我們說是有的，就是借助“execute immediate”方法，“繞過”編譯過程中對(duì)DDL的屏蔽。我們使用truncate table DDL語句實(shí)驗(yàn)。
?
?
SQL> create or replace procedure P_TEST_NC
?2?is
?3?begin
??4???execute immediate'truncate table t';
?5?end P_TEST_NC;
?6?/
Procedure created
?
?
編譯通過了，DDL語句以一個(gè)字符串的形式避開了編譯時(shí)Oracle的語法檢查，編譯成功。那么，執(zhí)行起來會(huì)不會(huì)報(bào)運(yùn)行時(shí)錯(cuò)誤呢？
?
?
SQL> exec p_test_nc;
PL/SQL procedure successfully completed
?
?
執(zhí)行成功，說明：在Oracle存儲(chǔ)過程中，可以使用exectue immediate語句繞開編譯時(shí)對(duì)DDL語句的檢查，生成運(yùn)行代碼。
?
?
2、SP中DDL權(quán)限
?
任何程序編譯執(zhí)行都會(huì)伴隨著語法語義的一系列檢查。使用execute immediate雖然可以回避編譯時(shí)檢查，但是SQL語句還是面臨著運(yùn)行時(shí)檢查的問題。下面看實(shí)驗(yàn)的例子。
?
--在scott用戶下進(jìn)行試驗(yàn)；
SQL> create or replace procedure P_TEST_NC
?2?is
?3?begin
?4???execute immediate 'create table t(id number)';
?5?end P_TEST_NC;
?6?/
Procedure created–編譯時(shí)通過；
?
SQL> exec p_test_nc;
?
begin p_test_nc; end;
?
ORA-01031:權(quán)限不足
ORA-06512:在"SCOTT.P_TEST_NC", line 4
ORA-06512:在line 1
?
?
在用戶自己的schema下創(chuàng)建數(shù)據(jù)表，難道是不允許的嗎？顯然不是。
?
?
SQL> create table m (id number);
Table created
?
?
單獨(dú)創(chuàng)建是允許的，說明是由于權(quán)限機(jī)制導(dǎo)致的問題。我們切換到sys用戶上，提高scott用戶權(quán)限。
?
?
Connected as SYS
--賦予最高創(chuàng)建數(shù)據(jù)表的系統(tǒng)權(quán)限；
SQL> grant create any table to scott;
Grant succeeded
?
?
切換回scott用戶，繼續(xù)實(shí)驗(yàn)。
?
?
SQL> conn scott/tiger@orcl;
Connected to Oracle Database10gEnterpriseEdition Release10.2.0.1.0
Connected as scott
?
SQL> exec p_test_nc;
PL/SQL procedure successfully completed
?
SQL> select * from t;
???????ID
----------
?
執(zhí)行成功！這個(gè)原因是什么呢？還是由于存儲(chǔ)過程權(quán)限體系特點(diǎn)和DDL語句特點(diǎn)共同造成的。
?
在之前筆者的系列文章《所有者權(quán)限和調(diào)用者權(quán)限》（http://space.itpub.net/17203031/viewspace-692161）中，介紹了Oracle存儲(chǔ)過程采用的兩種權(quán)限體系方式和role權(quán)限在存儲(chǔ)過程執(zhí)行中的特殊性。
?
默認(rèn)情況下，Oracle對(duì)存儲(chǔ)過程是使用所有者權(quán)限，也就是說：如果用戶B調(diào)用了用戶A schema下的一個(gè)存儲(chǔ)過程，其中使用的對(duì)象權(quán)限和系統(tǒng)權(quán)限，全部都是用戶A的。如果用戶A沒有權(quán)限，用戶B執(zhí)行要報(bào)錯(cuò)。
?
同時(shí)，用戶的角色權(quán)限在進(jìn)入存儲(chǔ)過程后，會(huì)被剝離掉，是不其效果的。
?
結(jié)合上面的實(shí)驗(yàn)，就好解釋了：scott自身只擁有一個(gè)resource的角色權(quán)限，單獨(dú)在SQL中使用沒有問題。進(jìn)入到SP之后，這個(gè)create table的權(quán)限就被剝離掉了。而該SP存在被其他用戶調(diào)用生成數(shù)據(jù)表的可能。所以會(huì)在運(yùn)行時(shí)報(bào)錯(cuò)權(quán)限不足。
?
當(dāng)我們顯示的賦予scott用戶create any table/create table之后，系統(tǒng)權(quán)限就可以滲透到SP中起效果了。
?
這并不是解決該問題的唯一方法。此處我們可以使用調(diào)用者權(quán)限機(jī)制，改寫SP代碼。首先我們剔除掉scott的create any table權(quán)限。
?
?
Connected to Oracle Database10gEnterpriseEdition Release10.2.0.1.0
Connected as SYS
?
SQL> revoke create any table from scott;
Revoke succeeded
?
SQL> conn scott/tiger@orcl;
Connected to Oracle Database10gEnterpriseEdition Release10.2.0.1.0
Connected as scott
?
SQL> exec p_test_nc;
begin p_test_nc; end;
?
ORA-01031:權(quán)限不足
ORA-06512:在"SCOTT.P_TEST_NC", line 4
ORA-06512:在line 1
?
?
我們改寫代碼為：
?
?
SQL> create or replace procedure P_TEST_NC
?2?Authid Current_User
?3?is
?4?begin
?5???execute immediate 'create table t (id number)';
?6?end P_TEST_NC;
?7?/
Procedure created
?
SQL> exec p_test_nc;
PL/SQL procedure successfully completed
?
?
執(zhí)行成功，這里使用“authid Current_user”將存儲(chǔ)過程轉(zhuǎn)化為調(diào)用者權(quán)限。每次調(diào)用存儲(chǔ)過程，都是動(dòng)態(tài)根據(jù)調(diào)用者的權(quán)限構(gòu)成去判定是否有權(quán)限，這樣就回避了該問題的出現(xiàn)。
?
總之：在使用DDL在存儲(chǔ)過程中時(shí)，權(quán)限管理和使用的復(fù)雜度是在增加。
?
?
4、DDL對(duì)事務(wù)的提交影響
?
將DDL語句放置在存儲(chǔ)過程中，潛在最大風(fēng)險(xiǎn)就是對(duì)事務(wù)管理的破壞。在Oracle中，如果調(diào)用一個(gè)DDL語句，潛藏效果就是將當(dāng)前會(huì)話的未提交事務(wù)進(jìn)行提交。這個(gè)過程顯然是對(duì)原有的事務(wù)邏輯破壞。
?
?
SQL> create table m (id number);
Table created
?
SQL> select * from m;
???????ID
----------
?
SQL> create or replace procedure P_TEST_NC
?2?is
?3?begin
?4???insert into m values (3);
?5???execute immediate 'truncate table t';
?6?
?7???rollback;
?8?end P_TEST_NC;
?9?/
?
Procedure created
?
--執(zhí)行代碼
SQL> exec p_test_nc;
PL/SQL procedure successfully completed
?
--事務(wù)提交
SQL> select * from m;
???????ID
----------
????????3
?
?
從上面的例子上，我們可以清楚的看到現(xiàn)象。由于中間的truncate table操作，引起數(shù)據(jù)表m的插入操作被提交commit。而真正的事務(wù)邏輯可能是一個(gè)rollback。
?
所以，在SP中使用DDL命令，可能引起業(yè)務(wù)邏輯的不可控提交和數(shù)據(jù)不一致，這個(gè)風(fēng)險(xiǎn)在任何應(yīng)用中是不可以允許的。
?
那么，有沒有方法回避這個(gè)過程呢？經(jīng)一個(gè)同事提醒，的確可以使用手段回避。
?
?
5、DDL與自治事務(wù)
?
自治事務(wù)（AUTONOMOUS_TRANSACTION）是保證在事務(wù)進(jìn)行過程中一段獨(dú)立的事務(wù)過程。如果在DDL操作外套入一個(gè)自治事務(wù)過程，是否就可以回避問題了。
?
?
SQL> select * from m;
?
???????ID
----------
?
SQL> create or replace procedure P_TEST_NC is
?2???procedure p_inner_test
?3???is
?4???PRAGMA AUTONOMOUS_TRANSACTION;
?5???begin
?6?????--調(diào)用ddl
?7?????execute immediate 'truncate table t';
?8???end;
?9?begin
?10???insert into m values (3);
?11???p_inner_test;
?12?
?13???rollback;
?14?end P_TEST_NC;
?15?/
Procedure created
?
SQL> exec p_test_nc;
PL/SQL procedure successfully completed
\
SQL> select * from m;
???????ID
----------
?
?
實(shí)驗(yàn)成功，通過自治事務(wù)的確可以回避DDL的事務(wù)問題。
?
6、結(jié)論
?
DDL在SP中，與常規(guī)的DML操作差異很大。這種差異不僅僅是語法上，更多的是權(quán)限、事務(wù)等更深層次復(fù)雜的差異。所以，從Oracle的角度看，盡量少在SP中使用DDL語句，避免出現(xiàn)不可控的問題。
?
PLS-00157:?? AUTHID?only allowed on schema-level programs
查了下錯(cuò)誤原因 An AUTHID clause was specified for a subprogram inside a package or type. These clauses are only supported for top-level stored procedures, packages, and types.
大致意思就是authid只能用在頂級(jí)的存儲(chǔ)過程、包、類型上，不能用在包或類型的子程序上。
?
在包上加入authid，執(zhí)行正常了。
create or replace package rule_execute
authid current_user
Oracle DB對(duì)于PLSQL程序提供兩種不同的對(duì)象權(quán)限. 默認(rèn)的(也是在8i以前唯一的)模式才能為定義者權(quán)限(definer rights, DR)
在這種模式下, PLSQL存儲(chǔ)程序通過定義者的權(quán)限執(zhí)行. 另外一種稱為執(zhí)行者權(quán)限(invoker rights, IR), 顧名思義, 即采用
執(zhí)行者的權(quán)限來進(jìn)行存儲(chǔ)程序.
定義為定義者權(quán)限下的子程序稱為DR Unit, 定義在執(zhí)行者權(quán)限下的子程序稱為IR Unit
匿名塊默認(rèn)執(zhí)行在IR權(quán)限下, 觸發(fā)器默認(rèn)執(zhí)行在DR權(quán)限下.
定義者權(quán)限(DR)
1. 任何外部引用的解析都發(fā)生在編譯時(shí), 通過對(duì)該用戶(定義該子程序的用于)的直接賦予權(quán)限來解析.
2. 所有編譯需要的權(quán)限必須通過直接授予的方式獲得(e.g directly grant previliges),?
3. 數(shù)據(jù)庫角色(roles) 在編譯時(shí)將被完全忽略.
4. DR程序中SQL語句永遠(yuǎn)執(zhí)行在創(chuàng)建該程序的用戶權(quán)限之下.
5. 盡管你需要直接賦予的權(quán)限來編譯該程序, 但你可以通過 grant EXECUTE 語句給其他用戶運(yùn)行該
? ?程序的權(quán)限.
DR的問題:
1. 比如在你的企業(yè)里面一種通常的做法是程序員會(huì)讀寫其他用戶的某些表. 這些表的讀寫權(quán)限都
? ? 被賦予給該程序員, 并且通過創(chuàng)建public synonyms來實(shí)現(xiàn)其他用戶的信息隱藏.
? ? 比如, 下面的語句可以順利的在SQL PLUS下運(yùn)行:
? ?? ?select student_no, first_name, last_name
? ?? ?from students;
? ? 但如果你試圖運(yùn)行下面的子程序:
? ? create or replace procedure show_students
? ? is
? ? begin
? ?? ?? ? for rec in (select student_no, first_name, last_name from students)
? ?? ?? ? loop
? ?? ?? ?? ? dbms_output.put_line(rec.first_name);
? ?? ?? ? end loop;
? ? end;
? ? /?
? ?你將會(huì)得到錯(cuò)誤信息:
? ? PL/SQL: SQL Statement ignored
? ? PLS-00201: identifier 'STUDENTS' must be declared
第二種情況是通過子程序?qū)?shù)據(jù)庫進(jìn)行DDL操作. 這種情況下調(diào)用該子程序的對(duì)象由于采用
創(chuàng)建者的權(quán)限從而無法在自己的用戶空間創(chuàng)建對(duì)象, 從而產(chǎn)生錯(cuò)誤信息. 這種情況, 即使
在表命前加上用戶名也會(huì)報(bào)權(quán)限不夠的錯(cuò)誤, 比如. hr.departments
綜上, 有DDL操作的子程序不適合采用DR模式.
第二種權(quán)限模式是 IR模式, 這種模式采用當(dāng)前用戶權(quán)限去解析引用對(duì)象.
AUTHID CURRENT_USER
因此, 對(duì)于引用對(duì)象, 要求調(diào)用者具有與定義者相同的權(quán)限才能成功執(zhí)行.
1) DR是默認(rèn)選項(xiàng), 不指定的情況下. 但匿名塊是IR
2) IR與DR不同, DR在編譯是用定義者權(quán)限解析引用對(duì)象, 而IR是在執(zhí)行時(shí)(run-time)對(duì)引用對(duì)象
? ?? ?? ?? ?? ?? ?? ?采用執(zhí)行者權(quán)限去解析.
3)?對(duì)IR, 角色(role)權(quán)限在運(yùn)行時(shí)是有效的, 只要這個(gè)IR子程序不是從一個(gè)DR的父程序進(jìn)行的調(diào)用即可.
4) AUTHID 子句只能用于單獨(dú)子程序的頭部, 包的頭部, 或者對(duì)象類型頭部, 而不能用在包或者對(duì)象類型
? ?中單獨(dú)的子程序的頭部. 換句話說, 對(duì)于一個(gè)包而言, 整體要么是DR要么是IR
5) 在編譯時(shí),對(duì)于子程序或者對(duì)象類型的過程, 永遠(yuǎn)都是采用DR來解析外部引用.
6) DR的優(yōu)先級(jí)高于IR
7) IR的缺點(diǎn)是在運(yùn)行時(shí)會(huì)有對(duì)用戶權(quán)限的檢查過程, 會(huì)產(chǎn)生額外的開銷從而對(duì)性能有所影響.
總結(jié)
以上是生活随笔為你收集整理的ORA-01031 权限不足-过程中DBA 角色用户无法执行DDL的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 数据库启动提示： ORA-27102:
- 下一篇： 软件的Express Edition是什