上篇说了行的隐藏，那如何隐藏列上的敏感记录，类似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.