Details

    • Type: New Feature
    • Status: Resolved
    • Priority: Normal
    • Resolution: Fixed
    • Component/s: None
    • Labels:
      None

      Description

      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>"

       

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                CaptainEXA Captain EXASOL
                Reporter:
                CaptainEXA Captain EXASOL
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: