客户说，现在数据安全比较重要，要实现不同用户登录后，只能访问属于自己的数据。听到客户这个要求后，我首先想到的是用 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