--构造用户表及数据,schema=AAA,该表的Select权限需要授予CKERK1CKERK2CKERK3
CREATE TABLE account
( ACCNO number(10),
  ACC_NAME varchar(50),
  ACC_BAL number(10)
  );
  
Insert into  account values(1,'jolin',900);
Insert into  account values(2,'jill',1100);
Insert into  account values(3,'joe',1200);
Insert into  account values(4,'jack',1000);
Insert into  account values(5,'lake',1500);
Insert into  account values(6,'ivan',1200);
Insert into  account values(7,'tony',1100);
Insert into  account values(8,'dennis',1300);
Insert into  account values(9,'clack',1400);

--DBA构造级别表及数据,该表的Select权限要授予ckerk1ckerk2ckerk3
CREATE TABLE user_level
( USERNAME varchar(10),
  USERLEVEL number(5)
  );
  
Insert into  user_level values('CKERK1',1);  -- <1000
Insert into  user_level values('CKERK2',2);  -- <1200
Insert into  user_level values('CKERK3',3);  -- all

--DBA创建应用程序环境,用于设置存贮用户首次登陆时的用户级别
CREATE CONTEXT user_level_ctx using set_user_level_ctx;

CREATE OR REPLACE PROCEDURE set_user_level_ctx
(p_level in number)
as
begin
     dbms_session.set_context('user_level_ctx','level',p_level);
end;

--以DBA角色创建Trigger以设置迎合程序环境,该Trigger调用上面定义的Procedure
CREATE OR REPLACE TRIGGER tr_set_user_level after logon on database
declare
     l_level number;
begin
     select userlevel into l_level from sys.user_level
            where username=(select sys_context('userenv','session_user') from dual);
--            sys_context('userenv','session_user');
--            (select b.username from v$process a,v$session b,v$instance d
--             where a.addr=b.paddr and b.audsid=sys_context('userenv','sessionid');              )


     set_user_level_ctx (l_level);
--exception
--     when no_data_fount then null;
--     when others then raise;
end;


--建立用户ckerk1,ckerk2,ckerk3
CREATE USER ckerk1 identified by ckerk1;
GRANT CONNECT,RESOURCE TO ckerk1;

CREATE USER ckerk2 identified by ckerk2;
GRANT CONNECT,RESOURCE TO ckerk2;

CREATE USER ckerk3 identified by ckerk3;
GRANT CONNECT,RESOURCE TO ckerk3;

--测试用户连接情况
conn ckerk1/ckerk1;
select sys_context('USER_LEVEL_CTX','LEVEL') from dual;

conn ckerk1/ckerk2;
select sys_context('USER_LEVEL_CTX','LEVEL') from dual;

conn ckerk1/ckerk3;
select sys_context('USER_LEVEL_CTX','LEVEL') from dual;

--建立VPD策略函数
--CREATE OR REPLACE FUNCTION get_acc_max_bal
--(p_schema in varchar2,
--    p_obj in varchar2
-- )
--return varchar2
--is
--  l_ret varchar2(2000);
--begin
--    select
   
--    case userlevel
--    when 1 then 'acc_bal<=1000'
--    when 2 then 'acc_bal<=1200'
--    when 3 then null
--else
--    '1=2'
--end
--    into l_ret from user_level where username='HR'
--    return l_ret;
--end;

--建立VPD策略函数
CREATE OR REPLACE FUNCTION GET_ACC_MAX_BAL
(p_schema in varchar2(500),
    p_obj in varchar2(500),
    userlevel_1 NUMBER(10))
return varchar2
is
  l_ret varchar2(2000);
begin  
    select sys_context('USER_LEVEL_CTX','LEVEL') into userlevel_1 from dual;
    CASE userlevel_1
    WHEN 1 THEN l_ret:='acc_bal<=1000';
    WHEN 2 THEN l_ret:='acc_bal<=1200';
    WHEN 3 THEN l_ret:='null';
    WHEN 4 THEN l_ret:='sys_sys';
    end CASE;
    DBMS_OUTPUT.PUT_LINE(l_ret);
    return l_ret;
end;

--建立VPD策略
begin dbms_rls.add_policy
(object_schema=>'AAA',
object_name=>'ACCOUNT',
policy_name=>'account_access',
function_schema=>'SYS',
policy_function=>'get_acc_max_bal',
statement_types=>'INSERT,UPDATE,DELETE,SELECT',
update_check=>TRUE,
enable=>TRUE
);
end;




declare
userlevel_1 NUMBER(10);
l_ret varchar2(500);
begin
  select sys_context('USER_LEVEL_CTX','LEVEL') into userlevel_1 from dual;
  CASE userlevel_1
  WHEN 1 THEN l_ret:='acc_bal<=1000';
  WHEN 2 THEN l_ret:='acc_bal<=1200';
  WHEN 3 THEN l_ret:='null';
  WHEN 4 THEN l_ret:='sys_sys';
  end CASE;
  DBMS_OUTPUT.PUT_LINE(l_ret);
end;
/






create or replace function GET_ACC_MAX_BAL(p_schema in varchar2,p_obj in varchar2) return varchar2 is
    l_ret varchar2(2000);
    userlevel_1 number(5);
  Result varchar2(2000);
begin
  select sys_context('USER_LEVEL_CTX','LEVEL') into userlevel_1 from dual;
    CASE userlevel_1
    WHEN 1 THEN l_ret:='acc_bal<=1000';
    WHEN 2 THEN l_ret:='acc_bal<=1200';
    WHEN 3 THEN l_ret:='';
    WHEN 4 THEN l_ret:='sys_sys';
    end CASE;
    DBMS_OUTPUT.PUT_LINE(l_ret);
    return l_ret;
  return(Result);
end GET_ACC_MAX_BAL;





SQL> conn ckerk1/ckerk1;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ckerk1

SQL> select * from account;

select * from account

ORA-00942: table or view does not exist

SQL> select * from aaa.account;

      ACCNO ACC_NAME                                               ACC_BAL
----------- -------------------------------------------------- -----------
          1 jolin                                                      900
          4 jack                                                      1000

SQL> conn ckerk2/ckerk2;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ckerk2

SQL> select * from aaa.account;

      ACCNO ACC_NAME                                               ACC_BAL
----------- -------------------------------------------------- -----------
          1 jolin                                                      900
          2 jill                                                      1100
          3 joe                                                       1200
          4 jack                                                      1000
          6 ivan                                                      1200
          7 tony                                                      1100

6 rows selected