Users and Roles [examples]

(OS) login name of the current user
? user()

Login name of the current user as defined in the Lianja Users Workspace
? username()

Dynamic data mask for the specified column for the current user
open database southwind
? username()
? userdatamask("customers","customerid")

Domain/tenancy information for the current or specified user as defined in the Lianja Users Workspace
? userdomain()

Domain/tenancy information for the current or specified user as defined in the Lianja Users Workspace
? usertenancy()

Email address for the current or specified user as defined in the Lianja Users Workspace
? useremail()

Full name of the current user as defined in the Lianja Users Workspace
? userfullname()
William Colline

Roles for the current or specified user as defined in the Lianja Users Workspace
? userroles()

Rowfilter condition for the current or specified user for the current or specified table
// 'Harry' has the 'salesuk' role
? userrowfilter("Harry","southwind","customers")  //  --> 'country = "UK"'
// 'Sally' has 'salesusa' role
? userrowfilter("Sally","southwind","customers")  //  --> 'country = "USA"'
// Logged in as 'Harry'
open database southwind
use customers
? userrowfilter()  //   --> 'country = "UK"'
? userrowfilter("Sally")  //  --> 'country = "USA"'

String representing masked data
open database southwind
use customers alias cust in 3
// Display customerid data with 'partial' mask applied
? maskeddata(cust,"customerid","partial")
? maskeddata(3,"customerid","partial")
// Display customerid data with current user's dynamic data mask applied
? maskeddata(cust,"customerid")
? maskeddata(3,"customerid")

Enable/disable use of LDAP/ActiveDirectory for user/group authentication

Define LDAP/ActiveDirectory Base DN

Define LDAP/ActiveDirectory server

LDAP/ActiveDirectory groups the specified user is a member of
? ldap_userroles("Mandy Monroe","","OU=AD LDS Users,O=Lianja,C=UK"))

Test an LDAP/ActiveDirectory user login and return the Groups and Roles the user is a member of
? ldap_login("Mandy Monroe","CcxkTpjFbfw2ulmv","","OU=AD LDS Users,O=Lianja,C=UK"))

Grants access privileges for users to tables
// Grant update privilege for columns lastname and firstname and insert for the table
GRANT UPDATE (lastname, firstname) INSERT;
  ON customer;
  TO '[20,100]' 
// Grant all privileges to all users

Revoke access privileges for users to tables
// Revoke update privilege for columns lastname and firstname and insert on the table
REVOKE UPDATE (lastname, firstname) INSERT;
  ON customer;
  FROM '[20,100]'
// Grant all privileges to all users
  ON test;

Currently active users on the system
SELECT user_name FROM sysactiveusers

Alert records for watched records
SELECT * FROM sysalerts WHERE user = user()

Username and email information for alerts for watched records
SELECT * FROM sysusers WHERE user = user()

User access group
if access() > 100
    dialog box "Sorry, access denied."

Group name
use accounts
@2,3 get name
@3,3 get rates;
  when getgrnam() = "lianja"

Group identity
if getgid() > 100 and getgid() < 200

User identity
if getuid() > 100 and getuid() < 200

Dynamic role information
select * from system!sysperms

User and role information
select * from system!sysroles

Role based dynamic data masking
select * from system!sysdatamasks

Role based row level filters


select * from system!sysrowfilters

Permissions [examples]

Display current protection and security settings
// Display current protection and security settings
// for the currently active table
// a range of user identification codes used to allow groups 
// or individuals to perform certain table operations

list protection

Display current protection and security settings


display protection

Users and Roles

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.

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 architecting 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 changes in your applications is required in order to benefit from row level security in Lianja.

You create row filters in the users workspace.

Name:  Screen Shot 2017-02-17 at 12.03.51 PM.jpg Views: 62 Size:  47.9 KB

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.

open database southwind
use customers
list status

set rowfilter on | off
set rowfilter to <expression>

The list status command now includes the roles and row filters for each table.


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:
insert into system! sysrowfilters values ( ‘public’, ‘Southwind’, ‘customers’, ‘~ CompanyID’ [= ‘A’])

update …

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..


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.


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?


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.


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.