FGAC Help with Predicate

Oracle 9.2.06 Enterprise Edition on Windows 2003.

We are trying to implement FGAC on a Public Health Disease Reporting Database.   We are limiting access to data by program area, region, and jurisdiction and have created a table epi_access to provide user access control.

ORACLE_USER                                                          NOT NULL VARCHAR2(40)
PROGRAM_AREA                                                       VARCHAR2(40)
JURIS                                                                       VARCHAR2(40)
REGION                                                                    VARCHAR2(40)
REMARK                                                                   VARCHAR2(100).

When we designed it each user only had one row in the table and no more than one program area, region, and jurisdiction.  Now of course, we find out that this isn't really the case and they can have multiple program areas, regions and jurisdictions.  There are only about 30 users.  In the end, I have to create a predicate for each query on a table that establishes the user rights.  I need something that is scalable and maintainable (doesn't everybody?).

If I add multiple program areas to the program area column of the table like:  STD, EPI
I seem to be running into a limitation of the sys_context function:
    I need this fragment " THEN d_predicate:= ' program_area_code in SYS_CONTEXT(' 'epi_clearance'', ''program_area' ')';   "
          to yield:   "     program_area in  ('STD','EPI');      "

Or, If I add multiple rows per user with each value of program area can I use FGAC at all?  There are no examples of this and and I'm wondering if complex security is within the ability of FGAC at all without being unmaintainable.

What is the solution to my problem?

Here is all the current code which uses one row per user in the table and one program area, jurisdiction, and region:

CREATE OR REPLACE TRIGGER check_logon
after logon
ON DATABASE
DECLARE
v_program_area  varchar2(40);
v_juris         varchar2(40);
v_region        varchar2(40);
BEGIN


            SELECT program_area,region,juris INTO v_program_area, v_region, v_juris
                        FROM epi_access
                                    WHERE ORACLE_USER=SYS_CONTEXT ('userenv', 'session_user');

                        IF v_program_area = null then v_program_area := '';
                        ELSIF
                        v_region = null then v_region := '';
                        ELSIF
                        v_juris = null then v_juris := '';
                        END IF;

                        epi_clearance (v_program_area,v_juris ,v_region);

END;
**************************************************************************************************************************************************************

CREATE OR REPLACE PROCEDURE epi_clearance (p_program_area VARCHAR2,p_juris VARCHAR2,p_region VARCHAR2)
IS

v_program_area  VARCHAR2(40):=p_program_area;
v_juris     VARCHAR2(40):=p_juris;
v_region    VARCHAR2(40):=p_region;

BEGIN

      /* SET attributes in 'epi_clearance' context */
         DBMS_SESSION.SET_CONTEXT('epi_clearance', 'program_area', v_program_area);
         DBMS_SESSION.SET_CONTEXT('epi_clearance', 'juris', v_juris);
         DBMS_SESSION.SET_CONTEXT('epi_clearance', 'region', v_region);

END;
**************************************************************************************************************************************************************

CREATE OR REPLACE PACKAGE BODY "EPI_SECURITY" AS

-- limits select statements based on program area, region, jurisdiction */
FUNCTION epi_sec (object_schema VARCHAR2, object_name VARCHAR2) RETURN VARCHAR2

IS
d_predicate VARCHAR2 (2000) := null;

    BEGIN
--Steve Rule
    IF sys_context('epi_clearance','program_area') IS NOT NULL
      AND  sys_context('epi_clearance','juris') IS NULL
      AND  sys_context('epi_clearance','region') IS NULL
     THEN d_predicate:= ' program_area_code in SYS_CONTEXT(''epi_clearance'', ''program_area'')';
--Leah Rule
    ELSIF sys_context('epi_clearance','region') IS NOT NULL
      AND  sys_context('epi_clearance','juris') IS NULL
      AND  sys_context('epi_clearance','program_area') IS NULL
     THEN d_predicate:= 'responsible_region_code= SYS_CONTEXT(''epi_clearance'', ''region'')';
--- Vic Rule (region + juris)
   ELSIF sys_context('epi_clearance','program_area') IS  NULL
      AND  sys_context('epi_clearance','juris') IS NOT NULL
      AND  sys_context('epi_clearance','region') IS NOT NULL
      THEN d_predicate:= 'responsible_region_code = SYS_CONTEXT(''epi_clearance'', ''region'')'|| ' and responsible_juris_code=SYS_CONTEXT(''epi_clearance'', ''juris'')';
