關於檢視和儲存過程的許可權問題探究

今天在處理一個工單的時候發現了一個奇怪的現象，開發同學需要建立一個儲存過程，目前的架構類似這樣的形式


資料庫中存在一個屬主使用者，表，儲存過程等物件都建立在這個使用者上，而另外有一些連線使用者，根據業務和功能可能訪問的物件許可權也有所不同。所以就會出現一個owner,多個connect user的情況。這種方式可以減少很多誤操作，許可權控制更為細粒度。
現在的問題是在owner使用者上建立儲存過程，儲存過程會引用若干張表，都在owner使用者下，而connect user下則沒有這些表相關的任何同義詞。看起來好像是不大合理啊，至少感覺資訊不夠完整，於是和開發的同學進行了確認，他們反饋這個儲存過程一直是connect user執行，沒有任何問題，當然在處理完之後，我還是帶著疑惑測試了一遍，發現果真如此，開始讓我有一種毀三觀的感覺。
我們來測試一下，步驟很明確，先來初始化資料，建立兩個使用者，一個owner,一個connect user,然後建立一個儲存過程，模擬當時的問題。
create user testo identified by oracle;
create user testc identified by oracle;
grant connect to testc;
grant connect,resource to testo;
alter session set current_schema=testo;
建立表test_bind，需要在後面的儲存過程中引用，保證表中有儲存過程呼叫合適的資料。
create table test_bind as select object_id cn,object_id cid from all_objects;
update test_bind set cn=100 where rownum<2;
update testo.test_bind set cn=101 where cn=100 and rownum<2   ;
SQL> select count(*) from testo.test_bind where cn=100;
  COUNT(*)
----------
         1
建立儲存過程
create or replace procedure test_proc
AS
tmp_cid test_BIND.CID%TYPE;
BEGIN
        SELECT CID INTO tmp_cid
        FROM test_BIND WHERE CN = 100;
dbms_output.put_line(tmp_cid);
END;
/
賦予許可權，建立儲存過程的同義詞，注意此處是沒有建立表的同義詞
grant execute on testo.test_proc to testc;
create synonym testc.test_proc for testo.test_proc;
開始復現問題：
alter session set current_schema=testc;
set serveroutput on
儲存過程呼叫沒有問題
SQL> exec test_proc;
100
PL/SQL procedure successfully completed.
檢視test_bind這個表是否可訪問
SQL> desc testc.test_BIND
ERROR:
ORA-04043: object testc.test_BIND does not exist

小結 由此可以看出，owner使用者上的儲存過程，裡面涉及的表在connect 使用者上沒有對應的同義詞時，儲存過程呼叫沒有問題。可見儲存過程的執行是完全基於owner使用者的。

當然儲存過程的許可權問題了解了，我的印象中檢視似乎也有點矯情，有時候許可權的要求比較高。在此一併矯正一下錯誤的觀點。
我們建立一個新的connect使用者testc2,然後測試檢視的情況。
如果我們在owner使用者上建立檢視，測試一下是否許可權也會有類似的問題。
create user testc2 identified by oracle;
grant connect to testc2;
alter session set current_schema=testo;
建立角色testo_role，所有的許可權都通過testo_role來控制
create role testo_role;
建立檢視
create view view_test_bind as select *from test_bind;
給角色testo_role賦予許可權
grant select on view_test_bind to testo_role;
角色賦予connect使用者testc2
grant testo_role to testc2;
alter session set current_schema=testc2;
建立同義詞
create synonym testc2.view_test_bind for testo.view_test_bind;
檢視檢視的結構
desc testc2.view_test_bind
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CN                                        NOT NULL NUMBER
 CID                                       NOT NULL NUMBER
檢視owner下的表test_bind是否可以在testc2下訪問
SQL> desc testc2.test_bind
ERROR:
ORA-04043: object testc2.test_bind does not exist

小結

由此可以看出情況和儲存過程是類似的

那麼關於檢視還有什麼矯情的問題呢。印象中是有的。
我們在connect使用者上建立檢視
先把訪問testo.test_bind的許可權給角色testo_role
grant select on testo.test_bind to testo_role;
然後建立同義詞
create synonym testc2.test_bind for testo.test_bind;
alter session set current_schema=testc2;
在connect使用者下建立檢視
SQL> create view view_test_bind as select *from testc2.test_bind;
create view view_test_bind as select *from testc2.test_bind
                                                  *
ERROR at line 1:
ORA-01031: insufficient privileges
而賦予了物件許可權之後，檢視的建立就引刃而解了。

小結

所以對於檢視而言，在連線使用者上建立檢視需要物件許可權而角色許可權會有限制。
所以推薦的架構方式為：


下面是我的公眾號二維碼，歡迎掃描關注。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-2123297/，如需轉載，請註明出處，否則將追究法律責任。