In row-level access policies, the policy is applied regardless of the selected columns.
However, when implementing restrictions at the column level, the policy is not enforced until the columns protected by the policy are included in the DML statement. As we will see, this option can also be used to mask column data when desired. When column masks are also enforced by the policy, the records that don't conform to the defined criteria have their column values hidden by the policy and displayed as nulls instead.
In this recipe we will create two users; DOCONNEL
and JWHALEN
. We will declare a policy named EMPLOYEES_SALCOMM_PLC
that will protect the salary
and commision_pct
columns from the EMPLOYEES_TEST_VPD
table. Then we will redefine the VPD policy to apply column masking.
system
user create users DOCONNEL
and JWHALEN
:SQL> create user DOCONNEL identified by DOCONNEL; User created. SQL> create user JWHALEN identified by JWHALEN; User created. SQL> grant create session to DOCONNL,JWHALEN; Grant succeeded.
HR
issues the grant select
privilege on the employee
table as follows:SQL> grant select on hr.employees_test_vpd to DOCONNEL,JWHALEN; Grant succeeded. SQL>
system
user and create the salcomm_plc_func
policy function:SQL> CREATE OR REPLACE FUNCTION salcomm_plc_func ( schema_v IN VARCHAR2, tbl_v VARCHAR2) RETURN VARCHAR2 IS ret_val VARCHAR2(200); BEGIN ret_val := 'email = SYS_CONTEXT(''USERENV'', ''SESSION_USER'')'; RETURN ret_val; END; SQL> / Function created. SQL>
EMPLOYEES_SALCOMM_PLC
as follows:1 SQL> BEGIN DBMS_RLS.add_policy (object_schema => 'HR', object_name => 'EMPLOYEES_TEST_VPD', policy_name => 'employees_salcomm_plc', policy_function => 'salcomm_plc_func', statement_types => 'SELECT', sec_relevant_cols => 'SALARY,COMMISSION_PCT'), END; PL/SQL procedure successfully completed. SQL>
DOCONNEL
and issue a SELECT
statement, without including the protected columns salary
and commission_pct
, as follows:SQL> select first_name,last_name from hr.employees_test_vpd; FIRST_NAME LAST_NAME -------------------- ------------------------- Ellen Abel Sundar Ande Mozhe Atkinson …………………………………………………………………………. 107 rows selected. SQL>
We can see that in this case the columns salary
or commission_pct
are not included in the SELECT
statement, so the policy is not enforced.
SELECT
statement that includes the salary
column as follows:SQL> select first_name,last_name,salary from hr.employees; FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Donald OConnell 4100 SQL>
SELECT
statement that includes the commission_pct
column as follows:SQL> select first_name,last_name,commission_pct from hr.employees; FIRST_NAME LAST_NAME COMMISSION_PCT -------------------- ------------------------- -------------- Donald OConnell
salary
and also the commission_pct
column as follows:SQL> select first_name,last_name,salary,commission_pct from hr.employees; FIRST_NAME LAST_NAME SALARY COMMISSION_PCT -------------------- ------------------------- ---------- -------------- Donald OConnell 4100 SQL>
JHWALEN
and repeat some of the statements performed for the DOCONNEL
user. Connect as the user security_adm
and disable the policy employees_salcomm_plc
:SQL> SQL> conn security_adm Enter password: Connected. SQL> BEGIN 2 dbms_rls.enable_policy(policy_name=>'employees_salcomm_plc',object_name=>'employees_test_vpd', object_schema=>'HR',enable=>FALSE); 3 END; 4 / PL/SQL procedure successfully completed. SQL> PL/SQL procedure successfully completed. SQL>
employee_salcomm_plc_mask
using the data masking option:SQL> SQL> begin 2 DBMS_RLS.add_policy (object_schema => 'HR', object_name => 'EMPLOYEES_TEST_VPD', policy_name => 'employees_salcomm_plc_mask', policy_function => 'salco mm_plc_func', statement_types => 'SELECT', sec_relevant_cols => 'SALARY,COMMISSION_PCT', sec_relevant_cols_opt => DBMS_RLS.all_rows ); 3 end; 4 / PL/SQL procedure successfully completed.
DOCONNEL
and issue the following statement:SQL> select first_name,last_name,salary,commission_pct fromhr.employees; FIRST_NAME LAST_NAME SALARY COMMISSION_PCT ------------------- ------------------------- ---------- -------------- -Donald OConnell 3100 Douglas Grant …………………………………………………………………………………………………………………………………………………………………….. 107 rows selected. SQL>
The salary
and commission_pct
has values just for the user DOCONNEL
; for other users' salary
and commission_pct
are displayed as null.
18.221.179.220