Intellicus Enterprise Reporting and Business Insights 19.1

Callback SQL Filtering

0 views September 22, 2020 0

Intellicus allows its customers to enable the security filtering of their report data. The filtering is typically on the basis of any user credential attributes like User ID, Org ID etc. This allows Intellicus customers to show only filtered records to a given user from their database.

The filtering is configured at organization level which enables the filtering on the data in the reports run by the users of that organization.

If a user who belongs to that organization runs a report, Intellicus would either apply filtering it self or call customer’s code depending on the configuration made.

This should be noted that during the report execution filtering is applied on all the relevant SQLs run, which include Report SQLs, Crosstab or Chart SQLs, Parameter SQLs etc.

SQL Filter

Configuration

Security Filtering can be done in following two ways:

  1. Intellicus: Using Intellicus, SQL filtering requires administrator to provide the filter column name which must be present in the database tables entities as an attribute and depending on which Intellicus would add a where clause in all the data fetching SQLs. (In the sample this column is WORKSPACEID). This way, the SQl is replaced and records are filtered based on given attribute.
  2. Callback: Intellicus supports callback SQL Filtering where Intellicus calls custom code to apply the filtering on all the data fetching SQLs.

Sample Schema
Let’s assume there are 50 tables, in which 45 tables have a field to separate row data.

Tables: COMPANY, CUSTOMER, CONTACT.

All these tables have a column WORKSPACEID which is used to divide rows amongst users.
Common Tables: CITY, COUNTRY
These tables store generic data and doesn’t need filtering.

Configuring SQL Filtering Callback Options

Customer system administrator configures the callback SQL Filtering. For configuration the administrator will go to organization screen where he/she enables callback SQL filtering.

Callback SQL Filtering
Figure 3: Callback SQL Filtering

Customer administrator enables security filtering and chooses Call Back Mechanism as the option between Intellicus and Call Back Mechanism.

Using call back security filtering requires administrator to provide the following:

  1. Filtering mode among Local, Socket or RMI
  2. Implementer class name which is a Java, C, or COM Class.
Callback SQL Filtering

Code implements Intellicus Call back Filtering interface

If Intellicus SQL filtering is not what customer wants, Intellicus also supports call back SQL Filtering where Intellicus calls customer’s code to apply the filtering on all the data fetching SQLs.

For Intellicus to call customers code customer will have to provide the implementation of the call back interface exposed by Intellicus.

Intellicus supports call back using various technologies:
Local Java

  • Implementer: Java Class
  • Call Type: In Memory

You provide Java implementation of Intellicus sql filtering call back interface and Intellicus calls it at runtime. The custom code library must be placed in Intellicus Report Server class path before starting the server.
Local COM

  • Implementer: COM Dll written in VB, VC++
  • Call Type: JNI COM call to Dll.

You provide a COM DLL implementation of the Intellicus SQL filtering call back COM interface. The report Server should be running on a Windows Platform.
Remote Call back

  • Implementer: COM Dll written in VB, VC++
  • Call Type: TCP Call to DLL Invoker and JNI Call

You provide a COM DLL implementation of the Intellicus SQL Filtering Call back interface. The report Server is running on non Windows Platform, thus can not support COM.

For the implementation of the interface Customer gets the interface from Intellicus.
Interface Details
For Java call back activities, Intellicus provides the interface in the form of a jar file, iCallback.jar.

The Jar contains the interface exposed by Intellicus, ISQLFilter.

Customer would implement the methods of the interface and provide the implementation class, normally, in the form of a jar file. The jar would be deployed in the Intellicus Report Server class path for Report Server to make the calls.

Equally, Intellicus provides IntellicusContext.dll for COM DLL implementation. The implementer COM DLL should be appropriately registered with the windows registry.

  1. Implement the interface ISQLFilter

public class CallBack_tables implements ISQLFilter

2. Initialize the class instance using initGlobal().

public void initGlobal() throws Exception
{
	System.out.println("---inside initGlobal()--------");
}

 
Call Sequence
Below are the methods of the call back filtering interface and the sequence in which they should be called:

Please refer to related Java doc for exact signature of the interface methods.

1) initGlobal()

Set the credentials and other information (helps custom class to initiate config files and database connections etc.).

 

2) int getFilterType()

Intellicus gets the filter type you want to implement:

Return Value = 1 or FILTER_TYPE_SQL

Type: SQL

Complete SQL statement will be passed for manipulation by your code and collected back.

Method for filter call: getFilteredSQL()

Return Value = 2 or FILTER_TYPE_TABLES

Type: TABLES

List of TABLES will be passed for manipulation by your code and collected back.

Method for filter call: getTablesFilters()

 

3) setOrgID(String orgID)

A series of setters are called for setting the context and credentials of the reporting user. Reporting user’s Intellicus OrgiD.

 

4) setUserID(String userID)

Reporting user’s Intellicus UserID.

 

5) setPassword(String passwd)

Reporting user’s Intellicus Password.

 

6) setSessionID( String sessionID)

Reporting user’s Intellicus sessionID.

 

7) setSD(String sd)

Reporting user’s SecurityDescriptor. (Custom tag)

 

8) setCustomerID(String customerID)

Reporting user’s Customer ID. (Custom tag)

 

9) setLocation(String location)

Reporting user’s Location ID. (Custom tag)

 

10) setLocale(String locale)

Reporting user’s Locale. (Custom tag)

 

11) setTimestamp(String timestamp)

Reporting request Time stamp (Custom tag)

 

12) setDBName(String dbName)

Get the filtered sql if filter type is SQL Filter Call

 

13) String getFilteredSQL(String sql)

Intellicus provides the original SQL to your code and gets the modified SQL back.

OR

13) String[] getTablesFilters(String[] Entities)

Intellicus provides the Entity names used in the SQL to your code and gets back the filters list to be replaced at the place of those Entities or Implicit Views to replace the table names.

Note: Please refer sample code available at

<Intellicus_Install_Path>SampleCodesCallBack APIsCallBack Filtering