OCM Tip of the week – Implement fine-grained access control
Implement fine-grained access control
Database Security Guide -> 7 Using Oracle Virtual Private Database to Control Data Access
Database PL/SQL Packages and Types Reference -> DBMS_RLS
Understanding Fine-Grained Access Control (DBMS_RLS) on INSERT (Doc ID 99250.1)
Master Note For Oracle Virtual Private Database ( VPD / FGAC / RLS ) (Doc ID 1352641.1)
Oracle8i Fine Grained Access Control – Working Examples (Doc ID 67977.1)
Evolution of Fine Grain Access Control FGAC Feature From 8i to 10g (Doc ID 281829.1)
Virtual Private Database (VPD) is used to prevent a user from access to unrelated data. VPD uses DBMS_RLS package that contains fine grained access control interface to limit specific data to specific users by applying the security policy directly to the database objects and modifies the SQL statement automatically before accessing the data by adding a WHERE condition that is returned from the security policy function.
Let’s create a simple VPD and see how it works. Refer to the Chapter 7 under “Database Security Guide” documentation to get syntax of main commands. In the following example, we create a table (TBL_VPD_TEST) based on ALL_OBJECTS view under the new user USR_VPD, grant SELECT access to the second user USR_VPD_2 and create a policy to prevent the second user to query some rows.
To implement this scenario, let’s create users and a table:
SQL> CREATE USER USR_VPD IDENTIFIED BY usr_vpd; User created. SQL> CREATE USER usr_vpd_2 IDENTIFIED BY usr_vpd_2; User created. SQL> GRANT CONNECT, RESOURCE TO usr_vpd, usr_vpd_2; Grant succeeded. SQL> CONN usr_vpd/usr_vpd; Connected. SQL> CREATE TABLE tbl_vpd_test AS SELECT * FROM all_objects; Table created. SQL> GRANT SELECT ON tbl_vpd_test TO usr_vpd_2; Grant succeeded. SQL>
Now we need to have a function which generates a dynamic WHERE clause. This function must take a schema and table name as an input, must provide a return value for the WHERE clause and must generate a valid WHERE clause. The following error will appear if the WHERE clause is invalid:
SQL> ERROR at line 1: ORA-28113: policy predicate has error
To access user’s session information to build up a WHERE clause, the next topic comprehensively describes the context is used. Create a function as follows:
SQL> CREATE OR REPLACE FUNCTION hide_all_except_100 (v_schema IN VARCHAR2, v_objname IN VARCHAR2) RETURN VARCHAR2 AS where_clause VARCHAR2 (100); BEGIN IF USER = ‘USR_VPD_2’ THEN where_clause := ‘object_id=100’; ELSE where_clause := ‘’; END IF; RETURN (where_clause); END hide_all_except_100; / Function created. SQL>
Note: Please note that the preferred method to pass a session information to the function used in the policy is to use an application context. Detailed information is provided about an application context in the next chapter.
Now DBMS_RLS.ADD_POLICY procedure to add fine-grained access control policy to the table.
The syntax of ADD_POLICY command is as follows:
object_schema IN VARCHAR2 NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2,
function_schema IN VARCHAR2 NULL,
policy_function IN VARCHAR2,
statement_types IN VARCHAR2 NULL,
update_check IN BOOLEAN FALSE,
enable IN BOOLEAN TRUE,
static_policy IN BOOLEAN FALSE,
policy_type IN BINARY_INTEGER NULL,
long_predicate IN BOOLEAN FALSE,
sec_relevant_cols IN VARCHAR2,
sec_relevant_cols_opt IN BINARY_INTEGER NULL);
Detailed information about all parameters of ADD_POLICY procedure is available in the “Database PL/SQL Packages and Types Reference” chapter “110 DBMS_RLS”. Now connect with SYS user and create a new policy that prevents user USR_VPD_2 from seeing all rows except where id=100 as follows:
DBMS_RLS.add_policy (object_schema => ‘USR_VPD’,
object_name => ‘TBL_VPD_TEST’,
policy_name => ‘MY_VPD_POLICY_01’,
function_schema => ‘USR_VPD’,
policy_function => ‘HIDE_ALL_EXCEPT_100’,
statement_types => ‘SELECT’);
PL/SQL procedure successfully completed.
You can also specify rest DML command for the statement_types parameter like INSERT, UPDATE and DELETE.
Now query table with both USR_VPD and USR_VPD_2 users:
SQL> SHOW USER USER is “USR_VPD” SQL> SELECT COUNT(1) FROM usr_vpd.tbl_vpd_test; COUNT(1) ---------- 55713 SQL> CONN usr_vpd_2/usr_vpd_2 Connected. SQL> SELECT COUNT(1) FROM usr_vpd.tbl_vpd_test; COUNT(1) ---------- 1
Exam tip: Remember, if you successfully create a policy, but it doesn’t work for certain reason and you need to recreate it, use DROP_POLICY procedure to drop the policy from the table. It is easy to use, simply provide the schema name, table name and policy name as follows:
SQL> EXEC DBMS_RLS.DROP_POLICY(‘usr_vpd’,’tbl_vpd_test’,’my_vpd_policy_01’); PL/SQL procedure successfully completed. SQL>
During an exam you might be asked to create a policy that must prevent a user from making an update that violates WHERE condition of the policy. In this special case, update_check parameter must be set to TRUE. To understand how it works, check the following example. The policy we have created earlier doesn’t contain update_check parameter and the default value of this parameter is FALSE.
Let’s connect with the second user, query the table and try to update it:
SQL> SELECT object_id FROM usr_vpd.tbl_vpd_test; OBJECT_ID ---------- 100 SQL> UPDATE usr_vpd.tbl_vpd_test SET object_id=500; 1 row updated. SQL>
Update completed successfully. If you query the table, you will get no rows:
SQL> SELECT object_id FROM usr_vpd.tbl_vpd_test; no rows selected SQL> ROLLBACK Rollback complete. SQL> CONN / AS SYSDBA Connected. SQL>
Make sure the second user has a grant privilege on the table:
SQL> GRANT UPDATE ON usr_vpd.tbl_vpd_test TO usr_vpd_2; Grant succeeded. SQL>
Next, drop the policy and recreate it:
SQL> EXEC DBMS_RLS.DROP_POLICY(‘usr_vpd’,’tbl_vpd_test’,’my_vpd_policy_01’); PL/SQL procedure successfully completed. SQL> BEGIN DBMS_RLS.add_policy (object_schema => ‘USR_VPD’, object_name => ‘TBL_VPD_TEST’, policy_name => ‘MY_VPD_POLICY_01’, function_schema => ‘USR_VPD’, policy_function => ‘HIDE_ALL_EXCEPT_100’, update_check => TRUE, statement_types => ‘SELECT, UPDATE’); END; / PL/SQL procedure successfully completed. SQL>
Now run update command of the row that is the only row you can view:
SQL> SELECT object_id FROM usr_vpd.tbl_vpd_test; OBJECT_ID ---------- 100 SQL> UPDATE usr_vpd.tbl_vpd_test SET object_id=500; UPDATE usr_vpd.tbl_vpd_test SET object_id=500 * ERROR at line 1: ORA-28115: policy with check option violation SQL>