Users and Roles [examples]

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

https://www.lianja.com/doc/index.php/USER()


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

https://www.lianja.com/doc/index.php/USERNAME()


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

https://www.lianja.com/doc/index.php/USERDATAMASK()


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

https://www.lianja.com/doc/index.php/USERDOMAIN()


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

https://www.lianja.com/doc/index.php/USERTENANCY()


Email address for the current or specified user as defined in the Lianja Users Workspace
USERMAIL
? useremail()
myuser@lianja.com

https://www.lianja.com/doc/index.php/USEREMAIL()


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

https://www.lianja.com/doc/index.php/USERFULLNAME()


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

https://www.lianja.com/doc/index.php/USERROLES()


Rowfilter condition for the current or specified user for the current or specified table
USERROWFILTER
// '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"'

https://www.lianja.com/doc/index.php/USERROWFILTER()


String representing masked data
MASKEDDATA
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")

https://www.lianja.com/doc/index.php/MASKEDDATA()


Enable/disable use of LDAP/ActiveDirectory for user/group authentication
LIANJA_LDAP
set LIANJA_LDAP=ON
set LIANJA_LDAP=OFF

https://www.lianja.com/doc/index.php/LIANJA_LDAP


Define LDAP/ActiveDirectory Base DN
LIANJA_LDAP_BASEDN
set LIANJA_LDAP_BASEDN=OU=AD LDS Users,O=Lianja,C=UK

https://www.lianja.com/doc/index.php/LIANJA_LDAP_BASEDN


Define LDAP/ActiveDirectory server
LIANJA_LDAP_SERVER
set LIANJA_LDAP_SERVER=192.168.80.139:389

https://www.lianja.com/doc/index.php/LIANJA_LDAP_SERVER


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

https://www.lianja.com/doc/index.php/LDAP_USERROLES()


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

https://www.lianja.com/doc/index.php/LDAP_LOGIN()


Grants access privileges for users to tables
GRANT
// 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
GRANT ALL ON test TO PUBLIC

https://www.lianja.com/doc/index.php/GRANT


Revoke access privileges for users to tables
REVOKE
// 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
REVOKE ALL;
  ON test;
  FROM PUBLIC

https://www.lianja.com/doc/index.php/REVOKE


Currently active users on the system
SYSACTIVEUSERS
SELECT user_name FROM sysactiveusers

https://www.lianja.com/doc/index.php/SYSACTIVEUSERS


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

https://www.lianja.com/doc/index.php/SYSALERTS


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

https://www.lianja.com/doc/index.php/SYSUSERS


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

https://www.lianja.com/doc/index.php/ACCESS()


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

https://www.lianja.com/doc/index.php/GETGRNAM()


Group identity
GETID
if getgid() > 100 and getgid() < 200
//...
endif

https://www.lianja.com/doc/index.php/GETGID()


User identity
GETUID
if getuid() > 100 and getuid() < 200
//...
endif

https://www.lianja.com/doc/index.php/GETUID()


Dynamic role information
SYSPERMS
select * from system!sysperms

https://www.lianja.com/doc/index.php/SYSPERMS


User and role information
SYSROLES
select * from system!sysroles

https://www.lianja.com/doc/index.php/SYSROLES


Role based dynamic data masking
SYSDATAMASKS
select * from system!sysdatamasks

https://www.lianja.com/doc/index.php/SYSDATAMASKS


Role based row level filters
SYSROWFILTERS

 

select * from system!sysrowfilters

https://www.lianja.com/doc/index.php/SYSROWFILTERS


Advertisements

Permissions [examples]

Display current protection and security settings
LIST_PROTECTION
// 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

https://www.lianja.com/doc/index.php/LIST_PROTECTION


Display current protection and security settings
DISPLAY_PROTECTION

 

display protection

https://www.lianja.com/doc/index.php/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
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:
insert into system! sysrowfilters values ( ‘public’, ‘Southwind’, ‘customers’, ‘~ CompanyID’ [= ‘A’])

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

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.