利用Oracle VPD實現行級安全保護（二）

http://boylook.itpub.net/post/43144/520545

通過VPD策略的設定，David只能夠查看部門60和80的記錄。

SQL> conn david
Enter password:
Connected.
SQL> select employee_id,salary,first_name from hr.employees;

EMPLOYEE_ID SALARY FIRST_NAME
----------- ---------- --------------------
103 9000 Alexander
104 6000 Bruce
105 4800 David
106 4800 Valli
107 4200 Diana
145 14000 John
146 13500 Karen
147 12000 Alberto
148 11000 Gerald
149 10500 Eleni
150 10000 Peter

EMPLOYEE_ID SALARY FIRST_NAME
----------- ---------- --------------------
151 9500 David
152 9000 Peter
153 8000 Christopher
154 7500 Nanette
155 7000 Oliver
156 10000 Janette
157 9500 Patrick
158 9000 Allan
159 8000 Lindsey
160 7500 Louise
161 7000 Sarath

EMPLOYEE_ID SALARY FIRST_NAME
----------- ---------- --------------------
162 10500 Clara
163 9500 Danielle
164 7200 Mattea
165 6800 David
166 6400 Sundar
167 6200 Amit
168 11500 Lisa
169 10000 Harrison
170 9600 Tayler
171 7400 William
172 7300 Elizabeth

EMPLOYEE_ID SALARY FIRST_NAME
----------- ---------- --------------------
173 6100 Sundita
174 11000 Ellen
175 8800 Alyssa
176 8600 Jonathon
177 8400 Jack
179 6200 Charles

39 rows selected.

其實，有時候不必全部隱藏，可能只要不能看到其他部門的薪水就OK了。
那麼，VPD在這種需求下怎麼辦呢？
VPD在Oracle10G中引入了一個叫做敏感列VPD的新特性，這個功能可以在某個列被訪問時啟用安全保護原則。
下面看例子：

SQL> conn hr
Enter password:
Connected.

--刪除掉原來的策略
SQL> declare
2 begin
3 dbms_rls.drop_policy(
4 object_schema =>'HR',
5 object_name =>'EMPLOYEES',
6 policy_name =>'HIDE_EMP');
7 end;
8 /

PL/SQL procedure successfully completed.

--然後修改：vi test_add_policy.sql
declare
begin
dbms_rls.add_policy(
object_schema =>'HR',
object_name =>'EMPLOYEES',
policy_name =>'HIDE_EMP',
function_schema =>'HR',
policy_function =>'TEST_VPD',
statement_types =>'SELECT',
sec_relevant_cols =>'SALARY',
SEC_RELEVANT_COLS_OPT =>dbms_rls.all_rows
);
end;

SQL> @test_add_policy

PL/SQL procedure successfully completed.

讓我們來看看效果：
SQL> conn david
Enter password:
Connected.
SQL> select employee_id,department_id,salary from hr.employees;

EMPLOYEE_ID DEPARTMENT_ID SALARY
----------- ------------- ----------
198 50
199 50
200 10
201 20
202 20
203 40
204 70
205 110
206 110
100 90
101 90

EMPLOYEE_ID DEPARTMENT_ID SALARY
----------- ------------- ----------
102 90
103 60 9000
104 60 6000
105 60 4800
106 60 4800
107 60 4200
108 100
109 100
110 100
111 100
112 100

EMPLOYEE_ID DEPARTMENT_ID SALARY
----------- ------------- ----------
113 100
114 30
115 30
116 30
117 30
118 30
119 30
120 50
121 50
122 50
123 50

EMPLOYEE_ID DEPARTMENT_ID SALARY
----------- ------------- ----------
124 50
125 50
126 50
127 50
128 50
129 50
130 50
131 50
132 50
133 50
134 50

EMPLOYEE_ID DEPARTMENT_ID SALARY
----------- ------------- ----------
135 50
136 50
137 50
138 50
139 50
140 50
141 50
142 50
143 50
144 50
145 80 14000

EMPLOYEE_ID DEPARTMENT_ID SALARY
----------- ------------- ----------
146 80 13500
147 80 12000
148 80 11000
149 80 10500
150 80 10000
151 80 9500
152 80 9000
153 80 8000
154 80 7500
155 80 7000
156 80 10000

EMPLOYEE_ID DEPARTMENT_ID SALARY
----------- ------------- ----------
157 80 9500
158 80 9000
159 80 8000
160 80 7500
161 80 7000
162 80 10500
163 80 9500
164 80 7200
165 80 6800
166 80 6400
167 80 6200

EMPLOYEE_ID DEPARTMENT_ID SALARY
----------- ------------- ----------
168 80 11500
169 80 10000
170 80 9600
171 80 7400
172 80 7300
173 80 6100
174 80 11000
175 80 8800
176 80 8600
177 80 8400
178

EMPLOYEE_ID DEPARTMENT_ID SALARY
----------- ------------- ----------
179 80 6200
180 50
181 50
182 50
183 50
184 50
185 50
186 50
187 50
188 50
189 50

EMPLOYEE_ID DEPARTMENT_ID SALARY
----------- ------------- ----------
190 50
191 50
192 50
193 50
194 50
195 50
196 50
197 50

107 rows selected.

本文出自 “MIKE老畢的部落格” 部落格，請務必保留此出處http://boylook.blog.51cto.com/7934327/1298620