connect scott/tiger@orcl
create table user_info (
username varchar2(30) primary key,
deptno number,
access_salary varchar2(5));
insert into user_info values ('GREGORY', 20, 'false');
commit;
connect system/manager@orcl
create user apps
identified by apps
default tablespace users
temporary tablespace temp;
grant connect to apps;
drop user gregory cascade;
create user gregory
identified by gregory
default tablespace users
temporary tablespace temp;
grant create session to gregory;
create role gregrole;
grant select on scott.emp to gregrole;
grant select on scott.user_info to gregrole;
grant gregrole to gregory
alter user gregory grant connect through apps with roles gregrole;
connect apps[gregory]/apps@orcl
alter session set current_schema = SCOTT;
set pages 1000
set lines 120
select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17/12/80 800 20
7499 ALLEN SALESMAN 7698 20/02/81 1600 300 30
7521 WARD SALESMAN 7698 22/02/81 1250 500 30
7566 JONES MANAGER 7839 02/04/81 2975 20
7654 MARTIN SALESMAN 7698 28/09/81 1250 1400 30
7698 BLAKE MANAGER 7839 01/05/81 2850 30
7782 CLARK MANAGER 7839 09/06/81 2450 10
7788 SCOTT ANALYST 7566 19/04/87 3000 20
7839 KING PRESIDENT 17/11/81 5000 10
7844 TURNER SALESMAN 7698 08/09/81 1500 0 30
7876 ADAMS CLERK 7788 23/05/87 1100 20
7900 JAMES CLERK 7698 03/12/81 950 30
7902 FORD ANALYST 7566 03/12/81 3000 20
7934 MILLER CLERK 7782 23/01/82 1300 10
select user from dual;
USER
----------------
GREGORY
connect system/manager@orcl
grant execute on dbms_rls TO scott;
connect scott/tiger@orcl
CREATE OR REPLACE
function selbydept(p_schema varchar2, p_table varchar2) return varchar2
as
v_deptno number;
begin
select deptno into v_deptno from user_info where username=user;
if p_schema='SCOTT' and p_table='EMP' then
return 'deptno='||to_char(v_deptno);
end if;
return '2=1';
exception when no_data_found then
return '2=1';
end;
/
BEGIN
DBMS_RLS.ADD_POLICY (object_schema => 'scott',
object_name => 'emp',
policy_name => 'selbydept',
function_schema => 'scott',
policy_function => 'selbydept');
END;
/
connect apps[gregory]/apps@orcl
alter session set current_schema = SCOTT;
set pages 1000
set lines 120
select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17/12/80 800 20
7566 JONES MANAGER 7839 02/04/81 2975 20
7788 SCOTT ANALYST 7566 19/04/87 3000 20
7876 ADAMS CLERK 7788 23/05/87 1100 20
7902 FORD ANALYST 7566 03/12/81 3000 20
connect scott/tiger@orcl
select * from emp;
aucune ligne sélectionnée
connect scott/tiger@orcl
CREATE OR REPLACE
function filtersal(p_schema varchar2, p_table varchar2) return varchar2
as
v_access varchar2(5);
begin
select access_salary into v_access from user_info where username=user;
if p_schema='SCOTT' and p_table='EMP' and v_access='true' then
return '1=1';
else
return '2=1';
end if;
return '2=1';
exception when no_data_found then
return '2=1';
end;
/
BEGIN
DBMS_RLS.ADD_POLICY (object_schema => 'scott',
object_name => 'emp',
policy_name => 'filtersal',
function_schema => 'scott',
policy_function => 'filtersal',
sec_relevant_cols=>'sal,comm',
sec_relevant_cols_opt=>dbms_rls.ALL_ROWS
);
END;
/
connect apps[gregory]/apps@orcl
alter session set current_schema = SCOTT;
set pages 1000
set lines 120
select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17/12/80 20
7566 JONES MANAGER 7839 02/04/81 20
7788 SCOTT ANALYST 7566 19/04/87 20
7876 ADAMS CLERK 7788 23/05/87 20
7902 FORD ANALYST 7566 03/12/81 20
connect scott/tiger@orcl
BEGIN
DBMS_RLS.DROP_POLICY (object_schema => 'scott',
object_name => 'emp',
policy_name => 'selbydept');
END;
/
BEGIN
DBMS_RLS.DROP_POLICY (object_schema => 'scott',
object_name => 'emp',
policy_name => 'filtersal');
END;
/
