Adding connections from existing data source connections to Watson™ Query by using personal credentials

When pass-through authentication is enabled, the security settings that are in place on the data source are passed through to virtualized data in Watson Query. You can use your personal credentials that are associated with platform connections when you add and use connections from the existing data source connections in Watson Query.

Tech preview This is a technology preview and is not supported for use in production environments.

Pass-through authentication is enabled by default. When pass-through authentication is enabled, a user can add a connection and define it so that when a virtual table is queried, the user’s personal credentials, as defined in the platform connection, are passed through to authenticate with the data source.

When pass-through authentication is disabled, the shared credentials for one account are used for every query, regardless of which user is connected and running the query. When a user creates a platform connection that uses personal credentials and then adds it to Watson Query, all users connect to the data source by using the credentials of that user because they added the connection.

Important:

When pass-through authentication is enabled, if a cache is created on a virtual object, the data is placed within the Watson Query access and governance model and bypasses any controls that might be implemented on the data source. To protect data properly, administrators must ensure that appropriate access lists and governance policies are defined on the virtual objects within Watson Query and the governed catalog before they create a cache.

Example with pass-through authentication enabled: Gina is a Watson Query Admin user who adds a Watson Query connection to Db2® for z/OS®. To add this connection, Gina needs to use personal credentials that provide access to tables at the data source. After Gina adds the connection, Lee, a Watson Query engineer, virtualizes data in the source tables that Gina's personal credentials have access to. Kyle, a Watson Query user, then uses his own personal credentials, which he set up in platform connections, to add and use the virtualizations that Lee created.

Example with pass-through authentication disabled: Gina is a Watson Query Admin user who adds a Watson Query connection to Db2 for z/OS. To add this connection, Gina needs to use personal credentials that provide access to tables at the data source. After Gina adds the connection, Lee, a Watson Query engineer, virtualizes data in the source tables that Gina's personal credentials have access to. Kyle, a Watson Query user, then uses Gina's credentials to use the virtualizations that Lee created.

Before you begin

If you plan to query tables for a data source by using personal credentials, ensure that the following requirements are met:

Pass-through authentication must be enabled by an administrator. For more information, see Enabling and disabling pass-through authentication in Watson Query.
You must have access to the data source in platform connections so that you can add your credentials to the data source. For more information, see Managing collaborators on platform connections.
The connection must first be created in platform connections. Make sure to select Personal as the type of credential and add your credentials to the connection. For more information, see Connecting to data sources.

Gather the following information that you need when you run the command to set the configuration options and add the connection:

Hostname

The data source hostname.

Port

The network port of the data source service.

Database Name

The name of the database for the connection.

Connection Options

Specifies a comma-delimited list of extra options in the following format: '<key1>=<value1>, <key2>=<value2>, ...'.

User

The data source username. The username and password should have higher authority in the data source that you are connecting to. The username and password are used in operations such as getting database metadata or gathering statistics from a catalog or by a query.

Password

The password for the specified user.

SSL

Specifies whether the database connection uses SSL. Valid values are 0 (the connection does not use SSL) and 1 (the connection uses SSL).

Node Name

The node that you are adding the data source to. To find valid node names, run the following query:

SELECT * FROM DVSYS.LISTNODES;

Note: Adding too many data sources to the same node impacts performance. To check which nodes already have associated data sources, run the following query:

SELECT * FROM DVSYS.LISTRDBC;

CCID

The unique identifier of the platform connection. To find this identifier, go to the Platform connections page and view the value in the Name field in the Connection overview section.

CID

The unique identifier of the data source connection. Run the following command to get the cid from the dvsys.rdb_connections table:

select cid, url from dvsys.rdb_connections;

You can also find this identifier in the UI. Go to Data > Data virtualization and expand the connection on the Data sources page to see the CID.

CCDEFINER

The platform’s unique identifier for a user. This identifier is the same as the one that is used to identify the user that is adding the connection to Watson Query. This user is also the user who created the connection in platform connections and whose credentials to the data source are being passed in the User and Password parameters. To find a user’s unique identifier, go to Administration > Access Control. The identifier is the value in the User ID column.

Adding connections to Watson Query by using personal credentials

To add the connection, set the configuration options on the Watson Query connection by running the setrdbcx SQL command in the Run SQL interface, as shown in the following example:

call dvsys.setrdbcx('DB2','9.30.167.128',50000,'SAMPLE','','<user>','<password>',0,0,'','','qpendpoint_1:6415','CCID=611335a2-164b-4873-be5e-074989d12dee,CCDEFINER=1000330999,USE_PERSONAL_CREDENTIALS=TRUE',?,?,?)

For more information about the setrdbcx stored procedure, see setRdbcX stored procedure (Variation 1) or setRdbcX stored procedure (Variation 2).

Editing connections that are added to Watson Query by using personal credentials

After you add a data source connection to Watson Query by using personal credentials, do not edit the data source connection in Watson Query user interface because the OPTIONS_CSV will be overwritten. To edit a connection that you added by using the setrdbcx, use the additional option EDITCID=CID (Connection ID) of the data source to be edited, as shown in the following example:

dvsys.setrdbcx('DB2','9.30.167.128',50000,'SAMPLE','','db2inst1','db2inst1',0,0,'','','qpendpoint_1:6415','CCID=611335a2-164b-4873-be5e-074989d12dee,CCDEFINER=1000330999,USE_PERSONAL_CREDENTIALS=TRUE,EDITCID=DB210000',?,?,?)

For more information about the setrdbcx stored procedure, see setRdbcX stored procedure (Variation 1) or setRdbcX stored procedure (Variation 2).