Introduction to oracleFGAC (fine-grained access control)

In ORACLE, recursive is sometimes called a Virtual Private Database (VPD) or a fine-grained access control (FGAC ).

With this feature, we can define a security policy (and specify the type of operation on the table) to restrict data that can be viewed or modified by users.

Most of this function is implemented through the built-in package DBMS_RLS. An example is provided below. For details, refer to the official documentation:

Certificate -----------------------------------------------------------------------------------------------------------------------------------

Environment: 11.2.0.1.0-64bit

Effect: Create a customer table and order table under SCOTT: MERs,Orders_tabTo enable users in the customer table to log on to only view their orders;

1. Create a user account and an example table:

Dba User Logon:

GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE TRIGGER, ADMINISTER DATABASE TRIGGER TO sysadmin_vpd IDENTIFIED BY password;GRANT EXECUTE ON DBMS_SESSION TO sysadmin_vpd;GRANT EXECUTE ON DBMS_RLS TO sysadmin_vpd;

GRANT CREATE SESSION TO tbrooke IDENTIFIED BY password;GRANT CREATE SESSION TO owoods IDENTIFIED BY password;

CONNECT scottEnter password: passwordCREATE TABLE customers ( cust_no    NUMBER(4),  cust_name  VARCHAR2(20));INSERT INTO customers VALUES (1234, 'TBROOKE');INSERT INTO customers VALUES (5678, 'OWOODS');

GRANT SELECT ON customers TO sysadmin_vpd; 

CREATE TABLE orders_tab (  cust_no  NUMBER(4),  order_no NUMBER(4));INSERT INTO orders_tab VALUES (1234, 9876);INSERT INTO orders_tab VALUES (5678, 5432);INSERT INTO orders_tab VALUES (5678, 4592);

GRANT SELECT ON orders_tab TO tbrooke;GRANT SELECT ON orders_tab TO owoods;

2. Create a session-based application context:

CONNECT sysadmin_vpdEnter password: password

CREATE OR REPLACE CONTEXT orders_ctx USING orders_ctx_pkg;

CREATE OR REPLACE PACKAGE orders_ctx_pkg IS   PROCEDURE set_custnum; END;/CREATE OR REPLACE PACKAGE BODY orders_ctx_pkg IS  PROCEDURE set_custnum  AS    custnum NUMBER;  BEGIN     SELECT cust_no INTO custnum FROM scott.customers        WHERE cust_name = SYS_CONTEXT('USERENV', 'SESSION_USER');     DBMS_SESSION.SET_CONTEXT('orders_ctx', 'cust_no', custnum);  EXCEPTION   WHEN NO_DATA_FOUND THEN NULL;  END set_custnum;END;/

3. Create a login trigger to call the application context package:

CREATE TRIGGER set_custno_ctx_trig AFTER LOGON ON DATABASE BEGIN  sysadmin_vpd.orders_ctx_pkg.set_custnum; END;/

4. Create a policy function ):

CREATE OR REPLACE FUNCTION get_user_orders(  schema_p   IN VARCHAR2,  table_p    IN VARCHAR2) RETURN VARCHAR2 AS  orders_pred VARCHAR2 (400); BEGIN  orders_pred := 'cust_no = SYS_CONTEXT(''orders_ctx'', ''cust_no'')';  RETURN orders_pred;END;/

5. Create a security policy ):

BEGIN DBMS_RLS.ADD_POLICY (  object_schema    => 'scott',   object_name      => 'orders_tab',   policy_name      => 'orders_policy',   function_schema  => 'sysadmin_vpd',  policy_function  => 'get_user_orders',   statement_types  => 'select');END;/

6. test the security policy:

CONNECT tbrookeEnter password: password

SELECT * FROM scott.orders_tab;

The following output shoshould appear:

   CUST_NO    ORDER_NO----------  ----------      1234        9876

CONNECT owoodsEnter password: passwordsSELECT * FROM scott.orders_tab

The following output shoshould appear:

   CUST_NO    ORDER_NO----------  ----------      5678        5432      5678        4592

7. Remove the sample component:

Connect as user OE and remove the orders_tab and MERs tables.

CONNNECT SCOTTEnter password: passwordDROP TABLE orders_tab;DROP TABLE customers; 

Connect as user SYS, connecting with as sysdba.

CONNECT sys/as sysdbaEnter password: password

Run the following statements to drop the components for this tutorial:

DROP CONTEXT orders_ctx;DROP USER sysadmin_vpd CASCADE;DROP USER tbrooke;DROP USER owoods;

------------------------------

Dylan Presents.