1

Data Security and Session Variables Design

Overview

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. 

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.

 

Created by:  Amit Kothari

Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
Like1 Follow
  • 1 Likes
  • 1 mth agoLast active
  • 148Views
  • 2 Following

Welcome!

Welcome to Incorta's user community! Whether you are already an Incorta customer, or interested in becoming one, this is your place to come together and discuss the software, register for webinars, learn about events, learn about new product releases and get support from the community.