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@pdb1SQL*Plus: Release 12.1.0.2.0 Production on 星期四 3月 24 13:58:36 2016Copyright (c) 1982, 2014, Oracle.  All rights reserved.连接到:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> 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_definerRETURN VARCHAR2   AUTHID DEFINERAS  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_invokerRETURN VARCHAR2  AUTHID CURRENT_USERAS  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=3SQL> 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=3SQL> 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.tab1SQL> 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=-1SQL> select * from cbac_user1.t1;select * from cbac_user1.t1                         *第 1 行出现错误:ORA-00942: 表或视图不存在