Row Level Security

From Lianjapedia
Jump to: navigation, search

Lianja supports user roles and permissions for UI elements and these user roles extend into the core of the database engine to provide row level security.

Bm-noteicon.png
Quick Start Guide

You manage Row Level Security in the "Users" workspace.

Row Level Security (RLS) is a concept that provides security at the row level within the database layer, instead of at the application layer. With this feature you can implement RLS without changing your application code.

Managing Row Filters Declaratively

You manage row filters in the users workspace.

Bm-rowlevelsecurity.png

Row-Level Security enables customers to control access to rows in a database table based on the roles (or groups) of the user accessing data.

Row-Level Security (RLS) simplifies the design and coding of security in your application. RLS enables you to implement restrictions on data row access. For example ensuring that workers can access only those data rows that are pertinent to their department, or restricting a customer's data access to only the data relevant to their company.

The access restriction logic is located in the database engine rather than away from the data in applications. The database system applies the access restrictions every time that data access is attempted.

While designing the architecture of this functionality we looked at how it is implemented in other industry standard SQL engines and found all of them to be overly complex and long winded, so we came up with a simpler approach which is more flexible.

Row level security in Lianja is accomplished using row filters. Each row filter is associated with a tenancy, database, table and role name.

When a user authenticates, the tenancy and username determine the roles assigned to the user.

Whenever a database table is accessed, the database engine will lookup the row filters associated with the table based on the roles that the authenticated user has been assigned.

If a user has a specific role and a row filter exists for that named role then the row filter associated with that role is included (OR) from the records that can be read.

If the role name is negated e.g. ~manager and the user does not have manager role, then the row filter is excluded (AND NOT) from the records that can be read.

These row filters operate across native Lianja tables and virtual tables, so you can use them with MSSQL, MySQL, PostgreSQL and other third party SQL databases also.

When defining row filters for Virtual tables you should specify the name of the virtual table as the table e.g. vt_customers.

Row filters are transparent to the application in desktop, web and mobile apps as well as OData API calls. In other words, no application changes are required in order to benefit from row level security in Lianja.

Managing Row Filters Programatically

Row filters are stored in the system!sysrowfilters table.

Because row filters are stored in a database table you can also use standard SQL commands to perform CRUD operations on the row filters.

insert into system!sysrowfilters ;
    values ('public', 'southwind', 'customers', 'bname', [startsWith(customerid, 'B')])

Notice that 'public' is the tenancy that an authenticated user belongs to and 'bname' is the role you have assigned to the user. A user can have multiple roles.

If the role you specify is prefixed with a ~ e.g.

insert into system!sysrowfilters ;
    values ('public', 'southwind', 'customers', '~cname', [startsWith(customerid, 'C')])

and the authenticated user does not have the cname role, then all rows where the customerid starts with a 'C' are excluded for that user.

You can test data access for different users and roles using the following commands interactively in the console workspace.

open database southwind
use customers
list status 
list

The LIST STATUS command now includes the roles and row filters for each table.

Also see the SET ROWFILTER set command:

set rowfilter on | off
set rowfilter to <expression>

and the USERROWFILTER() function:

cRowFilter = userrowfilter([<cUsername> [, <cDatabase>, cTable>]])

and the $rowfilter OData argument, e.g.

/odata/southwind/customers?$rowfilter=country eq 'USA'

Pages in category "Row Level Security"

The following 4 pages are in this category, out of 4 total.