Implementing column-level access policies

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.

Getting ready

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.

How to do it...

  1. As the 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.
  2. As the user 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>
  3. Connect as the 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>
  4. Create the column-level policy 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>
  5. Connect as user 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.

  6. Now issue a 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>
  7. Next issue a 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
  8. And finally include the 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>
  9. Connect as the user 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>
  10. Create a new policy named 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.
  11. Connect as the user 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.

How it works...

The policy will not trigger unless the columns declared in sec_relevant_cols are not used in statements. Column masking works only with SELECT statements.

Additional information about secured columns can be found in the DBA_SEC_RELEVANT_COLS dictionary view.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.145.173.199