Kognitio server-side configuration

In order for Kognitio to be able to authenticate clients using Kerberos the following libraries need to be installed on all nodes used by Kognitio server:

  • libkrb5.so (package name krb5-32bit-… which may rely on other packages)

  • libldap.so (package name libldap-…)

  • cyrus-sasl-gssapi

We recommend using at least SLES11 if using the SLES Linux distribution, to minimise missing dependencies on other libraries.

You also need appropriate realm details in /etc/krb5.conf on all nodes. Typically that file will have entries like:

[libdefaults]
    default_realm = DEVELOPMENT.LOCAL
    rdns = false
    default_tkt_enctypes = arcfour-hmac-md5 des-cbc-crc des-cbc-md5
    default_tgs_enctypes = arcfour-hmac-md5 des-cbc-crc des-cbc-md5

[realms]
    DEVELOPMENT.LOCAL = {
            kdc = 172.30.0.4
            admin_server = 172.30.0.4
    }

arcfour-hmac-md5 entries allow Kognitio nodes to be able to understand service principal keys generated on Windows.

As linux root user edit the Kognitio global configuration file using the Kognitio command line tool wxviconf or wxeditconf adding the following lines:

[general]
server_principal_name=kognitio/<hostname>@<YOUR.REALM.NAME>
ldap_server_name=DEV-AD_DC.development.local

The server_principal_name attribute should match whatever you specified as the service principal name in Kerberos when creating the account for Kognitio. To use external groups with Active Directory you need to add the ldap_server_name parameter. This must be a hostname rather than just an IP address. You need to add an entry in /etc/hosts on every Kognitio Standalone node or on the edge node of Kognitio on Hadoop

The Kognitio server must be restarted for this change to take effect.

Kognitio client-side configuration

Add the following to your odbc.ini file in the appropriate Kognitio DSN section:

GSSEnabled=Y
GSSServicePrincipalName=<server principal name>

The first setting enables GSS authentication in the absence of a password or if a blank password is provided. The second setting prevents the user being prompted to verify the server principal name on their first connection.

If GSSServicePrincipalName is not set, then if you have a terminal, the first time you connect, you will be prompted to confirm that the Kognitio service principal name matches the service principal name you intend to connect to. If you confirm this a line is added to a file called ~/.kogknownservices associating the DSN with the service principal name, and when you connect to that DSN in the future, this file will be consulted and the client will only authenticate if the server’s principal name matches this.

This approach to discovering the service name, where the server is asked for its name the first time the client connects, and the user is asked to verify it, is also used by SSH (hence the warnings it gives you when the server’s host key changes), and is known as the leap-of-faith approach. It is described in page 15 of Best Practice for Integrating Kerberos into Your Appliaction in the section “Discovering the service name”.

If Kognitio’s Kerberos principal name changes later then when the user connects an error telling them to verify they are connecting to the correct service is displayed. Edit the ~/./kogknownservices file accordingly.

There are a number of additional options settings:

  • GSSEncryption=Y - use GSS to encrypt the session, rather than SSL. Y is the default.

  • GSSEncryptionAllowFallback=N - fail if encryption can’t be used for some reason. Y is the default, which means fallback to plaintext if encryption is not available at the GSSAPI layer.

For Linux clients, you will need to install the Kerberos client tools and add details of the domain to /etc/krb5.conf as shown above on the client side. You will also need to run kinit, which prompts for a password, to add the appropriate ticket to your credential cache so the GSS library can find it:

wxadmin wxadmin@hp-rack1-enc5-7:~> kinit testy.mctestface@DEVELOPMENT.LOCAL
Password for testy.mctestface@DEVELOPMENT.LOCAL: <enter the Windows password for testy.mctestface>
wxadmin wxadmin@hp-rack1-enc1-1:~> wxsubmit -s myserver testy
Kognitio WX2 SQL Submission Tool v8.02.00
(c)Copyright Kognitio Ltd 1992-2016.

Connected to myserver ODBC Version 8.02.00 Server Version 08.02.0000
>

The ticket created with kinit will persist for around 10 hours (you can check with klist). When it expires, you need to rerun kinit and enter your password again.

