ANBOB™

上篇说了行的隐藏，那如何隐藏列上的敏感记录，类似VIEW
CREATE VIEW XX AS
SELECT XX,XX,XX FROM TXX;

列上的隐藏分两种，指定的敏感列，1，查询到敏感列时，整行记录不显示，2，查询到敏感列时，行记录显示，列显示为空

下面看我的两种演示

hr@NCME>select * from emp;

        ID NAME                        SAL TEL
---------- -------------------- ---------- -----------
         1 bkdcici                 4656.82 138
         2 rgeankq                 4394.34 138
         3 zkbstla                 3139.03 138
         4 oxtzdbq                 3590.45 138
         5 yctslvq                 3909.43 138
         6 htbhqff                 4234.96 131
         7 beofrsy                 4237.33 131
         8 ixbmdyd                 4265.09 131
         9 ixtzghd                 3533.51 131
        10 sysdeptman                10000 131
        11 hrman                     10000 138

11 rows selected.

hr@NCME>create or replace function fun_vpd_man(powner varchar2,pname varchar2)
  return varchar2 is
  v_wherecase varchar2(2000);
  begin
  v_wherecase:='substr(name,-3)!=''man''';
  return v_wherecase;
  end;
/

Function created.

hr@NCME>begin
  2  dbms_rls.add_policy('HR','EMP','POL_VPD_MAN',
  3                     'HR','FUN_VPD_MAN',sec_relevant_cols=>'SAL');
  4  END;
  5  /

PL/SQL procedure successfully completed.


hr@NCME>SELECT ID,NAME FROM EMP;

        ID NAME
---------- --------------------
         1 bkdcici
         2 rgeankq
         3 zkbstla
         4 oxtzdbq
         5 yctslvq
         6 htbhqff
         7 beofrsy
         8 ixbmdyd
         9 ixtzghd
        10 sysdeptman
        11 hrman

11 rows selected.

hr@NCME>SELECT * FROM EMP;

        ID NAME                        SAL TEL
---------- -------------------- ---------- -----------
         1 bkdcici                 4656.82 138
         2 rgeankq                 4394.34 138
         3 zkbstla                 3139.03 138
         4 oxtzdbq                 3590.45 138
         5 yctslvq                 3909.43 138
         6 htbhqff                 4234.96 131
         7 beofrsy                 4237.33 131
         8 ixbmdyd                 4265.09 131
         9 ixtzghd                 3533.51 131

9 rows selected.


sec_relevant_cols
	

Enables column-level Virtual Private Database (VPD), which enforces security policies when a column containing sensitive information is referenced in a query. Applies to tables and views, but not to synonyms. Specify a list of comma- or space-separated valid column names of the policy-protected object. The policy is enforced only if a specified column is referenced (or, for an abstract datatype column, its attributes are referenced) in the user SQL statement or its underlying view definition. Default is all the user-defined columns for the object.


hr@NCME>EXEC DBMS_RLS.DROP_POLICY('HR','EMP','POL_VPD_MAN');

PL/SQL procedure successfully completed.

hr@NCME>BEGIN
  2    DBMS_RLS.ADD_POLICY ('HR','EMP','POL_VPD_MAN',
                     'HR','FUN_VPD_MAN',sec_relevant_cols=>'SAL',
                      sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);
  5  END;
  6  /

PL/SQL procedure successfully completed.

hr@NCME>SELECT * FROM EMP;

        ID NAME                        SAL TEL
---------- -------------------- ---------- -----------
         1 bkdcici                 4656.82 138
         2 rgeankq                 4394.34 138
         3 zkbstla                 3139.03 138
         4 oxtzdbq                 3590.45 138
         5 yctslvq                 3909.43 138
         6 htbhqff                 4234.96 131
         7 beofrsy                 4237.33 131
         8 ixbmdyd                 4265.09 131
         9 ixtzghd                 3533.51 131
        10 sysdeptman                      131
        11 hrman                           138

11 rows selected.


sec_relevant_cols_opt	
Use with sec_relevant_cols to display all rows for column-level VPD filtered queries (SELECT only), but where sensitive columns appear as NULL. Default is set to NULL, which allows the filtering defined with sec_relevant_cols to take effect. Set to dbms_rls.ALL_ROWS to display all rows, but with sensitive column values, which are filtered by sec_relevant_cols, displayed as NULL. See "Usage Notes" for restrictions and additional information about this option.

打赏

微信扫一扫，打赏作者吧～