Dbms_rls Package implementation of database table row-level security control

Dbms_rls is the implementation of database table row-level security control, this package contains fine-grained access control management interface, which is used to implement VPD (virtual private database). Dbms_rls can only be used in Oracle's Enterprise Edition (Enterpris Edition only). The permissions for Oracle EBS are managed with this. in the database of data security access to solve, there are many ways to solve the problem of permissions, some through the function module to control access rights, and some with the establishment of a view of the method control, such as query statements in the add where statement to control. However , when using the view method, when the table structure or permissions change is not easy to operate, coding workload, the system to adapt to user management system elastic space is small, once the permission logic changes, you may need to modify the permission system, resulting in all the view Must be modified; The WHERE statement solves the problem but is not secure and can be controlled only at the application level, bypassing the application.

Oracle VPD Technology at the database level of security control effectively solves the permissions of data access operations, access control of database objects, can be flexible data isolation, Oracle has implemented the VPD, can be used directly, than in the application layer privilege control is more convenient to implement and easier to maintain. VPD access control principle is also used in the where statement to control, but the where statement is the database in the operation of data automatically splicing, not in the application layer splicing, and by the pre-defined policy to decide whether to splice conditional statements.

Let me use the Dbms_rls package to achieve the following conditions of access control functions:
There are 4 users am145,am147,am148,am149
1, can only query oe.custs table,
2, the filter condition is OE.CUSTS.ACCOUNT_MGR_ID=SUBSTR (user,3,3)

First step: Create user and access authorization
$>sqlplus/as SYSDBA
Create user AM145 identified by AM145;
Grant create session to AM145;
Grant SELECT on Oe.custs to AM145;
Create user AM147 identified by AM147;
Grant create session to AM147;
Grant SELECT on Oe.custs to AM147;
Create user AM148 identified by AM148;
Grant create session to AM148;
Grant SELECT on Oe.custs to AM148;
Create user AM149 identified by AM149;
Grant create session to AM149;
Grant SELECT on Oe.custs to AM149;

Step Two: Create a function to get the predicate of the where filter. The function returns the statement that is stitched after the where condition of the DML statement, but the where statement is not visible when the DML is executed, and the system eradication policy is automatically added, conditional from policy The return value of the function.
VI Fun1.sql
Create or replace FUNCTION oe.policy_function (Object_schema in VARCHAR2, object_name VARCHAR2)
RETURN VARCHAR2 is
Ls_return varchar2 (100);
Ls_username varchar2 (100);
Begin
Ls_username: = user;
If ls_username like ' am% ' then
Ls_return: = ' oe.custs.account_mgr_id=substr (' | | | ' ' | | ls_username| | "' | | ', 3, 3) ';
End If;
return ls_return;
End
/

sql> @fun1. sql

Function created.

Step three: Add a policy
VI Mypolicy1.sql
BEGIN
Dbms_rls.add_policy (Object_schema = ' oe ',
object_name = ' custs ',
Policy_name = ' My_policy1 ',
Function_schema = ' oe ',
policy_function = ' policy_function ',
statement_types = ' SELECT ',
Enable=>true);
END;
/

sql> @myPolicy1. sql

PL/SQL procedure successfully completed.

Fourth Step: Verify Success
Sql> Select Account_mgr_id,count (*) from oe.custs GROUP by account_mgr_id;

account_mgr_id COUNT (*)
-------------- ----------
147 76
149 74
148 58
145 111

Sql> Conn am145/am145
Connected.
Sql> Select COUNT (*) from oe.custs;

COUNT (*)
----------
111

Sql> Conn am147/am147
Connected.
Sql> Select COUNT (*) from oe.custs;

COUNT (*)
----------
76

Sql> Conn am148/am148
Connected.
Sql> Select COUNT (*) from oe.custs;

COUNT (*)
----------
58

Sql> Conn am149/am149
Connected.
Sql> Select COUNT (*) from oe.custs;

COUNT (*)
----------
74

As can be seen from the above, the user am145,am147,am148,am149 access control success.

Note: Delete policy:
Sql> Conn/as SYSDBA
Connected.
Sql> exec dbms_rls. Drop_policy (' oe ', ' custs ', ' my_policy1 ');

PL/SQL procedure successfully completed.

Sql> Conn am145/am145
Connected.
Sql> Select COUNT (*) from oe.custs;

COUNT (*)
----------
319
After you delete a policy, user am145,am147,am148,am149 is able to see all the row data for the OE table.