For Windows clients, the ODBC setup dialogue has an “Authentication and encryption” section:

  1. Click the “Kerberos authentication using Windows credentials”.

  2. Press the “Discover” button to ask the Kognitio nodes what the server principal name is – the name should appear in the text box next to the Discover button.

  3. Press “OK” to save changes.

Kognitio System Tables for Kerberos Authentication

There are a number of Kognitio system tables associated with Kerberos Authentication

  • SYS.IPE_ALLPRINCIPAL_NAME, maps a Kognitio USER_ID to a principal name with a priority setting.

  • SYS.IPE_ALLEXTERNAL_GROUP associates an Active Directory group with a Kognitio group.

  • SYS.IPE_CURSESSION_GROUP shows which groups a user’s session is a member of, either via direct inclusion with ALTER GROUP ... ADD USER..., or because of equivalence between Active Directory groups the user is in and Kognitio groups.

Supporting SQL syntax

You need the ADD/DROP PRINCIPAL privilege on a user to change the principal names for that user. These are granted via:

GRANT { ADD | DROP } PRINCIPAL ON <user1> TO <user2>

To associate a specified named principal to a specific Kognitio user modify the Kognitio user using the following syntax:

ALTER USER <username> ADD PRINCIPAL '<principalname>' [ PRIORITY <n> ]

This allows the Kerberos principalname to log on to Kognitio as the Kognitio user: username. This inserts a new row into the Kognitio system table SYS.IPE_ALLPRINCIPAL_NAME. The PRIORITY is optional and is only used when a someone tries to authenticate with a principalname that is assoicated with more than one Kognitio user. In this case there are multiple entries in SYS.IPE_ALLPRINCIPAL_NAME so the Kognitio user with the lowest PRIORITY value will be used for log on.

To remove the ability for a principal to log in to Kognitio as a user:

ALTER USER <username> DROP PRINCIPAL '<principalname>'

This removes the corresponding row in SYS.IPE_ALLPRINCIPAL_NAME.

External group mappings

An external group has an entry in IPE_ALLEXTERNAL_GROUP mapping an Activity Directory (AD) group to a Kognitio group.

A Kognitio user authenticated via Kerberos, and who is a member of the AD Group, will be treated as being in the specified Kognitio group in terms of privileges. Group memberships are considered transitive, so if a user is a member of Active Directory group G1, which is in turn a member of AD group G2, that user will be considered to be a member of both G1 and G2, and will have all the same rights associated with G2 as if it were a member of G2 directly.

A Kognitio user’s AD group membership is established by Kognitio at the start of a session, so changing a user’s AD group membership will take effect for new sessions for that user, but not existing ones.

To create an external group, the syntax is:

CREATE EXTERNAL GROUP <canonical name of AD group>
                GROUP <Kognitio group name>
                [CAN LOGIN]
                [TEMPLATE USER <user>]
                [PRIORITY <n>]

The final three optional clauses are only relevant for autocreated users.

To disassociate an AD group from a Kognitio group, the syntax is:

DROP EXTERNAL GROUP <canonical name of AD group>

Automatically created users

If a client connects and authenticates via Kerberos, but does not specify a user name, the principal name they authenticated with is looked up in Kognitio SYS.IPE_ALLPRINCIPAL_NAME table and the Kognitio user with the lowest priority value that matches the principal is used.

If the client’s principal does not appear in SYS.IPE_ALLPRINCIPAL_NAME, then if the AD user is a member of at least one group which appears in Kognitio SYS.IPE_ALLEXTERNAL_GROUP table with the CAN_LOGIN field set then a Kognitio username is created automatically for the user, and the client logs in as this user. The user is then associated with the principal name by adding a new row to IPE_ALLPRINCIPAL_NAME. The Kognitio username is derived from the principal name. If there are multiple matching external group entries, the one with the lowest priority value is used.

The new Kognitio user is created from the specified template user (same per-user parameters, queue settings, and privileges). If the template user’s default schema matches the template user name, the new user will have their own schema created matching their user name, and that will be set as their default schema.

