JDBC authentication AuthState

Introduction and overview

The JDBC authentication AuthState (JDBCAuthState) is designed to authenticate and authorize a user and/or to fetch user properties from SQL databases. It uses the JDBC interface to establish connections. A JDBC driver for the corresponding database must be installed in the "lib" extension directory of the nevisAuth instance. The individual SQL statements are configured as templates and prepared statements are used to avoid SQL injection and ensure maximal security.

Description

The following table and chapters describe the characteristics of the AuthState.

Topic	Description
Class	ch.nevis.esauth.auth.states.jdbc.JDBCAuthState
Logging	JDBCState
Auditing	none
Marker	JDBC:username/password
Methods	process (all events)

Properties

• url (string, -)

  This string is used to configure the Oracle/MSSQL/MySQL JDBC connection string as supported by the configured JDBC driver.

  Example

  jdbc:oracle:thin:@<host>:<port>:<DB-name>

• user (string, -)

  Database username used to log in to the database.

• password (string, -)

  Password of the database user. The password should be obfuscated.

• column.name.userid (string, -)

  If an authentication query template is defined, this property sets the column name to extract the user ID.

• column.name.password (string, -)

  If an authentication query template is defined, this property sets the column name to extract the password.

• column.name.roles (string, -)

  If an authorization query template is defined, this property sets the column name to extract the roles.

• query.template.authentication (string, -)

  Query template for authentication. Authentication fails if the result set is empty.

• query.template.authentication.argN (string, -)

  N ("argN") is the number of the argument starting from 1.

  Defines the arguments to be used in the prepared SQL query for authentication. The number N defines the order of the argument.

• query.template.authorization (string, -)

  Query template for authorization, result set must contain the roles column.

• query.template.authorization.argN (string, -)

  N ("argN") is the number of the argument starting from 1.

  Defines the arguments to be used in the prepared SQL statement. The number N defines the order of the argument.

• stmt.template.authenticationFailed (string, -)

  Statement template that will be executed if authentication failed. E.g., for auditing or account locking.

• stmt.template.authenticationFailed.argN (string, -)

  N ("argN") is the number of the argument starting from 1.

  Define values for prepared statement used as authentication failed statement.

• stmt.template.authenticationSucceeded (string, -)

  Statement template that will be executed if authentication succeeded. E.g., for auditing or automatic account mutation.

• stmt.template.authenticationSucceeded.argN (string, -)

  N ("argN") is the number of the argument starting from 1.

  Defines values for prepared statement used as authentication succeeded statement.

• stmt.template.logout (string, -)

  Statement template that will be executed at logout. E.g., for auditing or mutation of special accounts.

• stmt.template.logout.argN (string, -)

  N ("argN") is the number of the argument starting from 1.

  Define values for prepared statement used as logout statement.

• propagationScope (string, "notes")

  Defines propagation scope to store column values of the result set of the authentication statement.

• noPostprocessingMissingUser (string, "true")

  Avoids post-processing if a user is not found in the database, e.g., because the user name was misspelled. The goal is to avoid executing the authentication failed statement.

• login.password.src (string, -)

  Defines the source for the login password.

• authentication.checker.impl (string, "StringPasswordChecker")

  Defines a custom implementation of the JDBCAuthenticationChecker interface. This interface is used to verify the correctness of the password. If no package name is set, nevisAuth loads the class from the package ch.nevis.esauth.auth.states.jdbc.

  Available authentication checkers are:

  • StringPasswordChecker

    Performs a simple string comparison between the supplied and the stored password

  • BinaryHashPasswordChecker

    The "BinaryHashPasswordChecker" creates a hash based on the supplied password and an optional salt. It then compares the hash value to the value stored in the database. The checker identifies the salt at the start of the stored password hash (if one exists) through the saltLength property. See further on in this table for more information about all properties related to the "BinaryHashPasswordChecker".

• connectionPoolName (string, <user>@<connection-url>)

  If you give this property the same value in several AuthStates, these AuthStates share a common connection pool.

BinaryHashPasswordChecker only

• hashAlgorithm (string, "SHA-1")

  Defines the hash algorithm used to hash the login password (login.password.src property). Available algorithms are: "MD5", "SHA-1", "SHA-256".

  For more details, see the property authentication.checker.impl.

