Data Security using Session Variables

[amit_kothari]

Overview

This article discusses how to address some data security (row level security) use cases ensuring that the solution is scalable and handles large data sets.

Please refer to this link for overall support of security in Incorta.

An example is provided.

Team Data Model

• A person may belong to multiple teams and a team may have multiple people.
• A Team may belong to multiple Team Sets and
• Accounts are secured by Team sets

• Each Account record is owned by one team set. Only members of the teams belonging to the team sets can access that record. 

Requirements

1. Regular Security

• Secure the Accounts data by the Team Set security (model above).
• Secure the Activity data by user’s market, for example, US Education, EMEA  etc.

2. Exception Security

One or more Incorta users belong to certain groups that need to view all records. The groups can be:  ExecutiveGroup (who can see everything), DirectorGroup, etc.

Design

1. My List of Teams

We get the list of team set IDs for the user by populating an external session variable ($GetTeamSets) to get the list of TEAM Set IDs for the logged in user ($user).

SELECT tst.team_set_id

FROM team_memberships tm,users_vw u,team_sets_teams tst

WHERE u.employee_id_c = $user

AND tm.user_id = u.id

and tst.team_id = tm.TEAM_ID

2. My Market

Get the Market by populating an internal session variable ($V_UserMarket)  by using the following expression:

query(<schema>.USERS_VW.market,<schema>.USERS_VW.LOGINNAME=$user)

3. Exception Users

Create a simple table called SeeAllDepartments (loginname,access_flag) to store users who can see all departments. You can use an excel file or a database table.

Refresh the table when you add new uses to these exception groups.

Populate another internal session variable ($CheckUserInList) to check if the current user exists in the above table. Use Y or N for session variables by using the following expression:

query(lookup(<schema>.SeeAllDepartments.ACCESS_FLAG, (<schema>.SeeAllDepartments.LOGINNAME, $user))

4. Security Filter

Add a security filter to the schema table you want to secure based on the following variables:

1. Secure <schema>.ACCOUNTS table using the following runtime security filter in the schema: or($user=’admin’,$CheckUserInList='Y',inlist(<schema>.ACCOUNTS.TEAM_SET_ID,$GetTeamSets))
2. Secure <schema>.ACTIVITIES table using the following runtime security filter in the schema table: or($user=’admin’,$CheckUserInList='Y',<schema>.USER_CREATED.market =$V_UserMarket)

This condition turns all records true if the user is an administrator or member of the exception groups. For other users, the condition adds an implicit condition to any query on table, so users only see records for their own teams or market.

Note

Session variables are populated at login time from Incorta data, so security filters are as of the last refresh in Incorta.