Lianja already supports user roles and permissions for UI elements and now in Lianja 3.4 user roles extend into the core of the database engine to provide row level security.
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 new feature you can implement RLS without changing your application code.
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.
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 changes in your applications is required in order to benefit from row level security in Lianja.
You create row filters in the users workspace.
Row filters are stored in the system!sysrowfilters table.
This table has the following table structure:
Structure for table : sysrowfilters.dbf
Alias : sysrowfilters
Number of records : 2
Date of creation : 02/17/2017
Date of last update : 02/17/2017
DES3 Encrypted : No
CursorAdaptor : No
Field Field Name Type Width Dec Description
1 DOMAIN Character 80 Domain
2 DATABASE Character 80 Database
3 TABLE Character 80 Table
4 ROLE Character 80 Role
5 ROWFILTER Memo 8 RowFilter
** Total ** 329
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 a 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 from what the user can see.
You can test data access for different users and roles using the following commands interactively in the console workspace.
Code:
open database southwind
use customers
list status
list
set rowfilter on | off
set rowfilter to <expression>
The list status command now includes the roles and row filters for each table.
Q:
in a context of a multi company DB .. I can filter data for company?
Customers Table structure:
CompanyID, customerId, customerName
if the user is assigned to the comapany "A"
i can:
Code:
insert into system! sysrowfilters values ( 'public', 'Southwind', 'customers', '~ CompanyID' [= 'A'])
or
And the user see and edit only the Customers records where "CompanyId" = "A"?
In this way I can create a multi tenancy app in a easy way..
A:
Lianja already provides you with multi tenancy databases. You could use row filters but having a separate database for each company/tenant is a better choice in my opinion.
Row filters are better suited to providing views on data depending on user roles.
In your example code you would set the ROLE to 'companya' and the ROWFILTER to companyid='A'
Then you assign the companya role to users in that company.
Q:
Suppose we have many contract staff who join and go on different date. When I add these new users, how can I auto set it to an "expired role" (i.e. deny further login beyond expiry will prompt "Contract expired.") say 1 year or 1/2 year from the date of entry?
A:
The users table does not have an expiry date column. You could however add a role e.g. expires20170701 and have a background task running once a day that removes users who have expired. This could be a .rsp page.
A2:
I have added an "Expires" column into system!sysroles table in Lianja 3.5. If a user attempt to login and an expiry date has been specified then the login is rejected if it has matured.
All topics in [Answers] alphabetically: https://www.lianja.com/community/sho...ll=1#post13748
These answers are also systematized on the site "Lianja developer": https://lianjadeveloper.wordpress.co...ers-and-roles/
Reply With Quote
Bookmarks