---  Leah 2 (program area + region code)
   ELSIF sys_context('epi_clearance','program_area') IS  NOT NULL
      AND  sys_context('epi_clearance','juris') IS NULL
      AND  sys_context('epi_clearance','region') IS NOT NULL
THEN d_predicate:= 'responsible_region_code = SYS_CONTEXT(''epi_clearance'', ''region'')'|| ' and program_area_code=SYS_CONTEXT(''epi_clearance'', ''program_area'')';

---  Vic  2 (program area + region code + juris)
   ELSIF sys_context('epi_clearance','program_area') IS  NOT NULL
      AND  sys_context('epi_clearance','juris') IS NOT NULL
      AND  sys_context('epi_clearance','region') IS NOT NULL
THEN d_predicate:= 'responsible_region_code = SYS_CONTEXT(''epi_clearance'', ''region'')'|| ' and program_area_code=SYS_CONTEXT(''epi_clearance'', ''program_area'')'||' and responsible_juris_code=SYS_CONTEXT(''epi_clearance'', ''juris'')';


      ELSE
--- Kristen (Power User )
         d_predicate := NULL;
END IF;

     RETURN d_predicate;
    END epi_sec;
END epi_security;
**************************************************************************************************************************************************************
/* Set the Policy on the Reports_dataset */

BEGIN
  SYS.DBMS_RLS.ADD_POLICY     (
    object_schema          => 'EPI'
    ,object_name           => 'REPORTS_DATASET'
    ,policy_name           => 'EPI_POLICY'
    ,function_schema       => 'MASTERMIND'
    ,policy_function       => 'EPI_SECURITY_N.EPI_SEC'
    ,statement_types       => 'SELECT'
    ,update_check          => FALSE
    ,static_policy         => FALSE
    ,enable                => TRUE );
END;
/
LVL 1
DonFreemanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DrSQLCommented:
DonFreeman,
    As you may have suspected, setting the second program just overwrites the first one.  There can only be one context.  I would suggest you set an upper limit on the number of program areas and define contect for program_area1, program_area2, etc..  Then, in your "in" you would have:

' program_area_code in (SYS_CONTEXT(' 'epi_clearance'', ''program_area1' '),SYS_CONTEXT(' 'epi_clearance'', ''program_area2' '),<etc.>)';   "

Good luck!
DrSQL

P.S.  I'd fill all of the "program_area" values - just repeat whatever the last one is.  It does mean that you need to know which is the first, second, etc.  But this is just the concept.
DonFreemanAuthor Commented:
Well, its going to be more complicated than that which makes me think using sys_context for anything else except the username is not going to work.  There are 8 program areas now including 'unknown' or other and additional program areas may be added as different areas transition from old systems to the new umbrella system.   In addition to program areas there are 79 jurisdiction codes and 7 region codes.  The seven region codes can also be repeated as jurisdiction codes.   I think the answer is going to be create a code table with however many combinations of access are needed, give it a key value, join it with the logon_user and point the predicate function at my code table instead of using sys_context in order to build the predicate.  Any other ideas?  Anybody else doing something like this?
DrSQLCommented:
DonFreeman,
      Well, you could string all of them together for any particular user.  If, everytime a user gets a new program_area (or similarly for jurisdiction), you would change their context to:

   SYS_CONTEXT('epi_clearance', 'program_area')|| <new area> || '#'   /* use a symbol that CAN'T BE in the program area id code */

Then, in your predicate:


' instr(SYS_CONTEXT(''epi_clearance'', ''program_area''), ''#'' || program_area_code || ''#'') > 0;"

Just be sure to initialize it to '#', so the first program area gets '#ORA#' (I used ORA as an example program area).

Good luck!
DrSQL


Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

DonFreemanAuthor Commented:
That solution is looking a little too complicated.   Where do I store all the extra program area codes?  I can't hard code them in and I can't see setting 100 or so context values

