Exasol OpenID Connect support

Detail name	Value
Changelog Number	9170
Type	New Feature
Status	Resolved
Fix Versions	Exasol 7.1.0
Resolution Date	2021-08-03

New Feature 

Exasol 7.1 supports authenticating database clients using OpenID Connect.
Exasol acts as a resource server which authenticates database connections opened by Exasol ODBC, JDBC, ADO.NET or WebSockets clients using OpenID/OAuth access token or refresh token.

Details

The implementation follows the OpenID Connect Core 1.0 specification as well as the internet standards RFC 6749 (The OAuth 2.0 Authorization Framework), RFC 7517 (JSON Web Key) and RFC 7519 (JSON Web Token). In the supported scenarios Exasol is able to act as a resource server which authenticates database connections opened by Exasol ODBC, JDBC, ADO.NET or WebSockets clients using OpenID/OAuth access token or refresh token.

Once a database client sends an access token for authentication, Exasol will validate the access token against the set of JSON Web Key fetched from JKU. If the provided access token is signed with none of those keys, the authentication will fail.

If a database clients sends a refresh token for authentication, Exasol will perform the Refresh Token Flow with the OpenID Token Endpoint on behalf of the user. The returned access token is then validated against the set of JSON Web Key fetched from JKU. Neither the access token nor the refresh token are retained by Exasol.

Configuration (access token)

Command line parameter	Description
-oidcProviderISS	OpenID Provider Issuer
-oidcProviderJKU	OpenID Provider endpoint to retrieve the JSON Web Key Set (JWKS) used for signing JWS tokens

Configuration (additional for refresh token)

Command line parameter	Description
-oidcProviderTokenEndpoint	OpenID Provider endpoint to retrieve OAuth 2.0 and OpenID Connect tokens
-oidcProviderClientId	OpenID Client ID registered with OpenID Provider
-oidcProviderClientSecret	OpenID Client Secret of the OpenID Client registered with OpenID Provider Remains unset if Proof Key for Code Exchange (PKCE) is enabled
-oidcProxy	Proxy URL to be used for connections to OpenID Provider endpoints (Schemes supported: http, https, socks4, socks4a, socks5, socks5h)

Configuration (optional parameters)

Command line parameter	Description
-oidcJKUFetchInterval	Interval in which Exasol will fetch new signing keys from OpenID Provider JWKS in seconds Default: 86400 seconds (24h)
-oidcJKUFetchRetryInterval	Interval in which Exasol will retry fetching new signing keys from OpenID Provider JWKS in case of errors in seconds Default: 300 seconds (5 minutes)
-oidcRefreshTokenCacheCapacity	Capacity of the OpenID Refresh Token-based authentication information cache Default: 10000
-oidcRefreshTokenCacheCompactionInterval	Interval in which Exasol will perform maintenance procedures for the OpenID Refresh Token-based authentication information cache in seconds Default: 3600 seconds (1h)

In-database setup

Database users that should be enabled to login via OpenID-based authentication, must be altered such that they are identified by the respective OpenID Subject.

CREATE USER oidctestuser IDENTIFIED BY OPENID SUBJECT 'db-test-user@exasol.example';
GRANT CREATE SESSION TO oidctestuser;

 

ALTER USER oidctestuser IDENTIFIED BY OPENID SUBJECT 'db-test-user@exasol.example';

 

Client setup

Set authentication method via connection string

Authentication via OpenID access or refresh token is configured in the Exasol drivers using the connection string parameter "authmethod". With this parameter being set the access or refresh token must be passed as a password to the client. The username does not need to be provided and will be ignored by Exasol.

Connect via JDBC:

• Connection String: "jdbc:exa:<host>:<port>;authmethod=accesstoken"
• Password: "<access_token>"

and

• Connection String: "jdbc:exa:<host>:<port>;authmethod=refreshtoken"
• Password: "<refresh_token>"

Connect via ODBC:

• Connection String: "EXAHOST=<host>:<port>;AUTHMETHOD=accesstoken;EXAPWD=<access_token>"

and

• Connection String: "EXAHOST= <host>:<port>;AUTHMETHOD=refreshtoken;EXAPWD=<refresh_token>"

 
Connect via ADO.NET:

• Connection String: "Server=<host>;Port=<port>;AuthMethod=accesstoken;PWD=<access_token>"

and

• Connection String: "Server=<host>;Port=<port>;AuthMethod=refreshtoken;PWD=<refresh_token>"

 
Connect via WebSockets in Python:

import websocket
import json
 
ws = websocket.WebSocket(sslopt={"cert_reqs": ssl.CERT_NONE})
ws.connect('wss://<host>:<port>')
ws.send(json.dumps({'command': 'loginToken', 'protocolVersion': 3}))
assert(json.loads(ws.recv())['status'] == 'ok')
ws.send(json.dumps({'useCompression': False, 'accessToken': '<access_token>'}))
assert(json.loads(ws.recv())['status'] == 'ok')
ws.send(json.dumps({'command': 'disconnect'}))
assert(json.loads(ws.recv())['status'] == 'ok')

Compatibility mode for refresh token-based authentication

If it is not possible to amend connection string parameters, Exasol provides a compatibility mode. As such Exasol interprets provided passwords for users identified by OpenID Subject as a refresh token, if the database username is given.

Connect via JDBC:

• Connection String: "jdbc:exa: <host>:<port>"
• Username: "<db_username>" (e.g. oidctestuser as per the example above)
• Password: "<refresh_token>"

Connect via ODBC:

• Connection String: "EXAHOST= <host>:<port>;UID=<db_username>;EXAPWD=<refresh_token>"

Connect via ADO.NET:

• Connection String: "Server= <host>;Port=<port>;UID=<db_username>;PWD=<refresh_token>"