This explains the structure of the XML policies, and how the correct SQL scripts can be generated from the policy descriptors using the pattern generator.

1. Writing the XML Profile
2. Generating the SQL Scripts
3. Applying the scripts to the database
4. Testing
5. Appendix

Assumptions

Thorough understanding and setup of the Pattern Generator for Finder and Viewer as defined in How to use the Pattern Generator.

First you need to identify the security policies and the tables to which the security will be applied to.

• Identify the Predicate (where clause) to be applied to various tables which falls under data security and the Roles under which the security policy will applied (if any)
• Create the XML file under \source\java\resources\tools\patternengine\data_security_policy_1_1. based on the data example below. (Please refer to the Appendix for understanding the XML structure.

<?xml version="1.0" encoding="UTF-8"?>
<Domain>
   <PatternTemplate>patterns/library/data_security_policy_1_1/SecurityDomainPattern.xml</PatternTemplate>
   <MappingPackage>sqlscripts</MappingPackage>
   <SchemaName>jaffatest</SchemaName>
   <SqlPrefix>jaffa</SqlPrefix>
   <BasePackage>sec_itemfilter</BasePackage>
   <Roles>
       <Role>Manager</Role>
   </Roles>
   <Description>Test Policy for Item</Description>
   <Tables>
       <Table>
           <PolicyName>clerk_item_sec</PolicyName>
           <SourceTable>ZZ_JUT_ITEM</SourceTable>
           <Custom>
               <Predicate>sc like ''JAFFA%''</Predicate>
           </Custom>
       </Table>
       <Table>
           <PolicyName>clerk_part_sec</PolicyName>
           <SourceTable>ZZ_JUT_PART</SourceTable>
           <Indirect>
               <SourceField>part</SourceField>
               <ForeignTable>sc like ''JAFFA%''</ForeignTable>
               <ForeignField>sc like ''JAFFA%''</ForeignField>
           </Indirect>
       </Table>
   </Tables>
</Domain>

1. For this example we have the xml definition under unittest folder. You can have it under any directory structure you want.
2. First make sure that you have the XML Policy file from which you need to generate the SQL script is under \source\java\unittest\java\resources\tools\patternengine\data_security_policy_1_1.
3. Run the target data_security_policy_1_1 from the script located at \unittest\java\resources\tools\patternengine\executePatternGenerator.xml

4. This will generate (for each XML Policy file) three files: - sec_itemfilter_CreatePredicate.sql (under \sql\admin\datasecurity\clerk) - sec_itemfilter_ApplyPredicate.sql (under \sql\system\datasecurity\clerk) - sec_itemfilter_DropPredicate.sql (under \sql\system\datasecurity\clerk)

   Where sec_itemfilter prefix is derived from the <BasePackage> element in the policy file

   itemfilter_CreatePredicate.sql

   CREATE OR REPLACE PACKAGE sec_itemfilter AS FUNCTION clerk_item_sec (D1 VARCHAR2, D2 VARCHAR2) RETURN VARCHAR2; FUNCTION clerk_part_sec (D1 VARCHAR2, D2 VARCHAR2) RETURN VARCHAR2; END; / CREATE OR REPLACE PACKAGE BODY sec_itemfilter AS FUNCTION clerk_item_sec (D1 VARCHAR2, D2 VARCHAR2) RETURN VARCHAR2 IS D_predicate VARCHAR2(2000); BEGIN IF sys_context('jaffa_sec_ctx', 'userid') IS NULL THEN D_predicate := ' ''1'' = ''2'' '; ELSIF sys_context('jaffa_sec_ctx', 'is_CLERK') = 'TRUE' THEN D_predicate := 'sc like ''Jaffa%'''; ELSE D_predicate := ''; END IF; RETURN D_predicate; END; FUNCTION clerk_part_sec (D1 VARCHAR2, D2 VARCHAR2) RETURN VARCHAR2 IS D_predicate VARCHAR2(2000); BEGIN IF sys_context('jaffa_sec_ctx', 'userid') IS NULL THEN D_predicate := ' ''1'' = ''2'' '; ELSIF sys_context('jaffa_sec_ctx', 'is_CLERK') = 'TRUE' THEN D_predicate := 'part IN (select part from ZZ_JUT_ITEM)'; ELSE D_predicate := ''; END IF; RETURN D_predicate; END; END sec_itemfilter; /

   sec_itemfilter_ApplyPredicate.sql
   EXECUTE DBMS_RLS.ADD_POLICY ('jaffatest','ZZ_JUT_ITEM','clerk_item_sec','jaffatest','sec_itemfilter.clerk_item_sec','select'); EXECUTE DBMS_RLS.ADD_POLICY ('jaffatest','ZZ_JUT_PART','clerk_part_sec','jaffatest','sec_itemfilter.clerk_part_sec','select');

   sec_itemfilter_DropPredicate.sql
   EXECUTE DBMS_RLS.DROP_POLICY ('jaffatest','ZZ_JUT_ITEM','clerk_item_sec'); EXECUTE DBMS_RLS.DROP_POLICY ('jaffatest','ZZ_JUT_PART','clerk_part_sec');

Once the sql scripts have been generated , you need to apply those scripts to the database.

• First log on to the database as a schema owner (connect jaffatest/changeme).

• Run the Sql script (BasePackage)_CreatePredicate.sql
  Note : To run the scripts in sqlplus , you need to type "@" and then the filename.

• This should create the Package and Package body successfully.

• Now logon as the system user (connect SYSTEM/changeme).

• Run the sql script (BasePackage)_ApplyPredicate.sql

• This will apply the predicates to the appropriate tables

Testing From SQL*Plus

Now to see if you've got it all working try the following...

• Log on to the database (connect jaffatest/changeme)

• Set the context to 'Mahesh' who is not a Manager
  SQL> JAFFA_SEC.SET_USERID('Mahesh', ROLE ('Clerk') );

• See how many items are available
  SQL> select count(*) from item;

• You should see less items, as only ones with sc starting with 'Jaffa' are visible.

• Now change to a different user that is a Manager

• Set the context to 'Paul' who is a Manager
  SQL> JAFFA_SEC.SET_USERID('Paul', ROLE ('Manager') );

• See how many items are available
  SQL> select count(*) from item;

• You should see all items.

Testing From Jaffa

There is a unittest to test data security. Its located at \source\unittest\java\org\jaffa\security\TestDataSecurity.java. This unittest sets security context and retreives Item record and checks for number of records retreived. Make sure that the snadbox.ant.properties is setup correctly to match your database information before running the unittests.

See Propagating Security Rules from the Business Tier to the Data Tier for more details of this.

Structure of the Policy DTD

<?xml version="1.0" encoding="US-ASCII"?>
<!-- This dtd is used for validating the XML file for generating the sql scripts -->
<!ELEMENT Domain (PatternTemplate,MappingPackage,SchemaName,BasePackage,Roles?,BasePredicate?,Description?,Tables)>
<!ELEMENT PatternTemplate (#PCDATA)>
<!ELEMENT MappingPackage (#PCDATA)>
<!ELEMENT SchemaName (#PCDATA)>
<!ELEMENT SqlPrefix (#PCDATA)>
<!ELEMENT BasePackage (#PCDATA)>
<!ELEMENT Roles (Role+)>
<!ELEMENT Role (#PCDATA)>
<!ELEMENT BasePredicate (#PCDATA)>
<!ELEMENT Description (#PCDATA)>
<!ELEMENT Tables (Table+)>
<!ELEMENT Table (PolicyName,SourceTable,(Direct|Indirect|Custom))>
<!ELEMENT PolicyName (#PCDATA)>
<!ELEMENT Direct (SourceField)>
<!ELEMENT Indirect (SourceField,ForeignTable,ForeignField)>
<!ELEMENT Custom (Predicate)>
<!ELEMENT SourceTable (#PCDATA)>
<!ELEMENT SourceField (#PCDATA)>
<!ELEMENT ForeignTable (#PCDATA)>
<!ELEMENT ForeignField (#PCDATA)>
<!ELEMENT Predicate (#PCDATA)>

• Under <Domain> tag
  
  • The <PatternTemplate > tag specifies the placeholder for script generator template in Jaffa
    
  • The <MappingPackage > tag specifies the package under which the script generator files will be created
    
  • The <SchemaName> tag specifies the schema name to which the security is applied.
    
  • The <SqlPrefix> tag specifies the Sql prefix for the security context.
    
  • The <BasePackage> tag specifies the package name to be created.
    
  • The <Role> tag specifies the the Role Name for which the security is applied to.You can specify a list of roles
    
  • The <BasePredicate> tag specifies the base predicate which will be used as a default predicate
    
  • The <Description> tag specifies description for the Policy File.
    
  
  
• Under <Table> tag
  
  • The <PolicyName> tag specifies the unique policy name
    
  • The <SourceTable> tag specifies the Source table to which the security is applied to.
    
  • The <Direct> tag specifies that the BasePredicate should be used .
    
  • The <Indirect> tag specifies that the SourceField is not a field in the one specified in BasePredicate and requires to access the ForeignTable
    
  • The <Custom> tag specifies a custom query .
    
  
  
• Under <Direct> tag
  
  • The <SourceField> specifies the field from the SourceTable .
    
  
  
• Under <Indirect> tag
  
  • The <ForiegnTable> specifies the table , if the SourceField is not present in the Table specified in the BasePredicate
    <ForiegnField> specifies the the field from the ForeignTable.
    
  
  
  
• Under <Custom> tag
  
  • The <Predicate> tag specifies the custom predicate.