My final predicate is going to be like this for a super_user:  I actually only have 1 user in the system that can see all records.  She will actually get a null predicate to simplify. But other users will have various combinations of these.  My 30 users have about 20 unique combinations of values.   I could just create a predicate for each user and stick it in a table but ultimately we have to provide an app to our help desk for users and security and they'll have to be able to update the predicate information.  We'll just have to create a table with one row for each combination we will be using.

                                             WHERE program_area in ('VPD','STD','IDE','HIV','TB','O','ND')
                                                     AND juris_code in ('400032','4000033' .....70 or so values
                                                         AND region_code ('SC','SE','SW','NE','NW','NC','CO');

I'm pretty sure I'm no longer going to be able to do this using sys_context.  Thanks for your replies.

DrSQLCommented:
DonFreeman,
    You don't HAVE to use sys_context, but you can. There would definitely have to be a table of possible values, but sys_context  is a session-specific value.  You would set the program area to the combination of program_areas for that user in the dbms_session.set_context, and the same for the juris_code and region_code.  The table would have all of the security information you were discussing, so you CAN use it as your security table.  But, that would mean coordinated selects and joins.  If you have a session initialization procedure set the context, based on your secutity table, then you can have:

                                             WHERE instr(program_area, SYS_CONTEXT('epi_clearance', 'program_area') > 0
                                                     AND instr(juris_code, SYS_CONTEXT('epi_clearance', 'juris_code') > 0
                                                         AND instr(region_code, SYS_CONTEXT('epi_clearance', 'region_code') > 0;

With no links to another table.

The security table would be:

Security
---------
username
context
value

with entries like:

username          context               value
FRED                 program_area     VPD
FRED                 program_area     STD
FRED                 juris_code           400032
FRED                 juris_code           400033
FRED                 region_code        SC


And your session init proc would string these together to create a program_area string, a juris_code string, and a region_code string for that user's session.

Good luck!
DrSQL
jwittenmCommented:
one idea is to use multiple contexts.  you are not limited to 'userenv'.  create and use as many as you need.
jwittenmCommented:
for instance, one function can get values from three different contexts and string them into one predicate, like so:

CON_FUNC
----------------------------------------------
region = sys_context('con1','region') and
area = sys_context('con2','area') and
user = sys_context('con3','user')
DonFreemanAuthor Commented:
Ok, Dr SQL .  I'm having a little trouble understanding what you are saying altogether....Questions:

>>>>>>You would set the program area to the combination of program_areas for that user in the dbms_session.set_context, and the same for the juris_code and region_code.  

Ok, (And jwittenm) so instead of setting one context area consisting of three contexts, program area, juris, and region I now create three context areas consisting of the multiple values of each attribute.

>>>>>>The table would have all of the security information you were discussing, so you CAN use it as your security table.  But, that would mean coordinated selects and joins.  

If I don't get it from a table then don't I have to hard code set_context procedures for each user?  I need to store it somewhere.  I only need one join between my username table and the epi_access table.

>>>>>If you have a session initialization procedure set the context, based on your secutity table, then you can have:

                                             WHERE instr(program_area, SYS_CONTEXT('epi_clearance', 'program_area') > 0
                                                     AND instr(juris_code, SYS_CONTEXT('epi_clearance', 'juris_code') > 0
                                                         AND instr(region_code, SYS_CONTEXT('epi_clearance', 'region_code') > 0;

I'm sorry, I'm not understanding what is going on with this code snip.  Is this a predicate?  Or, are you just determining what contexts exist for a user?   Or, are you getting ready to set the context with values from the table.   Sorry I don't understand the "context" <g>.

>>>>>>>>>>>>>>>>
username          context               value
FRED                 program_area     VPD
FRED                 program_area     STD
FRED                 juris_code           400032
FRED                 juris_code           400033
FRED                 region_code        SC

>>>>>>>>>>>>>>>>And your session init proc would string these together to create a program_area string, a juris_code string, and a region_code string for that user's session.

I could use something like this to set 50 or 60 contexts but eventually the entire context for this user would have to be pulled back together by a function and turned into a where clause with two ands.  If I'm not understanding exactly how this would work please expand.  Thanks for your time.



Good luck!
DrSQL
DrSQLCommented:
DonFreeman,
  Ok, we'll go one step at a time.

1) Create a SECURITY table.  For each user, make an entry for each program_area, juris_code, and region_code forwhich they are to have access.  This is the:

username          context               value
FRED                 program_area     VPD
FRED                 program_area     STD
FRED                 juris_code           400032
FRED                 juris_code           400033
FRED                 region_code        SC


2) Create a stored procedure that, for the current user, will set the sys_context for the current session:

create or replace procedure initialize_session_context is
  Type chartable is table of varchar2(40);

  contexts chartable;
  contexts_allowed varchar2(4000) := '#';
begin
   select value bulk collect into contexts from security where context='program_area' and username=user;
   for this in contexts.first..contexts.last loop
      contexts_allowed  := contexts_allowed  || contexts(this) || '#'l
   end loop;
   dbms_session.set_context('epi_clearance', 'program_area',contexts_allowed,user,<id of your application>);

<same thing for juris_code and region_code>
end;

3)  In your application, place a call to initialize_session_context (what you just wrote, above) right after logon.

4) in any queries where you need to ensure the user HAS access to the data, you would need to check the program_area, juris_code, and region code against what initialize_session_context  stored in the user's session for the epi_clearance namespace.  This would be done with the INSTR function, which says (for example), if the current record's prorgam_area is in the list of prorgam_areas for this session (SYS_CONTEXT('epi_clearance', 'program_area') as set by initialize_session_context), then the user is allowed to see it (assuming it is also a juris_code and region that are in THOSE respective attributes of the SYS_CONTEXT).  This was an example of that sort of WHERE:

                                             WHERE instr(program_area, SYS_CONTEXT('epi_clearance', 'program_area') > 0
                                                     AND instr(juris_code, SYS_CONTEXT('epi_clearance', 'juris_code') > 0
                                                         AND instr(region_code, SYS_CONTEXT('epi_clearance', 'region_code') > 0;


  So there's only one namespace (epi_clearance) and only three attributes of that namspace (program_area, juris_code, and region_code).  EACH SESSION WOULD GET A VALUE BASED ON THE SECURITY TABLE.  That is the purpose of initialize_session_context.  This is just a conceptual view, but you could just as easily use roles instead of specific usernames, but you'd need another tablethat told you which users belong to which roles.  That would only change the slect statement that gets the valid values, not the approach.  It's still each session getting a distinct value of all allowed values for each filter variable.

Good luck!
DrSQL

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jwittenmCommented:
You can set up multiple contexts, or better yet just set up one custom context to hold all your values.

Create a context named (ex., test).
on logon, set the values for user, area, and region in the context.  then have the function that is called by your policy string the three values together.  yes, it works fine.  I tested it before I posted the suggestion.  I'll pull togethetr the ddl and post it below.
jwittenmCommented:
create context con1 using con1_pkg;

package con1_pkg as
procedure set_con1;
end;
PACKAGE BODY CON1_pkg
as
procedure set_con1 is
begin
DBMS_SESSION.SET_CONTEXT('con1','deptname','dept1');
DBMS_SESSION.SET_CONTEXT('con1','area','area1');
DBMS_SESSION.SET_CONTEXT('con1','region','region1');
end;
end con1_pkg;

TRIGGER CON123 AFTER LOGON ON DATABASE
BEGIN
sys.con1_pkg.set_con1;
END;

FUNCTION CON_FUNC
(p_schema in varchar2 DEFAULT NULL,
p_object in varchar2 default null)
return varchar2
as
begin
return 'dept1 = sys_context(''con1'',''deptname'')';
END;

DESC FOO
 DEPT1                                                                            VARCHAR2(5)
 DEPT2                                                                            VARCHAR2(5)
 DEPT3                                                                            VARCHAR2(5)

SELECT * FROM FOO;
DEPT1 DEPT2 DEPT3
----- ----- -----
dept1 dept2 dept3
dept2 dept3 dept1
dept3 dept1 dept2

NOTE:  all objects are owned by sys (easy example) except the table which is owned by anyone else.  cant put a policy on sys tables.

begin
dbms_rls.add_policy
(object_schema => '???',
object_name => 'FOO',
policy_name => 'FOO_S',
FUNCTION_SCHEMA => 'SYS',
POLICY_FUNCTION => 'CON_FUNC',
STATEMENT_TYPES => 'SELECT');
END;

test the efficacy from the non-sys schema.  issue 'select * from foo;'
jwittenmCommented:
oh, i tested it before with stringing the three values together, as shown in the first post.  it works quite well.  i just didn't go back and set this one up to do it also.  issue a 'select con_func from dual;' to see your predicate.  issue a 'select sys_context('con1','area') from dual;', a 'select sys_context('con1','user') from dual;','select sys_context('con1','region') from dual;' to see your context values.
jwittenmCommented:
sorry DrSQL, did not see that you had posted pretty much the same thing while I was typing away...
jwittenmCommented:
hmm. or not.  i'm using the fine grained access controls to achieve row level security by creating the policy on the table.  you can create mulitple policies for mulitple actions.  each policy uses the function assigned to it to create the predicate.  the dbms_fgac and dbms_rls packages were designed to do exactly this.
DonFreemanAuthor Commented:
DrSQL - OK starting to see the light.....

Working backward from the target table for each row I will check to see if there is a session context set that matches the values in my program_area, juris_code and region_code.  That means I don't need a complex predicate.... just one that proves an 'exists' relationship.

This is looking a lot more promising than what we were going to do.  

jwittenm -

I have a working VPD now with each user having 1 program area, 1 juris, and 1 region.  Regions and juris are stable. Program areas are not.  We will pick up an unknown number of programs as our highly successful application absorbs its rivals<g>.  In my latest table I have about 90 columns pretty much following your logic for all the variations.  With DrSQL's method I have a small, three column table, easily maintainable.  If we add a program area I only have to add rows, not columns.  The control logic surrounding the predicate is static instead of large and difficult to code.  I think his is a much better more scalable solution.

Thanks for everybodies contributions.
jwittenmCommented:
Hey, that is why there is more than one of us :)  Glad you got your answer(s)!
DrSQLCommented:
jwittenm,
   I guess this means you are opposed to cloning.

Good luck!
DrSLQ
jwittenmCommented:
Heh!  I'm confused by the author's comments about the number of tables and columns, though.  One thought was multiple contexts, but not one I would particulary recommend.  The example I gave added NO tables or columns.  Simply create the logon trigger, policies and contexts.  Oracle handles the rest.
DonFreemanAuthor Commented:
I've tried more than one approach.   The basic system used contexts.  One context area with three contexts (hope that's right) and a simple security table that held the three contexts for each user.  I pulled them from the table with the trigger and logon procedure.   Once the requirements changed I planned on trying to do this with a security table that would contain about 80 columns with all the various combinations of the three contexts I had used before.  I was just going to map the username to the name and values in the table and create a (very ugly) predicate.  It may not have even worked or had performance problems.   Now I'm back to using contexts as per DrSqls suggestion.
DrSQLCommented:
jwittenm,
   I assume that DonFreeman will use the session contexts to create policies (that is the "predicate" being mentioned).  The security table seems easier to manage than keeping all of the security template in contexts (I actually have trouble visualizing how that would work).  This is more like a declarative approach, if a user has permissions to see a particular region (for example), then there's an entry in the security table for that and it will be put in their session's context.  The policy can then use this to filter out regions (again, in this example) that they are not allowed to see.  Then, like you said, Oracle handles the rest.  And, it is possible to create more complex security relationships (hierarchies).  I would just caution DonFreeman to search for strings WITH the delimiters.  So:

instr('#OLD#O#','O') should not return a 2.  That would require:

instr('#OLD#O#', '#' || 'O' || '#')  


so, the policy would (for one of the context values) be:

instr('#' || program_area || '#', SYS_CONTEXT('epi_clearance', 'program_area') > 0

Good luck!
DrSQL
jwittenmCommented:
Ah, I see the point now.  I would have put such information in OID.  Similar to the approach of putting them in a table, but they would be stored as part of the user identification / authorization information in the LDAP directories.  But I understand the need for this type of approach.
DonFreemanAuthor Commented:
Hey Dr. SQL,

We been working on this a couple of days now and are having trouble.....

NEDART-DFREEMAN> CREATE OR REPLACE PROCEDURE epi.EPI_CLEARANCE_Y IS
  2  TYPE CHARTABLE IS TABLE of varchar2(40);
  3  
  4  contexts CHARTABLE;
  5  contexts_allowed VARCHAR2(4000) := '#';
  6  
  7  BEGIN
  8  
  9  /*** PROGRAM AREA ***/
 10  SELECT context_value bulk collect
 11  INTO contexts
 12  FROM mastermind.epi_control
 13  WHERE context_area='PROGRAM_AREA'
 14  AND oracle_user=SYS_CONTEXT('userenv', 'session_user');
 15  
 16  FOR i IN contexts.First..contexts.last
 17  LOOP
 18  contexts_allowed := contexts_allowed || contexts(i )|| '#';
 19  END LOOP;
 20  DBMS_OUTPUT.put_line (contexts_allowed);
 21  DBMS_SESSION.set_context('epi_clearance','program_area', contexts_allowed);
 22  END;
 23  /

Procedure created.

NEDART-DFREEMAN> exec epi.EPI_CLEARANCE_Y
BEGIN epi.EPI_CLEARANCE_Y; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 78
ORA-06512: at "EPI.EPI_CLEARANCE_Y", line 21
ORA-06512: at line 1

This isn't really a privilege problem.  It doesn't matter what privs you have.  The procedure does create the string value but for some reason I can't get it to set the context.  It creates the string #IDE#STD# but I can't seem to get it to accept this as a context in this procedure no matter what we do.  I'm sure its something dumb and simple.  Can you help?
DrSQLCommented:
DonFreeman,
   I was using 8i syntax.  There've been some changes.  You need to use create_context first.  Here's the notes and the link to the docs:


SET_CONTEXT Procedure
This procedure sets or resets the value of a context attribute.

Syntax
DBMS_SESSION.SET_CONTEXT (
   namespace VARCHAR2,
   attribute VARCHAR2,
   value     VARCHAR2,
   username  VARCHAR2,
   client_id VARCHAR2);

Parameters
Table 65-18 SET_CONTEXT Procedure Parameters
Parameter Description
namespace
 Name of the namespace to use for the application context (limited to 30 bytes).
 
attribute
 Name of the attribute to be set (limited to 30 bytes).
 
value
 Value to be set (limited to 4 kilobytes).
 
username
 The username attribute of the application context
 
client_id
 The application-specific identifier of the current database session.
 

Usage Notes
The caller of this function must be in the calling stack of a procedure which has been associated to the context namespace through a CREATE CONTEXT statement. The checking of the calling stack does not cross DBMS boundary.

There is no limit on the number of attributes that can be set in a namespace. An attribute value remains for user session, or until it is reset by the user.

link:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_sessi2.htm#1001153


And, for CREATE CONTEXT:
 syntax:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_53a.htm#SQLRF01202

concepts:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c24privs.htm#CNCPT424

Good luck!
DrSQL
DonFreemanAuthor Commented:
Thanks Doc,

We figured it out about 10 minutes after I hit the send key.  The procedure responsible for setting the context is bound to the context during the context creation.  My guy working on this didn't want to disturb the original code so he simply created another test procedure to set the context that wasn't recognized.  Thats why we kept getting the privilege error.  So, once he dropped and recreated the context again it worked fine.  

create context my_app_ctx using new_procedure;

Thanks again!

DonFreemanAuthor Commented:
This thread is a good reference so I feel like I should update it, so at least if I ever have to do this again I know that the information is here.

We are about finished but found this code snippet that you provided to be incorrect.  The instr(string,substr) is backwards.

instr(program_area, SYS_CONTEXT('epi_clearance', 'program_area') > 0
                                                     AND instr(juris_code, SYS_CONTEXT('epi_clearance', 'juris_code') > 0
                                                         AND instr(region_code, SYS_CONTEXT('epi_clearance', 'region_code') > 0;

Here is the complete function.
CREATE OR REPLACE PACKAGE BODY EPI.EPI_SECURITY AS
FUNCTION EPI_SEC(object_schema VARCHAR2, object_name VARCHAR2)
RETURN VARCHAR2

IS

d_predicate VARCHAR2 (2000) := null;


BEGIN
d_predicate := ' instr(SYS_CONTEXT(''epi_ctx'', ''program_area''), PROGRAM_AREA_CODE ) > 0
                                  AND instr(SYS_CONTEXT(''epi_ctx'', ''juris''), RESPONSIBLE_JURIS_CODE ) > 0
                                           AND instr(SYS_CONTEXT(''epi_ctx'', ''region''), RESPONSIBLE_REGION_CODE ) > 0 ';

COMMIT;
     RETURN d_predicate;
  END epi_sec;
END epi_security;
/

Again, I appreciate your efforts.  They helped a couple of not-so-good coders to look like heroes and successfully hold up our end of the project.
DrSQLCommented:
DonFreeman,
  Thanks for posting this, it really helps those who use the searches and view solutions later.

  And it's pretty amusing that you refer to yourselves as "not-so-good" when I can't even get an instr syntax correct.

Good luck!
DrSQL
(all sizzle, no steak sometimes)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.