How Unica Campaign uses data filters to restrict access to customer data

Administrators can define data filters in Unica Platform to restrict Unica users from accessing specific customer data. In Unica Campaign, data filters affect flowchart output.

To restrict data access, Unica Platform administrators define data filters and then assign users or groups of users to different data filters. For example, administrators can control access to customer data based on the geographical sales territory to which the users are assigned.

To learn how to set up data filters, see the Unica Platform Administrator's Guide.

Effect of data filters in Unica Campaign

Data filters apply to the Select, Extract, and Audience processes in Unica Campaign flowcharts. For example, if a database table includes 2000 records but 500 of them are restricted by a data filter, a Select All in Unica Campaign returns only 1500 records.

Data filters are applied to downstream processes when multiple processes of the same type are used in the same flowchart. For example, if the Select process is included at two different positions in the flowchart, one of which is downstream, data filters apply to both processes.

For downstream Select and Extract process boxes, when multiple tables are used as an input, the data from tables that are not filtered is added to the data from the filtered tables. This improves the performance of the processes. TEMP TABLE can be On or OFF for downstream data filtering to work.

Consider the following points about data filtering for all process boxes:
  • Data filters work with only a single table in Select and Extract processes.
  • When two different type of filters are used, the resultant data increases as the OR condition is used for the two filters.
  • Data filters do not affect design-time activities. For example, a data filter does not hide the values that are displayed when a field is profiled. Although users can see restricted data when they profile a field or build queries in a process configuration dialog, the query results do not include restricted data. Data filters are integrated into the SQL that the process box uses to query the table with which the filter is associated.
  • Data filters do not apply to raw SQL queries or to custom macros that use raw SQL. For example, if you use Select customer IDs using SQL to create a raw SQL query in the Select Process Configuration dialog, any data filters are ignored when you run the query. This behavior is intentional, to provide advanced users with the ability to perform SQL queries without restrictions.
Important: Raw SQL queries override data filters, so users who perform SQL queries can access records regardless of data filters. If you do not want Unica Campaign users to be able to use raw SQL, you must restrict their permissions.

Example

In this example, we are using the table DATAFILTER_TEST and the corresponding DATAFILTER_TEST.xml XML file. You must make the required changes to the instructions based on the database and the tables that you are using.

Consider that data filtering is to be applied to the table DATAFILTER_TEST. The following prerequisites must be completed for the table:
  1. Create the table DATAFILTER_TEST in the user database.

    For Oracle:

    CREATE TABLE DATAFILTER_TEST   
(  ID NUMBER,    
   NAME VARCHAR2(20),
   COUNTRY VARCHAR2(20),
   AGE NUMBER,
   ACCT_TYPE VARCHAR2(20), 
   RETAIL_ACCT VARCHAR2(10), 
   HOUSEHOLD VARCHAR2(50)  
);

    For DB2:

    CREATE TABLE SB6.DATAFILTER_TEST ( ID BIGINT, NAME VARCHAR(20), COUNTRY VARCHAR(20), AGE BIGINT,
ACCT_TYPE VARCHAR(20),  RETAIL_ACCT VARCHAR(10), HOUSEHOLD VARCHAR(50));

CREATE TABLE SB6.DATAFILTER_TEST 
(  ID BIGINT,
   NAME VARCHAR(20),
   COUNTRY VARCHAR(20),
   AGE BIGINT,  
   ACCT_TYPE VARCHAR(20),
   RETAIL_ACCT VARCHAR(10),
   HOUSEHOLD VARCHAR(50),
   BIRTHDAY TIMESTAMP,
   FIRSTOCCUPATION DATE
)
  2. Add data to the table.
  3. In the DATAFILTER_TEST.xml file, add the user table name AUTODCC.DATAFILTER_TEST. If you are using the SQL Server database, the table name must be specified as dbo.DATAFILTER_TEST.

    Make the required changes to the XML file based on the user table. The XML file must contain data level filter table information, logical fields, data level filter, audience information and so on.

  4. Add the XML file to the folder <Platform_Home>/tools/bin.
  5. Create Audience levels in Unica Campaign application. For example, Customer, Account, Composite, Household.
  6. Run ManagerSchema_PurgeDataFiltering.sql located at <Platform_Home>/db on the Platform database.
    Note: This step removes all existing data level filters that are applied.
  7. In the Unica Platform application, go to Configurations > General > Data Filtering and clear the Default table name. There Default table name must be blank. It Default table name is filled when the XML file is imported.
  8. Go to <Platform_Home>/tools/bin and run following command.
    ./datafilteringScriptTool.sh -r <xmlfileName>
    For example:
./datafilteringScriptTool.sh -r DATAFILTER_TEST.xml
bash-3.2# cd /opt/HCL/Campaign/SB8606/Platform/tools/bin/
bash-3.2# ./datafilteringScriptTool.sh -r DATAFILTER_TEST.xml
Script started
Script completed successfully
bash-3.2#
  9. In the Platform application, go to Settings > Configuration > General > Data filtering and ensure that the Default table name is AUTODCC.DATAFILTER_TEST. This is the same name as the <Schemaname.TableName> in the XML file.
  10. Set the default Audience to Customer.
  11. Go to Settings > Data Filters > Assign Users or Groups. Select a filter criteria and assign the filter to a corresponding user.

    For example, select Country as India, USA and the User as Test. The default Test user in Unica Campaign has permissions for table mappings and all permissions for all Campaign objects.