Row-level security is a data security technique that restricts access to rows (or records) within a database table. For example, if a table contains sales transactions across many geographic territories, you may want to filter data by matching country codes so managers only see rows for transactions within their territory.
Why does row-level security matter? Because it’s fundamental for achieving the principle of least privilege (PoLP), which is a key tenet of data security, data privacy, and regulatory compliance.
Row-level security can be implemented to varying degrees in most BI, reporting, and database platforms. Search for “row level security [insert your BI tool or database here]” then do the same search for another BI tool or database, and you’ll quickly see that you’re facing a consistency and workload management nightmare.
When you’re working in fragmented silos, how can you have confidence that you implement data security policies correctly and consistently — everywhere? With so much variety and complexity, how do you demonstrate compliance with data privacy and security mandates to your CISO and auditors?
Row-level security can be achieved in several ways, with varying degrees of sophistication, flexibility, and explainability. Examples range from one-off parameterized queries that apply filters to shared dashboards, to stand-alone data extracts customized for different user groups, to database views, multiple roles (more on “role explosion” below), and finally, the focus of this blog post: modern, universal policies that are compatible with all your data consumer tools and are data platform agnostic.
Introducing Row-Level Security using Attribute-Based Access Control (ABAC)
Most modern approaches to row-level security rely on one contextual element to determine who can see what: the user’s group membership or role. In 2021, Okera evolved row-level security to enforce data privacy and security policies in a manner that is even more powerful, clear, concise, and scalable.
We did this by augmenting role-based access control (RBAC) with attribute-based access control (ABAC).
What is ABAC?
ABAC is one of Okera’s key design principles, as it is less prone to errors, easier, and more cost-effective to scale than hard-coded, resource-level policies. However, until recently, ABAC was limited to attributes for data objects like databases, tables, and columns. Now Okera policies can dynamically enforce fine-grained data access control (FGAC) by referencing both data object attributes and user attributes.
Keeping Row-Level Security Simple
Complexity is the enemy of security, and at Okera we strive to keep things as simple as possible. So let’s take a look at row-level security with Okera.
We’ll demonstrate with a simple sales dashboard. In this example, the logged-in user, Sally, is an analyst working with the North America sales team. The following table summarizes the policy requirements.
Legal basis for data use
Sally has no legitimate business purpose for working with the sales contact’s:
- Name
- Phone number
As an analyst, Sally is responsible for finding and evaluating trends. For this she needs some sort of unique identifier for aggregations and joins, but that identifier should not be traceable to a real person.
Sally should only work with data related to North America sales
Policy implementation*
Some options include hide, mask, null, zero
- Name → null
- Phone number → mask
The best available unique identifier in this dataset is email address.
- Email → tokenize
- Postal code → tokenize
Row-level security not yet implemented
* See Privacy and Security Functions in the Okera documentation for more options.
Okera authorized the queries in this dashboard to apply column level security as required in this example. Okera dynamically enforced column-level data redaction, masking, and tokenization. Row-level security is not yet implemented.
The objective now is to filter the data to show only records for Sally’s territory. Currently she sees all data for global sales, but for her role she should only work with data related to sales in North America.
By logging into Okera as a privileged administrator, we can see the policy grouping that governs sales data. We see three policies are in place: one for administrators, another for public use, and the last one for sales analysts like Sally.
To add row-level security, we simply edit the policy to dynamically filter rows by matching a user attribute with the value in a column.
Note that the user attribute key drop-down is populated with values (read more about user attribute keys below). Also notice that the column field doesn’t require you to specify a fully qualified column name. As long as there is a column named “region,” as shown in this example, the database doesn’t matter. Okera policies are database agnostic, and we take care of all the backend mapping for you.
The permission summary is straight-forward, with no complex code or annotation to decipher. This is so non-technical stakeholders, like your compliance officer or auditors, can understand and validate that the policy is implemented as intended. Those interested in knowing what’s under the hood can simply click “View as SQL”.
The next time Sally refreshes the dashboard, row-level security is dynamically enforced so she can only work with sales transactions from North America. Super easy!
With Okera, one role with one policy can scale to work with any number of users, user attributes, and datasets.
An End to Role Explosion with ABAC
This looks great, but how much of a role reduction can you really expect with Okera? Suppose you have one country manager for each of the 27 EU members and they need to work with data specific to their country. As shown in our example, with Okera you simply have one role and one policy:
Role country_manager: filter rows where user attribute ‘country’ matches database column ‘country’
Alternative approaches would require 27 different database views or multiple roles with hard-coded matching values. For example:
Role country_manager_Austria: filter rows where database.table.country=’AT'Role country_manager_Belgium: filter rows where database.table.country=’BE'Role country_manager_Bulgaria: filter rows where database.table.country=’BG' . . .Role country_manager_Sweden: filter rows where database.table.country=’SE’
This is a simplistic example. In the real world, row-level security gets far more complex, resulting in an even more daunting and highly undesirable “role explosion.”
One Universal ABAC Policy
In addition to the dramatic reduction in role complexity, one of Okera’s greatest benefits is that these policies are completely agnostic to the underlying data platforms. It’s important to point out that the dashboard in our example is populated from two entirely different data sources: the Sales Volume and Sales by Country tiles come from PostgreSQL, and the Customers data is populated from an Amazon S3 bucket. With Okera you do not create “service” or platform-specific policies.
Not only do you reduce the number of roles necessary to achieve consistent row-level data security, you only have to define the policy once, regardless of whether you have one, two, or hundreds of disparate datasets!
This is what Okera means by universal policies. It’s not just that the Okera policy builder offers a simple, standardized policy language that works across multiple data platforms. Okera policies are special because they truly are universal, meaning that a single policy can enforce fine-grained access control to multiple datasets at the same time, such as:
- Cloud data object stores like Amazon S3, Azure ADLS, Google Storage (GS)
- Cloud data platforms like Snowflake, Databricks, Amazon Redshift, Azure Synapse, and Google BigQuery
- Big Data (Hadoop) compute platforms that run Spark, Hive, and Presto, such as Amazon EMR, Azure HDInsight, Google Dataproc, and Cloudera CDH or CDP
- Stand-alone query engines like Denodo, Dremio, Starburst (Trino/PrestoSQL), Amazon Athena (PrestoDB)
- And many other data platforms and data science tools like Dataiku, Domino, Amazon Sagemaker, Jupyterhub, Python (Pandas), traditional relational databases, and more
More on User Attribute Keys
So how do those magical user attribute keys populate the policy builder dropdown? Okera lets you source them from the single-source-of-truth systems you use today, such as an Active Directory/LDAP integration, or scripts that reach out to custom entitlement databases — or both! Okera allows you to customize which values are relevant for data access control so you don’t overwhelm the dropdown list with extraneous keys.
This means that as people join or leave your company, change positions, get assigned to special committees or projects, and so on, all those user attributes are managed by HR or other authorized personnel. Okera is designed to allow integrations and separation of duties so you can implement the principle of least privilege the way it makes sense for your teams and your business.
“We simply populate their profile with the values that they are permitted to see and assign them to the role.
“We can distill a very large number of existing filters down to a much smaller set of Okera roles.”
Summary of ABAC Benefits for Row-Level Security
By implementing row-level security in Okera with ABAC, you satisfy the needs of your data protection officer, data security team, compliance officer, data engineering team, and business leaders. Specifically, you achieve:
- Better data security and faster compliance with data privacy and other data protection regulations such as GDPR, LGDP, CCPA/CCRA, Sarbanes-Oxley, HIPAA, and so on
- Massive economy of scale as you onboard, offboard, and transition both people and data
- Clarity of purpose for each policy, as the policies are discoverable and understandable by all stakeholders
- Consistent policy enforcement across all the data assets under Okera’s governance
- Clear accountability as to who has authority to implement policies and assign attributes (separation of duties)
To learn how Okera helps companies gain full visibility into how sensitive data is used and how to standardize and simplify fine-grained access control across the enterprise, sign up for a free demo or contact us to learn more.
Watch ABAC-augmented row-level security in action with Okera and Snowflake!
