Column level security ("CLS") in the context of analytics is a data protection feature that allows you to control access to specific columns within a database table. This is particularly important in scenarios where certain data elements are sensitive or regulated, such as personal identifiable information (PII), financial details, or health records.
Implementing column level security requires careful planning to balance data accessibility and security, ensuring that users can perform their necessary tasks without compromising sensitive data.
These are some things to consider when implementing a CLS solution. Each of these is not always required, and companies have different requirements but being aware of them can be useful.
- Granular Access Control: Precisely define who can view or modify individual columns within a table. For example, while a general user may see certain basic columns, more sensitive columns like social security numbers or salary details might be restricted to privileged users.
- Role-Based Permissions: Access to columns is often governed by user roles. Users with different roles (e.g., analyst, manager, administrator) might have different levels of access to the data.
- Compliance and Privacy: This is crucial for adhering to various data protection laws and regulations like GDPR, HIPAA, etc., by ensuring that sensitive information is only accessible to authorized personnel.
- Flexibility: In analytics, column level security can be implemented in various ways, depending on the tools and databases in use.
- Impact on Analytics: This security feature can complicate data analysis and reporting processes since analysts need to be mindful of the varying access levels across different segments of data. For example, a CLS implementation should not "break" other important features like features for collaboration.
- Dynamic Data Masking: In some cases, column level security might involve dynamic data masking, where sensitive data is obscured or replaced with fictional data for users without access, while maintaining the real data for authorized users.
This QuickStart assumes you are generally familiar with Sigma or have taken the QuickStart Fundamentals series. Not all steps will be shown in detail.
Target Audience
Administrators who need to ensure that protected data columns are not available to users who are not authorized to see them or the data contained in them.
Prerequisites
- A computer with a current browser. It does not matter which browser you want to use.
- Access to your Sigma environment.
- Some familiarity with Sigma is assumed. Not all steps will be shown as the basics are assumed to be understood.
What You'll Learn
How to apply column level security in Sigma, through the administrative user interface.
In Sigma, CLS is managed through team membership and user attributes.
When user attributes are used to set CLS, column visibility based (dynamically) on the attribute value set for each team, and applied to the logged on user (based on the users team membership)
CLS can also be used to allow access to individual columns within a table to different embed users or clients.
The benefits of CLS as implemented in Sigma are:
- Secures sensitive information: For example, personal identifiers (e.g., social security numbers), financial data or medical records.
- Data sharing and collaboration: Column-Level Security enables controlled data sharing and collaboration. Organizations can share specific columns with external parties or partners without exposing the entire dataset.
- Data Confidentiality in Multi-Tenant Environments: In multi-tenant systems or cloud-based environments, where multiple clients or orgs share the same infrastructure, CLS ensures that each tenant's data remains isolated and protected from other tenants.
- Data Masking and Anonymization: Column-Level Security can be combined with data masking and anonymization techniques to protect sensitive data and still allow certain authorized users to work with pseudo or obfuscated values.
In the next few sections of this QuickStart, we will setup and test CLS.
The basic steps are (links to help docs provided for convenience only):
3: Assign a user attribute to teams
4: Set the column in a dataset to the user attribute to enforce security
6: Impersonate a test user to verify CLS is being enforced
In Sigma (as an Administrator) navigate to Administration > User Attributes and click to Create Attribute:
Give the new user attribute a name. For this, we will use permit_customer_data.
For the description, we will use Permit administrators and Sales Managers to access the customer details column. Restrict all others
The new user attribute is created and restricted to all users in the organization (for now).
For our use case, we will have a few scenarios to evaluate, related to our "sensitive data" column, which contains customer detail information in a Snowflake table.
We will have three users, and control visibility to this column to each differently, based on team assignment:
1: Administrators: are permitted to see the column.
2: Sales Managers: are permitted to add the column (or see it if the admin had already added it to a table).
3: Sales Users: are not permitted to see the column data.
To setup this use case, we will need to add the required teams and test users. It is assumed that you understand how to add teams and users to Sigma, so we will skip demonstrating that in details.
For information on how to add users to Sigma, click here.
For information on how to add Teams to Sigma, click here.
We have created three teams:
- Administrators: Account Type: Admin (is the workbook "owner")
- Sales Managers: Account Type: Creator (will be able to edit the workbook)
- Sales Reps: Account Type: Viewer (will only be able to see the data in the workbook)
Each team will have only one user (member).
If you don't have suitable teams to test with, go ahead and create them:
Now that we have our test users assigned to our teams, we can assign the new user attribute.
Navigate to Administration > User Attributes and edit the permit_customer_data attribute.
CLick Assign Attribute:
Assign the three teams as follows:
The value you assign to the Attribute Value field maps to a preexisting value. By doing this, you're managing CLS through team membership and user attribute value.
The values have the following effect on the column visibility:
If you assign 0 to the team, the column data is visible to the team.
If you assign 1 to the team, the column data isn't shown in dataset-based workbooks by default, but is available in the data source column list. The user is able to add the column manually.
If you assign 2 to the team, the column data is never shown.
As we have it configured, Sales Reps should not see the Cust Json column. Sales Managers are able to add the column from a table that contains it, or see it if the Administrator already added it to a Workbook.
Now we start to use our configuration against a new test dataset.
Create a new dataset (click the + in the upper left corner of the homepage) and navigate through the available data to the Sigma Sample Database. We want to use the PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA and really only need the two columns shown to keep things simple for testing:
Once the dataset is configured as shown, click Get Started
The column we want to apply CLS to is called Cust_Json and contains data in JSON format.
Sigma is great at working with JSON column data and there is a QuickStart that shows you how!.
The dataset is presented to you; next click the edit button and then click Columns and select the Visibility drop list for the Column Name Cust Json.
Select our user attribute, permit_customer_data to drive this columns visibility:
Click Publish, then click Explore
Before we move on to test against our teams, we first need to share this new workbook with them.
Let's remove the column from table display, so it is not displayed by default:
We can see that the column is still available, just not shown:
Click the Save As button and name the workbook Column Level Security.
In the workbook, share with the Sales Managers and Sales Reps teams (uncheck the Send email checkbox):
and...
Instead of logging out and back in with each user to test, Sigma has a neat feature that allows an administrator to "impersonate" any other user. This saves you time in the development lifecycle.
To read more about user impersonation, click here
Navigate to Administration > People and click to impersonate the user who is a "Sales Rep":
The portal header changes to indicate impersonation is enabled. Navigate back to Home > Shared with me and click to select the Column Level Security workbook:
The workbook is displayed (as a Viewer role) and the user sees no reference to the restricted column Cust Json:
Click the page header button to Stop Impersonation.
Navigate back to Administration > People and this time, impersonate the Sales Manager user:
The portal header changes to indicate impersonation is enabled. Navigate back to Home > Shared with me and click to select the Column Level Security workbook.
The workbook table is not showing the Cust_Json column and this is expected, given our configuration:
Click the Edit button.
Click the table (to select it) and open the PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA source column list.
Here we can see that the Cust_Json column is available, but the checkbox is not "ticked"
Let's go ahead and add it to the table ("tick" the checkbox on) and Publish the workbook.
Let's now check as Sale Rep, how this edit by the Sales Manager has effected the workbook.
Stop Impersonation again.
Navigate to Administration > People and click to impersonate the user who is a "Sales Rep":
The portal header changes to indicate impersonation is enabled. Navigate back to Home > Shared with me and click to select the Column Level Security workbook:
This time, a column is displayed in the second column position, but the content is "Restricted" and each cell shows "No Access".
There are some instances where this is the desired result as opposed to not showing the column at all.
Sigma allows you to support both use cases.
Sigma also provides users to "Request explore access" if they feel that they do not have sufficient permissions to perform their job:
The workbook owner will get an email, and can grant permission, if they have the rights to do so.
This feature can also be disabled, so that users are not presented the option at all.
For more information on this topic, please refer to this document
In this QuickStart, we learned how to apply column-level security in Sigma using the administrative user interface, as well as the various implications of different configurations.
Additional Resource Links
Be sure to check out all the latest developments at Sigma's First Friday Feature page!
Help Center Home
Sigma Community
Sigma Blog
