Row-Level Security: SQL Server vs Gallium Data

Introduction

In SQL Server 2016, Microsoft introduced the concept of row-level security, which gives you fine-grained control over who gets access to what data, potentially down to the level of individual rows. Normally, SQL security grants coarse access to a whole table or view (SQL Server can also do it for columns), and anything more granular than that requires the use of views or stored procedures.

There is another way to do row-level security without changing the database clients, and that's with Gallium Data - a free database proxy that can change the network traffic between your database clients and your database servers.

Each solution has its strong points and weaknesses: let's compare them and find out which one fits which scenarios. 

The bottom line for the impatient:

• SQL Server row-level security has the main advantage of being built-in.
• Gallium Data has the main advantage of being much more flexible. 

SQL Server's Row-Level Security

You add row-level security to SQL Server by registering SQL functions called predicates, which are invoked automatically when accessing a given table. There are two kinds of predicates:

• Filter predicates, which are used for select, update, and delete operations.
• Block predicates, which are used for insert, update, and delete.

When SQL Server receives a SQL command, it executes these predicates along with the operation, and joins the results of the predicates with the relevant tables, thus allowing you to exclude certain rows. 

Sample Data

We'll use the following trivial database for our examples:

create table demo.customers (
  id int not null,
  name nvarchar(100) not null,
  country char(2) not null,
  primary key (id)
)

insert into demo.customers values
(1, 'Andrea', 'AR'),
(2, 'Bruno', 'BE'),
(3, 'Charles', 'CA'),
(4, 'Daniella', 'DK'),
(5, 'Eric', 'ES')

Filter Predicates

If we assume that our database users' names start with a region prefix (e.g. EU_jdoe), we can restrict access to this table with the following:

create function demo.RestrictCustomers(@country as sysname)
returns table with schemabinding as
return select 1 as res
where
(@country in ('DK', 'BE', 'ES') and user_name() like 'EU_%') or
(@country in ('US', 'CA') and user_name() like 'NA_%') or
(suser_name() = 'sa')

This specifies that users whose name starts with "EU_" only get access to customers in Denmark, Belgium, or Spain, and users whose name starts with "NA_" only get access to customers in the US or Canada. We also make sure that users sa can always see all customers.

This type of function can be as complex as needed and can include joins and calls to other functions. Keep in mind, however, that every access to that table will incur the cost of that function, and of the resulting join.

We register our function as a filter predicate with the below:

create security policy CustomersPolicy
add filter predicate demo.RestrictCustomers(country)
on demo.customers
with (state = on)

We'll need a user to test this like so:

create user EU_jdoe without login
grant select on demo.customers to EU_jdoe

Then, if we run a simple query like so:

execute as user = 'EU_jdoe'
select * from demo.customers
revert

We'll get back only the three customer rows that are in Europe. It works!

Note that access is granted at the row level: there is no way to specify that a specific column in a specific row is off-limits using this mechanism.

Block Predicates

Block predicates are similar, but they apply to inserts, updates, and/or deletes. There are four kinds: after insert, before the update, after the update, and before delete.

For instance, we can make sure that our users can only insert data in their region with the following:

alter security policy CustomersPolicy
add block predicate demo.RestrictCustomers(country)
on demo.customers after insert

You'd want to do the same for update and delete. This will check that the newly-inserted row is visible to the user who inserted it and if not, it will reject the insert.

With this in place, a user can insert a row if it obeys our predicate:

execute as user = 'EU_jdoe'
insert into demo.customers values (100, 'Didi', 'DK')
revert

But, an invalid insert gets rejected:

execute as user = 'EU_jdoe'
insert into demo.customers values (101, 'Cameron', 'CA')
revert

The attempted operation failed because the target object 'demo.customers' 
has a block predicate that conflicts with this operation etc...

How Secure Is SQL Server’s Row-Level Security?

It's not bullet-proof. For instance, user EU_jdoe does not have visibility to customer 3 (who is in Canada), but can detect that the row exists with a cleverly crafted SQL:

execute as user = 'EU_jdoe'
select * from demo.customers where 100/(id - 3) = 1
revert

This will fail (intentionally) with the error message: Divide by zero error encountered, which tells EU_jdoe that a row with id=3 exists.

You can take this much further if you can run a lot of queries, for instance:

execute as user = 'EU_jdoe'
select * from demo.customers where 100/(patindex('Ch%', name) - 1) = 1
revert

This will fail with the same error message, allowing user EU_jdoe to determine that there is at least one customer with a name starting with 'Ch', even though that user is not supposed to have any visibility to that customer. It should be obvious that a clever attacker can sniff out data pretty quickly.

