oracle 12c code based access control

-- 创建用户和基本授权
SQL> alter session set container = pdb1;

会话已更改。

SQL> create user cbac_user1 identified by 123 quota unlimited on users;

用户已创建。

SQL> grant create session,create table,create procedure to cbac_user1;

授权成功。

SQL> create user cbac_user2 identified by 123 quota unlimited on users;

用户已创建。

SQL> grant create session to cbac_user2;

授权成功。

-- 环境准备
C:\Users\Administrator>sqlplus cbac_user1/123@pdb1

SQL*Plus: Release 12.1.0.2.0 Production on 星期四 3月 24 13:58:36 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions

SQL> create table t1(id number);

表已创建。

SQL> insert into t1 select level from dual connect by level <=3;

已创建 3 行。

SQL> commit;

提交完成。

SQL> create table t2(id number);

表已创建。

SQL> insert into t2 select level from dual connect by level <=3;

已创建 3 行。

SQL> commit;

提交完成。

-- 创建测试函数
CREATE OR REPLACE FUNCTION get_count_definer
RETURN VARCHAR2 
  AUTHID DEFINER
AS
  l_count1 NUMBER;
  l_count2 NUMBER;
  l_return VARCHAR2(32767);
BEGIN
  SELECT COUNT(*)
  INTO   l_count1
  FROM   cbac_user1.t1;

  SELECT COUNT(*)
  INTO   l_count2
  FROM   cbac_user1.t2;

  l_return := 'CallUser=' || USER ||
              '  PrivUser=' || SYS_CONTEXT('userenv', 'CURRENT_USER') ||
              '  T1Count=' || l_count1 ||
              '  T2Count=' || l_count2;
  
  RETURN l_return;
END;
/

CREATE OR REPLACE FUNCTION get_count_invoker
RETURN VARCHAR2
  AUTHID CURRENT_USER
AS
  l_count1 NUMBER;
  l_count2 NUMBER;
  l_return VARCHAR2(32767);
BEGIN
  SELECT COUNT(*)
  INTO   l_count1
  FROM   cbac_user1.t1;
  BEGIN
    SELECT COUNT(*)
    INTO   l_count2
    FROM   cbac_user1.t2;
  EXCEPTION
    WHEN OTHERS THEN
      l_count2 := -1;
  END;
  l_return := 'CallUser=' || USER ||
              '  PrivUser=' || SYS_CONTEXT('userenv', 'CURRENT_USER') ||
              '  T1Count=' || l_count1 ||
              '  T2Count=' || l_count2;
  RETURN l_return;
END;
/

-- 授权测试
SQL> grant execute on get_count_definer to cbac_user2;

授权成功。

SQL> grant execute on get_count_invoker to cbac_user2;

授权成功。

SQL> conn cbac_user2/123@pdb1
已连接。
SQL> select cbac_user1.get_count_definer from dual;

GET_COUNT_DEFINER
-------------------------------------------------------------------------------

CallUser=CBAC_USER2  PrivUser=CBAC_USER1  T1Count=3  T2Count=3

SQL> select cbac_user1.get_count_invoker from dual;
select cbac_user1.get_count_invoker from dual
       *
第 1 行出现错误:
ORA-00942: 表或视图不存在
ORA-06512: 在 "CBAC_USER1.GET_COUNT_INVOKER", line 9

-- 12c 之前的处理方法
SQL> conn / as sysdba
已连接。
SQL> alter session set container=pdb1;

会话已更改。

SQL> create role cbac_role;

角色已创建。

SQL> grant cbac_role to cbac_user1,cbac_user2;

授权成功。

SQL> grant select on cbac_user1.t1 to cbac_role;

授权成功。

SQL> conn cbac_user2/123@pdb1
已连接。
SQL> select cbac_user1.get_count_definer from dual;

GET_COUNT_DEFINER
------------------------------------------------------------------------------

CallUser=CBAC_USER2  PrivUser=CBAC_USER1  T1Count=3  T2Count=3

SQL> select cbac_user1.get_count_invoker from dual;

GET_COUNT_INVOKER
------------------------------------------------------------------------------

CallUser=CBAC_USER2  PrivUser=CBAC_USER2  T1Count=3  T2Count=-1

-- 但是此时cbac_user2还能访问cbac_user1.tab1
SQL> select * from cbac_user1.t1;

        ID
----------
         1
         2
         3

-- 12c 的Code Based Access Control可以实现我们要求的功能
SQL> conn / as sysdba
已连接。
SQL> alter session set container=pdb1;

会话已更改。

SQL> revoke cbac_role from cbac_user2;

撤销成功。

SQL> grant cbac_role to function cbac_user1.get_count_invoker;

授权成功。

SQL> conn cbac_user2/123@pdb1
已连接。
SQL> select cbac_user1.get_count_invoker from dual;

GET_COUNT_INVOKER
---------------------------------------------------------------------------

CallUser=CBAC_USER2  PrivUser=CBAC_USER2  T1Count=3  T2Count=-1

SQL> select * from cbac_user1.t1;
select * from cbac_user1.t1
                         *
第 1 行出现错误:
ORA-00942: 表或视图不存在