• saltLength (number, 0)

  The length of the salt used to hash the login password.

  For more details, see the property authentication.checker.impl.

Oracle only

• maxCachedConnections (number, 10)

  Connection pool size to restrict number of network connections to the database.

• minCachedConnections (number, 3)

  Minimal connection pool size.

• connectionWaitTimeout (number, 10)

  Time-out settings for the connection pool, in seconds.

• connectionInactivityTimeout (number, 0)

  Time-out settings for the connection pool, in seconds.

• connectionTimeToLiveTimeout (number, 0)

  Time-out settings for the connection pool, in seconds.

MySQL Only

• connectionTimeoutInMillis (number, 1000)

  The connection time-out in milliseconds.

• maximumPoolSize (number, 10)

  The maximum size of the given connection pool.

Input

none

Transitions

• ok

  JDBC user ID/password authentication was successful.

Output

All fields of the result set of the authentication query are stored as "outargs"/"notes"/"session" (according to the selected propagation scope in the propagationScope property), with the prefix "jdbc.". The value is an object or string depending on the type of the column.

Errors

• lasterror=1

  lasterrorinfo=Login failed

Notes

none

Example

<AuthState name="TestJDBCAuthenticateState" class="ch.nevis.esauth.auth.states.jdbc.JDBCAuthState" final="false">
    <ResultCond name="ok" next="AuthDone"/>
    <Response value="AUTH_ERROR">
        <Gui name="AuthErrorDialog"/>
    </Response>
    <property name="url" value="jdbc:oracle:thin:@fulvia:49185:ESAU"/>
    <property name="user" value="system"/>
    <property name="password" value="secret:5+1BzswwozqKNfsHHMLeaMMC/uSUDMHG"/>
    <property name="column.name.userid" value="USER_ID"/>
    <property name="column.name.roles" value="ROLE_NAME"/>
    <property name="column.name.password" value="PASSWORD"/>
    <property name="query.template.authentication" value="SELECT * FROM TMUSER.SMUSERS WHERE NAME = ?"/>
    <property name="query.template.authentication.arg1" value="${inargs:isiwebuserid}"/>
    <property name="query.template.authorization"
            value="SELECT ROLES.ROLE_NAME FROM TMUSER.ROLES, TMUSER.ROLE_ASSIGNMENTS WHERE ROLE_ASSIGNMENTS.USER_ID = ? AND ROLES.ROLE_ID = ROLE_ASSIGNMENTS.ROLE_ID"/>
    <property name="query.template.authorization.arg1" value="${notes:jdbc.USER_ID}"/>
    <property name="stmt.template.authenticationFailed" value="INSERT INTO TMUSER.AUDIT_EVENT (USER_ID, EVENT) VALUES (?,'auth_failed')"/>
    <property name="stmt.template.authenticationFailed.arg1" value="${inargs:isiwebuserid}"/>
    <property name="stmt.template.authenticationSucceeded" value="INSERT INTO TMUSER.AUDIT_EVENT (USER_ID, EVENT) VALUES (?,'auth_succeeded')"/>
    <property name="stmt.template.authenticationSucceeded.arg1" value="${response:userId}"/>
    <property name="stmt.template.logout" value="INSERT INTO TMUSER.AUDIT_EVENT (USER_ID, EVENT) VALUES (?, 'logout')"/>
    <property name="stmt.template.logout.arg1" value="${response:userId}"/>
    <property name="login.password.src" value="${inargs:isiwebpasswd}"/>
    <property name="propagationScope" value="notes"/>
</AuthState>

Setup Oracle / Microsoft SQL Server driver

The Oracle and Microsoft SQL Server drivers (jar) are no longer bundled into the application. To prepare them manually, perform the following steps:

1. Download the appropriate drivers.
2. For the Oracle driver, go to the Oracle JDBC driver download page and download ojdbc8-<version>.jar.Use driver version 19.x or below, as version 21.x is not compatible.
3. For the Microsoft SQL Server driver, go to the MSSQL driver download page and download mssql-jdbc-<version>.jre8.jar.
4. Copy the downloaded driver to the newly created lib folder (/var/opt/nevisauth/<instance>/lib).
5. Restart nevisAuth.