Imagine you just joined the human resources team at a large company. You quickly discover a security nightmare – the whole team has access to the internal employee database and the sensitive information it holds. The Social Security Numbers (SSN), salaries, phone numbers etc. of each employee in the company can be accessed by every member of the HR team.
While it is clearly necessary for the company to store and use this information, who gets access to it poses a security challenge. You certainly don’t want everyone in the company (or even everyone on the HR team) to have access to the sensitive details of fellow team members. This information should be accessible only on a strict need to know basis.
We can use role based access control (RBAC) to tackle this problem. RBAC limits access to information based on an individual’s role. These limits can be applied at the level of the entire database, schemas, individual tables within a schema, rows and columns within a table, data sources, credentials etc.
For instance, as a new member of the team the company might want to limit your access to sensitive information during your probationary period. With RBAC, the database administrator can choose to give you access to only the last 4 digits of another employee’s SSN, mask their salary entirely and only allow you to see a phone number if that person belongs to the HR team.
This is an example of column and row level security for a specific table.
In this demo, we’ll take on the role of a database administrator at a fictional company called OurCo, and implement column and row level security based on employee roles. We’ll start by creating an HR and engineering role, followed by assigning users to these roles and then grant column and row permissions for each role. All of this is accomplished using Kinetica’s workbench user interface and SQL.
Let’s look at a quick example. The query below grants all members of the engineering team access to employees’ first and last names, obfuscated cell phone numbers (i.e. you can verify that the number exists but you cannot access the number itself since it is hashed out), and the last 4 digits of their SSNs.
GRANT SELECT ON TABLE OurCo.Employees (first_name, last_name, HASH(cell_phone), MASK(ssn, 1, 7, '*')) TO engineeringUse the instructions below to try out this example yourself.
Getting started
Deploy Kinetica
You will need an instance of Kinetica to run this demo. There are three options (in order of preference):
- Kinetica on the cloud: The cloud version of Kinetica comes with an interactive notebook environment called Workbench. This is the preferred mode for deploying this demo.
- Free developer edition: The developer edition of Kinetica is a containerized application that can be run on your personal computer. Developer edition does not currently include Workbench.
- On-premise version of Kinetica: Please reach out to support@kinetica.com for a license.
Access demo files
You can access the relevant material for the demo from our examples repo here.
Workbook files can only be loaded if your version supports workbench. So if you are on a cloud instance of Kinetica, you can download the workbook JSON file and import it.
If you are on a version of Kinetica that does not support workbench, you can execute queries in the included SQL file using the query interface on GAdmin (the database administration application).
Contact Us
We’re a global team, and you can reach us on Slack with your questions and we will get back to you immediately.
