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, currently Oracle, MS SQL and MySQL databases are supported. A JDBC driver for the corresponding database must be installed in the "lib" extension directory of the nevisAuth instance. The in
Description
The following table describes the characteristics of the AuthState.
| Topic | Description |
|---|---|
| Class | ch.nevis.esauth.auth.states.jdbc.JDBCAuthState |
| Logging | JDBCState |
| Auditing | none |
| Marker | JDBC:username/password |
| 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 user name used to log in to the database. | |
| password (string, -)Password of the database user. The password should be obfuscated, using "nevisauth encSecret". | |
| 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. Defines 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. Defines 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. | |
| Properties(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".(info) For more details, see the property authentication.checker.impl. |
| saltLength (number, 0)The length of the salt used to hash the login password.(info) For more details, see the property authentication.checker.impl. | |
| Properties(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) connectionInactivityTimeout (number, 0)connectionTimeToLiveTimeout (number, 0)Time-out settings for the connection pool, in seconds. | |
| Properties(MySQL only) | connectionTimeoutInMillis (number, 1000)The connection time-out in milliseconds. |
| maximumPoolSize (number, 10)The maximum size of the given connection pool. | |
| Methods | process (all events) |
| 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 | 1: 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:
- Download the appropriate drivers.
- For the Oracle driver, go to the(https://www.oracle.com/database/technologies/jdbc-ucp-122-downloads.html) and download ojdbc8-
<version>.jar. Use driver version 19.x or below, as version 21.x is not compatible. - For the Microsoft SQL Server driver, go to the(https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15) and download mssql-jdbc-
<version>.jre8.jar**. - Copy the downloaded driver to the newly created lib folder (/var/opt/nevisauth/
<instance>/lib). - Restart nevisAuth.