Overall, I'd say that row-level security is not likely to stop a malicious actor, and should be used more to complement an application's logic, for instance, to externalize data access rules. 

Gallium Data's Row-Level Security

Gallium Data takes a very different approach. It acts as a smart proxy between database clients and database servers and executes user-defined logic (expressed as filters and JavaScript code) that can modify that traffic when appropriate.

For row-level security, Gallium Data has two options: modify the SQL command on its way to SQL Server, or filter the result set coming back from SQL Server. These two options can be combined. 

Request Filter: Change the SQL Command

For instance, a simple request filter in Gallium Data can look for the statement:

select * from demo.customers where name like '?'

And, rewrite it to:

select * from demo.customers where name like '?' and country in ('DK', 'BE', 'ES')

Depending on the current user. This works well if you know in advance what SQL commands to expect.

This approach is effective for broad-stroke filtering, and can include more complex rewriting to mask or modify column values, e.g.:

select id,
case
  when country in ('BE', 'DK', 'ES') and user_name() not like 'EU_%'
  then '<hidden>'
  when country in ('CA', 'US') and user_name() not like 'NA_%'
  then '<hidden>'
  else name
end as name,
country
from demo.customers where name like '?'

Response Filter: Change the ResultSet

The other (and often complementary) option is to filter the result sets. Since it potentially involves the execution of code for each row in a result set, this is obviously a more expensive, but very flexible solution. The JavaScript code for the filter could look something like this:

let country = context.packet.country;
if (country === 'DK' || country === 'BE' || country === 'ES') {
  context.packet.remove();
}

This is less efficient than changing the query, but it's much more flexible: we can modify the row, insert new rows, etc. For instance, see the below:

let country = context.packet.country;
if (country === 'DK' || country === 'BE' || country === 'ES') {
  context.packet.name = '<n/a>';
  context.packet.country ='??';
}

If you have to filter large numbers of rows (i.e. millions), it can be expensive (though computers are really fast these days) to do this one row at a time, which is why result set filters allow you to narrow their definition to be invoked only for certain rows. The filter above would therefore be much more efficient if it was defined with the following parameter:

Column patterns: country=DK, country=BE, country=ES

You can also specify that the row should be hidden when the column patterns are satisfied. This allows for filtering without any code -- similar to SQL Server's row-level security. But for more complex logic, JavaScript is the way to go, as it gives you total power.

Summary

Advantages of SQL Server Row-Level Security

SQL Server	Gallium Data
Built-in: no need for an external system	Additional complexity: requires another system that must be kept up
Affects all queries and commands, regardless of projections, labels, etc.	Works best with known SQL commands
Good performance, depending on the complexity of predicates	Performance depends on the amount and complexity of logic

 Advantages of Gallium Data

Gallium Data	SQL Server
SQL commands can be changed in any way, including parameter values	SQL commands cannot be modified
Rows can be hidden, modified, or added in result sets	Can only show or hide entire rows
Does not require any special privileges in the database	Requires high-level database privileges to manage
Can be targeted to just some SQL commands	Affects all access to the table
Does not execute in the database, therefore puts no extra load on it	Adds load to the database server(s)
Can apply to calls on tables, views, and stored procedures, and react to any value, whether persisted or computed	Cannot apply to computed values
Not database-specific: works also with PostgreSQL, MySQL, and MongoDB	SQL Server only
Can be much more generic: Gallium Data sees all the requests and responses on the wire, so it can apply more general rules (i.e. "mask all columns whose name contains 'price' from users 'EU_*'")	Can only be done one table at a time

Conclusion: Who Does It Better?

These two approaches have different merits. They are not exclusive: it's possible to use both to implement different aspects of the requirements.

SQL Server's row-level security is a good approach if all you need is hiding and showing whole rows based on predicate logic. Access is controlled regardless of the SQL commands, which makes it appropriate in dynamic environments where you don't necessarily know what commands to expect. But if that's the case, you should also be aware that row-level security can be bypassed with relatively little effort, so don't bet the farm on it.

Gallium Data gives you unlimited flexibility -- you can hide rows, but you can also change them, mask values, compute values, even insert new rows into a result set. It works best for environments where you do know what SQL commands to expect, so you can catch them on the wire and modify them or their results.

P.S.

On a related note, you may be interested to see how Gallium Data can work with SQL Server's data classification feature to restrict data access, do data masking, and how Gallium Data can easily restrict queries based on prior behavior.