Use Oracle VPD (Virtual Private Database) to limit the scope of user data acquisition

VPD stands for Virtual Private Database. TechnologyProvides DataFine-grained access control of database information. For more descriptive information about VPD, see OracleOfficial documentation: http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/apdvpoli.htm#i1009600
Use a specific case to experience the features of VPD.

1. initialize the environment
Construct table T, which contains a field X.
SEC @ ora10g> Create Table T (X number );
SEC @ ora10g> insert into T values (1 );
SEC @ ora10g> insert into T values (2 );
SEC @ ora10g> insert into T values (10001 );
SEC @ ora10g> insert into T values (10002 );
SEC @ ora10g> commit;
SEC @ ora10g> select * from T;

X
----------
1
2
10001
10002

2. Problem Scenario Description
Do not query data with column X greater than 10000 in table t.

3. Implementation Method 1: Use the view
This method may be easy to think of and easy to implement. However, the basic table cannot be accessed.
SEC @ ora10g> Create view v_t as select * from t where x <= 10000;

View created.

SEC @ ora10g> select * From v_t;

X
----------
1
2

In this case, if you directly query the basic table t, you can still obtain the data.
SEC @ ora10g> select * from T;

X
----------
1
2
10001
10002

4. Method 2: Use vpd
1) create a function required by VPD. The name here is f_limited_query_t.
Create or replace function f_limited_query_t (s_schema in varchar2,
S_object in varchar2)
Return varchar2
As
Begin
Return 'x <= 10000 ';
End;
/

Function created.

2) Associate functions with tables to be protected
Begin
Dbms_rls.add_policy (object_schema => 'ss ',
Object_name => 'T ',
Policy_name => 'policy _ limited_query_t ',
Function_schema => 'sec ',
Policy_function => 'f _ limited_query_t ');
End;
/

PL/SQL procedure successfully completed.

3) Verify the VPD Effect
In this case, the SEC user directly queries the T table and only obtains the required data. Range.
SEC @ ora10g> select * from T;

X
----------
1
2

Even if you connect to another user, you still cannot obtain the data hidden from the T table of the SEC user.
SEC @ ora10g> conn secooler/secooler
Connected.
Secooler @ ora10g> select * from Sec. t;

X
----------
1
2

Only the Sys user has the right to view all data in the T table of the SEC user.
SEC @ ora10g> Conn/As sysdba
Connected.
Sys @ ora10g> select * from Sec. t;

X
----------
1
2
10001
10002

4) Remove VPD's Data Access Restrictions
We can use dbms_rls.drop_policy to complete this task.
Secooler @ ora10g> conn SEC/sec
Connected.

Begin
Dbms_rls.drop_policy (object_schema => 'ss ',
Object_name => 'T ',
Policy_name => 'policy _ limited_query_t ');
End;
/

PL/SQL procedure successfully completed.

In this case, all data in table t can be obtained.
SEC @ ora10g> select * from T;

X
----------
1
2
10001
10002

5. Summary
What we provide here is the simplest way to use VPD, which may be complicated in actual applications. However, through analysis and careful customization, VPD can be competent.
You are welcome to share the specific application cases of VPD technology.