Security

This is a practical guide to manage LeanXcale database security for remote administration, from user creation and permission management to client configuration for using secure mode.

1. Implementation

LeanXcale database can be configured to start in secure mode in order to provide safe access for users and administrators. This mode provides authentication and authorization methods to guarantee that users are actually who they claim to be and can only access resources they are allowed to.

Regarding authentication, LeanXcale database integrates an LDAP server to provide user/password authentication. It can also be configured to use an existing external LDAP server instead of the packaged one.

For authorization, LeanXcale has a permission based authorization mechanism which guarantees that only users with specific permissions can read or modify database resources.

2. Securing the LeanXcale interfaces

You can secure both, the SQL and the KiVi interfaces through the Cloud Manager using the "Security" option inside the "Operations" menú:

There you’ll find ways to provide ACLs to your LeanXcale instance, and if you have Full security enabled, you’ll be able to secure each interface.

2.1. Securing the SQL interface

2.1.1. Securing SQL access

User creation

In order to secure the SQL access, you will need to create new users for operating with your LeanXcale database. These users can be created under the SQL Interface Security section, where you’ll also see the previously created users.

If you want to create new users, click on the "Add User" button and fill the User and Password fields, then click the "Save user" button:

Granting Permissions

Granting permissions over resources is done through the SQL interface. You can execute your queries using your SQL client or the "Run SQL" When using the secured SQL access, you can manage the permissions over resources through the SQL interface, so that a certain user can grant read or write permission to other user over tables and sequences.

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 possible permissions are the following:

READ
- Table permissions: Insert, Upsert, Update and Delete.
- Sequence permissions: Next and Current Value.
WRITE
- Table permissions: Select.
- Sequence permissions: Select.
ALTER
- Table permissions: Create, Drop, Grant and Revoke table permissions.
- Sequence permissions: Create, Drop, Grant and Revoke sequence permissions.

Granting Permissions: Examples

Revoke all permissions to group members

revoke read, write, alter on table t to scigroup

Allow select on table

grant read on table t to tesla

Allow select on schema

grant read on schema einstein to tesla

Allow select, insert, update and delete

grant read, write on table t to tesla

Allow create, drop or truncate, grant or revoke

grant alter on table t to tesla

2.1.2. Securing SQL communications

When full security is enabled, SQL communications are secured using HTTPS over TLS. Therefore, SQL driver connections must be configured to use the correspondent HTTPS certificate. In this section you will learn how to download the TLS certificate for your LeanXcale instance and configure your client depending on the driver selected and OS preferred.

Download TLS certificate

TLS certificate can be downloaded in the Security tab under the Operations section in the web console, in the SQL Interface Security.

JDBC

When using LeanXcale JDBC Driver you must add the parameter to the JDBC URL specifying that you want to use a secure connection.

Connection conn = DriverManager.getConnection("jdbc:leanxcale://123.45.67.89:1529/dbtest;secure=true", "user1", "pass4user1");

JDBC Certificate Configuration

You must also add LeanXcale ca certificate to your Java Truststore when using JDBC driver. In order to do that, in a linux environment:

# Go to security directory in your Java installation. For example:
cd /usr/lib/jvm/java-8-openjdk-amd64/jre/lib/security

# Add LeanXcaleSSLConnect.crt to Java truststore
sudo keytool -import -trustcacerts -keystore cacerts -storepass changeit -noprompt -alias mycert -file LeanXcaleSSLConnect.crt

SQLAlchemy

When using Python SQLAlchemy driver, you have to use the connect_args arguments to pass the secure: true tuple at engine creation.

create_engine(url, , connect_args={'secure': True})

SQLAlchemy Linux Certificate Configuration

To add TLS certificate to client when using SQLAlchemy in Linux, you have to add the certificate to your trusted ca certificates for your machine.

sudo mkdir /usr/local/share/ca-certificates/extra
sudo cp LeanXcaleSSLConnect.crt /usr/local/share/ca-certificates/extra/LeanXcaleSSLConnect.crt
sudo update-ca-certificates

SQLAlchemy Windows Certificate Configuration

If using Windows, to install LeanXcale TLS certificate you have to open the .crt file and add the certificate to the trusted ca certificates store.

ODBC

When using the ODBC Driver, the configuration required depends on which platform you are using: Linux or Windows.

ODBC Linux

If using Linux you must add the secure entry to the the $HOME/.odbcinst.ini file.

[LeanXcaleTests]
Driver = LeanXcaleODBCDriver
Description = Sample Database to be used for tests
Trace = No
Server = 123.45.67.89       # IP / hostname of your LeanXcale instance
Port = 1529
Database = dbtest
UserName = user1
Password = pass4user1
Encoding = WCHAR_T
Secure = True

ODBC Linux Certificate Configuration

ODBC driver relies in the certificates installed in the client machine, so the steps are the same as the ones for SQLAlchemy Linux Certificate configuration.

ODBC Windows

When using Windows, the installation process is the same as the one described in the ODBC driver documentation until the ODBC Data Source Administrator configuration. When configuring the ODBC Driver Setup, SSL Mode must be set to True.

ODBC Windows Certificate Configuration

ODBC driver relies in the certificates installed in the client machine, so the steps are the same as the ones for SQLAlchemy Windows Certificate configuration.

2.2. Securing the KiVi Direct API interface

In order to use LeanXcale KiVi direct APIs when full security is enabled, you need to create certificates for those APIs. When creating these certificates, you can select over which resources will have permissions. This is, you can select several schemas for a certain API certificate, and thus, this certificate will have read, write and execute permission over all resources in those schemas.

NOTE: For instructions on how to use these certificates in the APIS (Java, Python…) please, go to the Development section.