User and Roles – Lianja developer

Users and Roles [examples]

Published on 2018-08-262018-07-25 by homunizamLeave a comment

(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

Permissions [examples]

Published on 2018-08-112018-09-22 by homunizamLeave a comment

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

Published on 2018-01-16 by homunizamLeave a comment

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.

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

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.

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.