xSQL Security Parameters

xSQL has the ability to password-protect a remote SQL connection.

For an overview see page xSQL Server Instrumentation

Password protection is implemented on the server by setting the sql_authentication_required parameter in the configuration file:

 
    sql_authentication_required : true,
     

On the client side the credentials can be either submitted through the configuration file:

 
    sql_login : 'user1',
    sql_password : 'mypass',
     

or alternatively on the commend-line when starting xSQL:

 
    ./xsql ... -user user1 -password mypass
     

Authentication

The authentication requires a table Users with two required string fields, login and password to keep usernames and passwords. For example:

 
    create table Users (login string primary key, password string);
    insert into Users values ('user1', 'mypass');
    insert into Users values ('user2', 'hYfjdKK2');
     

Remote client applications must provide the credentials to match those found in the table Users in order to connect to the database. Note the following important characteristics of this password-protection feature:

• The Users table can include fields in addition to login and password.
• The credentials are not encrypted by the database runtime. If desired, it is the application's responsibility to either encrypt or somehow protect the content of the Users table.
• The transmission of the credentials through the Remote SQL Engine protocol is not encrypted either. However it is possible to use the Remote server authentication in conjunction with SSL encryption.
• If SSL encryption (without authentication) is enabled when using xSQL, the password transmission will be protected.

For more powerful security, see the SSL section below.

Authorization

Authorization (for remote connections) is built atop of authentication and is performed for each query compilation.

It can be enabled by setting the configuration parameter sql_authorization_required to true (see target/xsql/readme.txt).

The Users table should contain two more fields (role and admin) in order to describe each user's role.

 

    Create table Users (
        login string primary key, 
        password string, 
        role integer, 
        admin boolean

    );

Also, the Permissions table should be created to describe access rights to a table (class) for a role.

 
    create table Permissions(
        class_code integer, 
        role integer, 
        access_mask integer);

class_code is a numeric code that corresponds to the text name of an SQL table. It can be determined by the table name, for instance, MyTable1. To find out the class_code, you can use the following SQL query:

 
    select Cid from Metaschema where TableName=‘MyTable1’;

role is an integer that represents a specific role. You can assign any integer value to it and use it in the table Users to assign roles to users.

access_mask is an integer (bitmask) that defines the Unix style "rxw" permissions that users with this role have with respect to a table with this class_code.

 
    4 ReadTable
    2 UpdateTable
    1 AlterTable

Administrators (i.e. users with admin set to true) are allowed to do everything with the tables (permissions are not checked).

For a non-admin user permissions are checked according to the user's role.

When a user creates a new table his roles and permissions are adjusted automatically so that he is granted maximal permissions for this table initially.

Example of authorization tables creation:

 
    create table Users(login string primary key, password string, role integer, admin boolean);
    create table Permissions(class_code integer, role integer, access_mask integer);
    create index Permissions_pk on Permissions(role,class_code);
    insert into Users values ('root','123',1,true);
    insert into Users values ('temp','111',2,false);
    create table t1(x integer);
    create table t2(x integer);
    insert into Permissions values (11, 2, 4);

Example of the query:

 
QueryResult result(con.executeQuery(authorizationRequired							   
    ? "select role,admin from Users where login=%v and password=%v"
    : "select * from Users where login=%v and password=%v",											     
       login, password));

C and C++ samples:

 
     eXtremeDB/samples/native/sql/api/sql-01-c-api/rsql/
     eXtremeDB/samples/native/sql/api/sql-10-rsql/

Currently, eXtremeDB authentication/authorization is not applicable to "local connections", it works with RSQL connections (SqlRemoteConnection) to an eXtremeSQL server (the xsql application or a custom server application) only.

SSL support

xSQL supports SSL for both server and client Remote SQL connections, as well as for High Availability and Cluster connections. SSL is enabled when the ssl_params section is present in the configuration file. Following is an example of the ssl_params configuration file settings for encrypted client-server exchange with peer verification (both client's and server's certificates must be signed by the same CA certificate stored in the ca.pem file):

    Server:
     
    ssl_params : {
        verify_mode: ["verify_peer", "verify_fail_if_no_cert"],
        cert_file_pem: "../certs/server.crt",
        pkey_file_pem: "../certs/server.key",
        ca_file: "../certs/ca.pem",
    }
     
    Client:
     
    ssl_params : {
        verify_mode: ["verify_peer", "verify_fail_if_no_cert"],
        cert_file_pem: "../certs/client.crt",
        pkey_file_pem: "../certs/client.key",
        ca_file: "../certs/ca.pem",
    }
     

Note that the only difference between the client and the server parameters is the certificate and the private key. For peer verification the peers are required to have certificates signed by the same CA.

However, if only encryption without authentication is required (and the inherent security risk is acceptable), the "aNULL" family of ciphers can be used. For example, a configuration for encrypted client-server exchange without peer verification would have identical ssl_params sections like the following in the configuration files for both the server and the client:

 
    ssl_params : {
        cipher_list: "aNULL",
        verify_mode: ["verify_none"],
    }