Automatically created users have their SYS.IPE_ALLUSER.STATUS value set to 16 to distinguish them from manually created users. If their principal name is later removed from all AD groups which gave them CAN_LOGIN status, the user will no longer be allowed to log in.

Schematic of authentication process

The following flowchart explains the process used when authenticating with Kerberos:

                       .-------------------------------------------.
                       | Client connects to Kognitio server,       |
                       | successfully authenticates as a principal |
                       | name, attempts to start a session         |
                       '-------------------------------------------'
                                            |
                                            |
                              .--------------------------.
                         YES  | Has the client specified | NO
                    .--------<| a username?              |>------.
                    |         '--------------------------'       |
                    |                                            |
        .------------------------.                   .------------------------.
        | Does the client's      |                   | Does the client's      |
  YES   | principal appear next  |   NO              | principal appear in    |
   .---<| to this user ID in     |>--.               | IPE_ALLPRINCIPAL_NAME? |
   |    | IPE_ALLPRINCIPAL_NAME? |   |               '------------------------'
   |    '------------------------'   |                  | YES           NO |
   |                                 |                  |                  |
.---------------------.      .--------------.   .-----------------------.  |
| Pick this user ID   |      | Refuse login |   | Pick the highest      |  |
'---------------------'      | with AM004F  |   | priority user ID in   |  |
          |                  '--------------'   | IPE_ALLPRINCIPAL_NAME |  |
          |                                     | which appears next to |  |
          |      .-----------------------------<| this principal        |  |
          |      |                              '-----------------------'  |
 .------------------------.                                                |
 | Was this Kognitio user |                                                |
 | automatically created? |                                                |
 '------------------------'               .----------------------------------.
      | YES          NO |                 | Use LDAP to determine:           |
      |                 |                 | Is the client principal a member |
      |                 |             YES | of any AD group listed in        |
.--------------------.  |              .-<| IPE_ALLEXTERNAL_GROUP which has  |
| Use LDAP to        |  |              |  | CAN_LOGIN set?                   |
| determine: is the  |  |              |  '----------------------------------'
| client principal   |  |              |                               | NO
| a member of any AD |  |        .---------------------------.         |
| group listed in    |  |        | Pick the highest-priority |         |
| IPE_ALLEXTERNAL-   |  |        | such row in               |  .--------------.
| GROUP which has    |  |        | IPE_ALLEXTERNAL_GROUP to  |  | Refuse login |
| CAN_LOGIN set?     |  |        | find template user ID     |  | with AM0050  |
'--------------------'  |        '---------------------------'  '--------------'
  | YES    NO |         |                     |
  |           |         |        .---------------------------.
  |  .--------------.   |        | Generate a new username   |
  |  | Refuse login |   |        | <NEW_USERNAME> from the   |
  |  | with AM0053  |   |        | principal name            |
  |  '--------------'   |        '---------------------------'
  |                     |                     |
  |                     |    .----------------------------------.
  |                     |    | Automatically create a new user: |
  |                     |    | CREATE USER <NEW_USERNAME>       |
  |                     |    | TEMPLATE USER <TEMPLATE_USER>    |
  |                     |    | AUTOCREATED                      |
  |                     |    '----------------------------------'
  |                     |                     |
  |                     |    .----------------------------------.
  |                     |    | Associate this new user with the |
  |                     |    | authenticated principal name:    |
  |                     |    | ALTER USER <NEW_USERNAME>        |
  |                     |    | ADD PRINCIPAL '<principal>'      |
  |                     |    '----------------------------------'
  |                     |                     |
  |                     |        .-------------------------.
  |                     |        | Assume this new user ID |
  |                     |        '-------------------------'
  |                     |                     |
  |                     |                     |
  |                     |            .-----------------.
  '---------------------'------------| Allow the login |
                                     '-----------------'

The error codes referenced in the flowchart expand as follows:

  • AM004F Authentication succeeded but you are not authorised to log on as this user

  • AM0050 Not authorised to log in without a pre-existing username

  • AM0053 Automatically created user is not a member of an external group with permission to log in

More background references on Kerberos

Useful links on setting up and using Kerberos: