Dbms_rls Package implementation of row-level security control in database tables

Dbms_rls implements a database table for row-level security control, which includes a fine-grained access control management interface, which is used to implement the VPD (virtual Private database), which is a VPN. Dbms_rls can only be sufficient in Oracle's Enterprise Edition (Enterpris Edition only). The permissions for Oracle EBS are managed with this.

There are a number of ways to resolve permissions issues on data security access to the database. Some are through the function module to control access permissions, and some with the establishment of a view of the method control, such as query statements in addition to the where statement to control. However , when using the view method, when the table structure or permissions change is very not easy to operate, coding workload, the system to adapt to user management system elastic space is small, once the authority logic changes, may need to change the authority system, resulting in all the view must be changed; The where statement solves the problem but is not secure and can only be controlled at the application level. Bypassing the application does not control it.

Oracle VPD Technology's security control at the database level effectively overcomes the permissions of the data access operation, can control the database objects, and can isolate the data flexibly, Oracle has implemented the VPD and can be used directly. It is more convenient than the application layer permission control, and the privilege maintenance is easier.

VPD access Control principle is also the use of the where statement to control, just the where statement is the database in the operation of data when the active splicing, not in the application layer splicing, and by the pre-defined policy to decide whether to join conditional statements.

Let me use the Dbms_rls package to implement an access control function such as the following:
There are examples of the following 4 users am145,am147,am148,am149
1, only can query the 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 run, and the system eradication policy itself joins the condition from the 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: Join the strategy
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 deleting the policy, the user am145,am147,am148,am149 is able to see all the row data of the OE table.

Copyright notice: This article Bo Master original articles, blogs, without consent may not be reproduced.

Dbms_rls Package implementation of row-level security control in database tables