oracle通過sys_context實現各使用者只能訪問屬於自己的資料

客戶說，現在資料安全比較重要，要實現不同使用者登入後，只能訪問屬於自己的資料。聽到客戶這個要求後，我首先想到的是用 sys_context('USERENV','SESSION_USER'）這個方式實現，但在實際實施的過程中，也遇到了一些麻煩，下面就把實施過程與大家分享一下。

1、簡單說明下

在資料庫中有user001,user002,user003,user004。在做業務的時候，我們在這個表上有一個對應關係，就這個表的userid：24401,24402,24403,24404分別與使用者對應。

表的情況如下（mydata下面存在一個這樣的表：mydata.bi_dis_t）：

SQL>  select * from mydata.bi_dis_t;

     GOODSID GOODSNAME                                GOODSTYPE                                GOODSUNIT      USERID
----------- ---------------------------------------- ---------------------------------------- ---------- ----------
          34 生命一號(禮盒裝)                         10ml*60支+240s*1*5提                     提              24401
         276 國公酒(簡裝)                             328ml*24瓶                               瓶              24403
         277 國公酒(精裝)                             328ml*24瓶                               瓶              24401
         438 鹽酸氨溴索口服溶液（沐舒坦）             100ml:0.6g*36瓶                          瓶              24402
         480 古漢養生精口服液                         10ml*12支*40盒                           盒              24401
         504 烏雞白鳳丸(北京白鳳丸)(禮盒裝)           6g*6丸*40盒                              盒              24402
         550 生命一號                                 10ml*10支+40粒*1*40                      盒              24404
         576 京都念慈菴蜜煉川貝枇杷膏*(新批文)        300ml*48瓶                               瓶              24404
         672 迴音必孕康口服液                         10ml*10支*50盒                           盒              24401
         674 抗病毒口服液                             10ml*12支*50盒                           盒              24403
         729 福壽仙多糖王口服液                       10ml*10支*40盒                           盒              24403
         769 轉移因子口服溶液                         10mg:10ml*6支*60盒                       盒              24401

也就是說，用user001登入時只查userid=24401，以此類推。 對於這種情況，很快可以想到用sys_context的方式來實現，在實現過程中，我們很我們遇到了ora-01031的錯誤。

2、開始進行測試，理論沒什麼問題，但要實踐一下

建立相應的使用者並授權：

SQL> create user user001 identified by 123456;

User created.

SQL> create user user002 identified by 123456;

User created.

SQL> create user user003 identified by 123456;

User created.

SQL> create user user004 identified by 123456;

User created.



3、建相應的對照表，並插入對應資料，user_map已經存在，客戶要求把對照表建在這個使用者下：

SQL> create table user_map.map_t(username varchar2(10),map_id number(6));

Table created.

SQL> select * from user_map.map_t;

no rows selected

SQL> insert into user_map.map_t values('USER001',24401);

1 row created.

SQL> insert into user_map.map_t values('USER002',24402);

1 row created.

SQL> insert into user_map.map_t values('USER003',24403);

1 row created.

SQL> insert into user_map.map_t values('USER004',24404);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM user_map.map_t;      

USERNAME       MAP_ID
---------- ----------
USER001         24401
USER002         24402
USER003         24403
USER004         24404


4、進行基本的一些授權

SQL> grant resource,connect to user001,user002,user003,user004;

Grant succeeded.

SQL> grant select on v_$session to user001,user002,user003,user004;

Grant succeeded.

SQL> grant select on v_$sesstat to user001,user002,user003,user004;

Grant succeeded.

SQL> grant select on v_$statname to user001,user002,user003,user004;

Grant succeeded.

SQL> grant select on mydata.bi_dis_t to user001,user002,user003,user004,user_map;

Grant succeeded.

SQL> grant select on user_map.map_t to user001,user002,user003,user004;

Grant succeeded.


5、建立相應的檢視和同義詞，並授權

SQL> create view user_map.bi_dis_t_v as
   2   select a.* from mydata.bi_dis_t a,
   3    (select map_id from user_map.map_t where username = sys_context('USERENV','SESSION_USER')) b
   4   where b.map_id = a.userid;

View created.

SQL> grant select on user_map.bi_dis_t_v to user001,user002,user003,user004;

Grant succeeded.

SQL> create public synonym bi_dis_t_v_s for user_map.bi_dis_t_v;

Synonym created.


6、上面的一切工作是相當的順利，看看測試情況：

SQL> connect user001/123456;
Connected.
SQL> select * from bi_dis_t_v_s;
select * from bi_dis_t_v_s
               *
ERROR at line 1:
ORA-01031: insufficient privileges

問題出來了，報許可權不足，仔細檢查一下，許可權都沒有問題，並且通過user001查詢檢視的表也沒問題。於是想授dba許可權試一下，結果如下：

SQL> grant dba to user001;

SQL> select count(*) from bi_dis_t_v_s;

   COUNT(*)
----------
         44

通過dba，是沒問題的，所以我告訴客戶說對幾個使用者授予dba，客戶立馬否定了。說老實話，我也會否定，安全大於天，事怕意外呀！

必須再想其他的辦法。

7、問題解決

我重新檢查一下檢視、表的情況許可權沒有問題，於是，我把表bi_dis_t複製一個到user_map下面：

SQL> create table user_map.bi_dis_t as select * from mydata.bi_dis_t;

Table created.

再將上面的檢視做一下調整：

SQL> create or replace view user_map.bi_dis_t_v as
   2   select a.* from user_map.bi_dis_t a,
   3    (select map_id from user_map.map_t where username = sys_context('USERENV','SESSION_USER')) b
   4   where b.map_id = a.userid;


SQL> connet user002/123456;
Connected.

SQL> select count(*) from bi_dis_t_v_s;

   COUNT(*)
----------
         44

這裡讓我有點眉目了，就是檢視裡面出現的mydata這個使用者出現的問題，也就是說一個使用者通過第二個使用者去訪問第三個使用者的資料出現問題。也就是說我剛才授予的許可權通過檢視以後，就已經不生效，問題出在這裡，就比較好辦了，授權時加上引數grant option

SQL> grant select on mydata.bi_dis_t to user_map with grant option;

Grant succeeded.

把檢視恢復成mydata.bi_dis_t

再執行語句：

SQL> connect user002/123456;
Connected.

SQL> select count(*) from bi_dis_t_v_s;

   COUNT(*)
----------
         82

最後說明 ，當一個使用者通過另外一個使用者（檢視）去訪問其它使用者的資料（包括表、檢視等），授權時需要用 with grant option

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