今天在處理一個工單的時候發現了一個奇怪的現象，開發同學需要建立一個存儲過程，目前的架構類似這樣的形式
資料庫中存在一個屬主使用者，表，存儲過程等對象都建立在這個使用者上，而另外有一些連接配接使用者，根據業務和功能可能通路的對象權限也有所不同。是以就會出現一個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;
建立角色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
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;
在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
而賦予了對象權限之後，視圖的建立就引刃而解了。
是以對于視圖而言，在連接配接使用者上建立視圖需要對象權限而角色權限會有限制。
是以推薦的架構方式為：
下面是我的公衆号二維碼，歡迎掃描關注。