Data Security and Session Variables Design
This article discusses how to address some data security use cases ensuring that the solution is scalable and handles large data sets. 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.
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.
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:
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:
- 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))
- 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.
Session variables are populated at login time from Incorta data, so security filters are as of the last refresh in Incorta.
Created by: Amit Kothari