Goal
This document describes how to connect to the Denodo Platform through JDBC using OAuth Authentication.
Content
Starting from Denodo Platform 8.0 Update 20210209, the Denodo JDBC driver supports the OAuth 2.0 protocol for authentication and authorization of users. OAuth is an open-standard protocol that allows client applications to access Denodo without sending the password of the user account. The Denodo JDBC driver can connect to the Denodo Platform using OAuth in two ways:
- Authentication providing access token:
The client application obtains an OAuth access token and passes it to the driver. Then, the driver uses this token to connect to Virtual DataPort instead of using user/password.
- Using Resource Owner Password Credentials:
If the client application does not have the capability of obtaining OAuth tokens, the driver can obtain one on behalf of the application. That is, when the application opens the connection, the driver will connect to the Identity Provider indicated to retrieve an OAuth token. Then the driver will use this token to connect to Virtual DataPort.
Before using OAuth protocol to connect to the Virtual DataPort Server (VDP), make sure that OAuth Authentication is enabled in Virtual DataPort. Otherwise, the authentication will fail. To enable OAuth, you can use any Identity Providers (IDP) such as Okta, PingFederate, or any other IDP. For more information on this, refer to the following Knowledge Base Articles:
- How To Configure Okta for Single Sign-On in Denodo Solution Manager
- How To Configure PingFederate for SSO in Denodo Solution Manager 8.0
- How To Configure Keycloak for SSO in the Denodo Solution Manager
Note : To utilize this feature, the driver with update 20210209 or newer has to be used.
Authentication providing access token
In this approach, the JDBC client application obtains an OAuth access token and passes it to the Denodo JDBC driver. Then, the JDBC driver uses this token to connect to Virtual DataPort.
This approach is suitable for the client applications that have the ability to obtain access tokens to connect to Virtual DataPort. To explore the JDBC connectivity using OAuth, we will use DBVisualizer as an example of client application.
Connecting to Denodo Platform from DBVisualizer
To use the JDBC driver in your client, you have to add the jar driver file to the classpath of your application.
In DBVisualizer, go to the “Tools > Driver Manager…” section. Here, in the Driver Manager window, go to Driver > Create User Driver from Template.
As there is no template dedicated to Denodo, choose a custom template.
Next, browse to the VDP's JDBC driver denodo-vdp-jdbcdriver location. For instance, it can be found under the <DENODO_HOME>/tools/client-drivers/jdbc folder of a Denodo Platform installation.
On doing this, you can see the driver is loaded and the driver class is automatically updated. Once done, specify the other details for this driver such as name and JDBC URL as shown below:
After this, close the window to save the configuration.
Now, we need to create a database connection to the Virtual DataPort server. For doing so, please choose the Database > Create New Database option.
On the Database connection window, specify the following parameters for the connection: Make sure to add the correct host and port based on your setup
- Database Name : Denodo9
- Driver (JDBC): Denodo 9.0
- Database URL: jdbc:denodo://localhost:9999/admin
Leave the username and password field empty as we are not going to use this Authentication.
To use the OAuth Authentication Access Token method, you can either add the OAuth parameters to the Connection URL or add the parameters as part of the Driver properties.
Including OAuth parameters in Driver properties
To add the OAuth parameters to the Driver properties click on the Properties tab and select the Driver properties.
Here, you can see many parameters related to OAuth. But, for OAuth 2.0 Authentication we just need to update two parameters called accessToken and useOAuth2.
To get the access token, you can use the OAuth credentials wizards from the Virtual Dataport Administration Tool or any API clients such as Postman. For more information, you can refer to the How to get the OAuth access token to connect to Denodo Knowledge Base Article.
We will set the values for the property useOAuth2 as true and then for the property accessToken, we need to specify the Token that is required to access the Virtual DataPort Server.
On doing so, you can click on Apply at the bottom to save these driver properties.
Finally, go to the Connection tab and click on Connect to see if the connection is successful. On successful connection, you can find the Connection Message like as shown in the screen shot :
Including the OAuth Parameters in Connection URL
You can also use the OAuth parameters directly in the Connection URL instead of Driver Properties. For that, you can perform the following steps :
- In the Connection tab, set the Connection URL to include the OAuth parameters useOAuth2 and accessToken with their corresponding values like this:
jdbc:denodo://<host_name>:9999/admin?useOAuth2=true&accessToken=<token> |
- Click on connect to establish connection to the Virtual DataPort server using OAuth Authentication.
OAuth 2.0 using Resource Owner Password Credentials
As an alternative method for OAuth Authentication we can use Resource Owner Password Credentials. In this approach, the driver can obtain access tokens on behalf of the application, if the Identity Provider used supports the Resource Owner Password Credentials OAuth flow.
This approach can be used when the client application does not have the ability of obtaining OAuth tokens.
For this method, we need to add the URL parameters that we will be obtaining from the OAuth Application configured in the Identity Provider to the JDBC connection URL. You can find the list of necessary parameters and its meaning below :
- useOAuth2: true
- user: The username used for connecting to Identity Provider to obtain an access token.
- password: The password corresponding to the user that will be authenticated.
- tokenEndpoint: URL exposed by the OAuth server to request the access token.
- clientId: application’s Client ID. Usually, you obtain this when registering the client application in the Identity Provider.
- clientSecret: Application’s Client secret.
- scope: String value of the different scopes requested
- useIdToken: If true, the driver will use the “id_token” for authentication. If it is false, it uses “access_token”. Set this to true in an environment with “OpenID Connect”. The default value is false
- oauthSSLVerify: If true the driver will validate the SSL certificate of the Identity Provider. If false, it will not validate the certificate. The default value is true.
- extraParams - In case you have any additional parameters to be added to the body of the HTTP request, you can specify the extraParams property in the Driver Properties. So that the driver will send this to obtain the OAuth tokens.
The syntax of this parameter is: param1=value1¶m2=value2&...
Creating a Native Application in Okta for Resource Owner Password grant
Let us quickly use any Identity Provider as an example and identify the values for the above five parameters such as user, password, clientId, clientSecret and scope. Note that you can use any Identity Provider of your own but for this example, let us assume Okta as our Identity Provider and get the necessary details.
You can refer to the How To Configure Okta for Single Sign-On in Denodo Solution Manager for performing all the steps in the Okta Configuration section such as accessing Okta, creating new users and also groups.
For using Resource Owner Password Credentials OAuth flow with the Okta Identity Provider, the Client ID and Client Secret has to be obtained from Native Application as this OAuth flow is not supported by Web Application.
To create a Native Application, you can navigate to the Applications tab and select the Create App Integration option.
Here, you can find the list of Applications which you can create in Okta. Choose the Application as Open ID connect and Application Type as Native Application.
After this, you can click on the Next option. On doing so, you will get a new window for Native application details. Here, you can give the name for this application and then in the Grant type section, ensure that you check the Resource Owner Password option.
Once done, click save to create the new application.
Obtaining the parameter values
Now that we have the Application for using the resource owner grant created in Okta, you can obtain the values necessary for the URL parameters for OAuth Authentication.
In configuring OAuth in the Denodo JDBC connection URL, the first two parameters are user and password. For these parameters, you could use the username and password with which you logged into the Okta Identity Provider or the OKTA credentials that have access to this new application.
Then, for the next two parameters clientID and clientSecret, you can open this Application and get the values under the Client Credentials section of the General tab.
Lastly, for the remaining parameters tokenEndpoint and scope, you need to navigate to the
Default Authorization Server Metadata which can be found in the “Security > API” section.
Here, click on the default option to see the Metadata URI of the Authorization server.
On launching the Metadata URI in a browser, you can view JSON metadata of the service. It contains the token_endpoint and scopes_supported. You can use this endpoint url and specify the scope for obtaining the access token for OAuth authentication.
Applying the Resource Owner Password Credentials approach in DBVisualizer
In DBVisualizer, you have the option to either configure the values for each of the mandatory fields in the Driver properties or input them directly into the Connection URL, as illustrated below:
After adding all the parameters, the jdbc url looks like:
- jdbc:denodo://<hostname>:9999/admin?useOAuth2=true&user=<user_name>&password=<password>&tokenEndpoint=<token_endpoint_url>&clientId=<client_id>&clientSecret=<client_Secret>&scope=<scope>&useIdToken=false&oauthSSLVerify=true
Or, by modifying directly the parameters values inside the Driver properties,like below screenshot:
The connection will then look like:
After successful connection, you could click on the expand icon next to the database connection on the Databases tab. On doing so, you could query the list of tables and views available in the Virtual DataPort Server.
References
Virtual DataPort Developer Guide - Access through JDBC
Virtual DataPort Developer Guide - Connecting with JDBC and ODBC with OAuth Authentication
Virtual DataPort Developer Guide - Connecting to Virtual DataPort Using OAuth Authentication
The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.
For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.