Dynamic Row Level Security

In my previous posting, Row Level Security, I showed how you could create your own custom security access type.  In this post I want to show how you can automate the maintenance of this new Access Type.

To recap our dilemma, we needed away to secure associates row level data for our regional HR Team members.  The requirement was to allow them to see all associates within their region, but not their own department.  This type of security is usually done via the department security tree.  However, we have departments that cross regions and we needed to prevent these regional HR Team members from viewing job data for people not in there region.  This is when we came up with row level security by Department/Location.

How do we maintain this new security access type?

Row level access for our new Access Type is controlled by the SJT_CLASS_ALL table.  You can manually enter access into this table by using the Security By Permission List Component under, Main Menu>>Set Up HRMS>>Security>>Core Row Level Security>>Security by Permission List.  However, maintaining this type of data could be a nightmare.  So lets build an Application Engine that would allow us to control this configuration using a query associated to a Row Level Permission List.  Think of it as Dynamic Row Level Security!


Build a simple record and page to associate your Query to your Permission List.

Record to associate Permission List to Query

Page for Online Configuration:







Then write an application Engine to process your query and store the results in the SJT_CLASS and SJT_CLASS_ALL.


Your Query needs to return distinct combinations of your custom Access Type, in our case this is Business Unit, Deptid and Location.





In the Example Above, I created a simple query from the Job Record and Location Table, where the location state is Colorado and the Department is not an HR Department.  It might be more beneficial to create a view from these tables and secure the view using the Query Security Tree and bypass the row level security.  This is important, if you are going to run this process in batch mode, then your batch id will need to have access to your query and row level security to all job rows in order to process the query correctly. 


Now that I have my query, I can associate it to a Row Level permission list and write a simple Application Engine to Loop through all my dynamic row level configurations and generate my access.

Here is the Application Engine:

Create a custom version of the SJT_CLASS to store your process results, prior to inserting into the delivered SJT tables.

Step 1

Delete from PS_ZZ_SJT_CLASS

 



 Step 2- Peoplecode

Local Rowset &rowLevel_RS;
Local ApiObject &aRunQry;
Local Rowset &aRowSet;
Local Record &aQryPromptRec;
Local number &count, &x, &r;
Local string &VALID;



Rem ZZ_ROW_LVL_SEC contains the relationship between the permission list and Query;
&rowLevel_RS = CreateRowset(Record.ZZ_ROW_LVL_SEC);

&count = &rowLevel_RS.Fill();

For &x = 1 To &count
  
   &aRunQry = %Session.GetQuery();
  
   If (&aRunQry.Open(&rowLevel_RS(&x).GetRecord(1).QRYNAME.Value, False, False) <> 0) Then
      Error ("Error in opening query");
   Else
      SQLExec("select 'FALSE' from psqryfield where qryname = :1 AND columnnum <=3 and ((columnnum = 1 AND FIELDNAME <> 'BUSINESS_UNIT') OR(columnnum = 2 AND FIELDNAME <> 'DEPTID') OR (columnnum = 3 AND FIELDNAME <> 'LOCATION'))",&rowLevel_RS(&x).GetRecord(1).QRYNAME.Value, &VALID);
      If &VALID = "FALSE" Then
         Error ("Invalid Query " | &rowLevel_RS(&x).GetRecord(1).QRYNAME.Value);
      Else
         &aQryPromptRec = &aRunQry.PromptRecord;
         &aRowSet = &aRunQry.RunToRowset(&aQryPromptRec, 0);
        
         For &r = 1 To &aRowSet.RowCount
            If All(&aRowSet(&r).GetRecord(1).GetField(1).Value) Then
               SQLExec("insert into ps_zz_sjt_class values (:1,'PPLJOB','041',:2,:3,:4)", &rowLevel_RS(&x).GetRecord(1).CLASSID.Value, &aRowSet(&r).GetRecord(1).GetField(1).Value, &aRowSet(&r).GetRecord(1).GetField(2).Value, &aRowSet(&r).GetRecord(1).GetField(3).Value);
            End-If;
         End-For;
      End-If;
   End-If;
End-For;


SQLExec("DELETE FROM PS_SJT_CLASS WHERE CLASSID IN ( SELECT CLASSID FROM PS_ZZ_ROW_LVL_SEC) AND SCRTY_SET_CD = 'PPLJOB' AND SCRTY_TYPE_CD = '041'");
SQLExec("DELETE FROM PS_SJT_CLASS_ALL WHERE CLASSID IN ( SELECT CLASSID FROM PS_ZZ_ROW_LVL_SEC) AND SCRTY_SET_CD = 'PPLJOB' AND SCRTY_TYPE_CD = '041'");
SQLExec("INSERT INTO PS_SJT_CLASS SELECT * FROM PS_ZZ_SJT_CLASS where SCRTY_SET_CD = 'PPLJOB' AND SCRTY_TYPE_CD = '041'");
SQLExec("Insert into PS_SJT_CLASS_ALL (CLASSID ,SCRTY_SET_CD, SCRTY_TYPE_CD, SCRTY_KEY1, SCRTY_KEY2, SCRTY_KEY3, TREE)  select CLASSID ,SCRTY_SET_CD, SCRTY_TYPE_CD, SCRTY_KEY1, SCRTY_KEY2, SCRTY_KEY3, 'N' from PS_ZZ_SJT_CLASS Where SCRTY_SET_CD = 'PPLJOB' AND SCRTY_TYPE_CD = '041'");


The code above loops through the configuration table we created, then opens the query and validates that the first three rows are Business Unit, Deptid and Location then it runs the query to a rowset.  Inserting the query results into our custom ZZ_SJT_CLASS table.  After we have processed all the queries in the configuration, the process deletes all the current configuration for the permission lists being processed from the Delivered SJT tables and inserts the results of our processing into the SJT_CLASS AND SJT_CLASS_ALL.


Access is granted.