1. Authorization

For authorization, we use KiVi certificates. When a user connects with an SQL driver (JDBC, ODBC or SQLAlchemy), the user has to specify the username, password and database in the connection string. With this information, the Query Engine firstly authenticates the user with LDAP, then the user can connect to the database. Each time the user runs a query, the permissions are checked. Depending on the query, read or write permissions on tables are checked. If a query affects multiple tables, the permissions are checked for the affected tables. This permission check is delegated to KiVi’s certificates. The Query Engine runs with a bucket of certificates of users. With this bucket of certificates, the Query Engine can authorize each user´s access with KiVi.

To manage the permissions of the user, a bunch of SQL commands are available. To check the full documentation, check the LeanXcale user’s documentation.

GRANT <permission lists> ON TABLE <table name> TO <user>
GRANT <permission lists> ON SEQUENCE <sequence name> TO <user>
GRANT <permission lists> ON SCHEMA <schema name/user> TO <user>
REVOKE <permission lists> ON TABLE <table name> TO <user>
REVOKE <permission lists> ON SEQUENCE <sequence name> TO <user>
REVOKE <permission lists> ON SCHEMA <schema name/user> TO <user>

The following graph shows how permissions are checked when a client connects to the Query Engine (QE) from any of the available SQL drivers.

First, it connects to the Query Engine using a username and password. Then, the Query Engine authenticates the user against the LDAP server. Next, the client runs a query and the Query Engine accesses KiVi to retrieve or modify data. During that phase, whether the user is allowed to run the query on KiVi is checked. This protocol allows the database administrator to change the permissions of the users immediately.

auth

Using the SQL interface, access is checked per table. A user, by default, has access to all the tables that this user creates. If the user needs to have access to tables from another user, permissions need to be granted. The user can revoke access to some tables, even owned by itself. Consequently, the granularity of the authorization using the SQL interface is ensured per table.