Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

Oracle Fine-Grained Access Control (FGAC) by example

Oracle Fine-Grained Access Control (FGAC) by example

Table employee:

EMP_ID FIRSTNAME                 LASTNAME                  LO DE

———- ————————- ————————- — —

1 Steve                     Miller                    GA IT

2 Scott                     Tiger                     GA HR

3 Tom                       Lutz                      FL HR

4 Harry                     OCM                       FL IT

Table orders:

ORDER_ID     AMOUNT LO DE

———- ———- — —

1    3454.45 GA IT

2  324893.34 FL IT

3   34545.11 FL HR

4        234.99 GA HR

Lets assume there are two customers (customer 1, customer 2) using the those tables, one wants to limit access by department, the other by location.

The problem with multiple policies is, that Oracle adds them and then  there would be multiple conditions added to the WHERE clause. That’s the reason why you have to work with grouped policies.

First you must create a Driving Application Context. This context sets the active application.

1. Create a driving context:

CREATE CONTEXT drivectx USING ocm.appsec;

CREATE OR REPLACE PACKAGE ocm.appsec AS

v_department VARCHAR2(2);

v_location VARCHAR2(2);

PROCEDURE setcont(policy_group VARCHAR2);

END;

/

CREATE OR REPLACE PACKAGE BODY ocm.appsec AS

PROCEDURE setcont(policy_group VARCHAR2) AS

v_department VARCHAR2(2);

v_location VARCHAR2(2);

BEGIN

DBMS_SESSION.SET_CONTEXT(‘drivectx’,’active_app’,policy_group);

SELECT department INTO v_department

FROM ocm.employee WHERE lastname = SYS_CONTEXT(‘userenv’,’session_user’);

DBMS_SESSION.SET_CONTEXT(‘drivectx’,’department’,v_department);

SELECT location INTO v_location

FROM ocm.employee WHERE lastname = SYS_CONTEXT(‘userenv’,’session_user’);

DBMS_SESSION.SET_CONTEXT(‘drivectx’,’location’,v_location);

END;

END;

/

2. Define Driving Context for the orders table:

EXEC DBMS_RLS.ADD_POLICY_CONTEXT(‘ocm’,’orders’,’drivectx’,’active_app’);

3. Create DEPARTMENT policy group and add policy to the group:

CREATE OR REPLACE FUNCTION ocm.dep_policy(d1 VARCHAR2, d2 VARCHAR2)

RETURN VARCHAR2 AS

BEGIN

RETURN ‘SYS_CONTEXT(”drivectx”,”department”) = department’;

END;

EXEC DBMS_RLS.CREATE_POLICY_GROUP(‘OCM’,’ORDERS’,’DEPARTMENT’);

EXEC DBMS_RLS.ADD_GROUPED_POLICY(‘OCM’,’ORDERS’,’DEPARTMENT’,’DEP_SECURITY’,’OCM’,’DEP_POLICY’);

4. Create LOCATION policy group and add policy to the group:

CREATE OR REPLACE FUNCTION ocp.loc_policy(d1 VARCHAR2, d2 VARCHAR2)

RETURN VARCHAR2 AS

BEGIN

RETURN ‘SYS_CONTEXT(”ID”,”location”) = location’;

END;

EXEC DBMS_RLS.CREATE_POLICY_GROUP(‘OCP’,’ORDERS’,’LOCATION’);

EXEC DBMS_RLS.ADD_GROUPED_POLICY(‘OCP’,’ORDERS’,’LOCATION’,’LOC_SECURITY’,’OCP’,’LOC_POLICY’);

5. Test it

CONNECT ocm/ocm@demo

EXEC ocm.appsec.setcont(‘DEPARTMENT’);   (use the policy group here)

!!! Attention, group is case sensitve !!!

SELECT * FROM orders;

ORDER_ID     AMOUNT LO DE

———- ———- — —

1    3454.45 GA IT

2    324893.34 FL IT

EXEC ocm.appsec.setcont(‘LOCATION’);

SELECT * FROM orders;

ORDER_ID     AMOUNT LO DE

———- ———- — —

2  324893.34 FL IT

3     34545.11 FL HR

This way you can use policies depending on the application.

January 17, 2010 - Posted by | oracle

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: