Configuring Direct SQL Query Actions

You can perform Direct SQL queries on databases using their respective query syntax to service privacy requests with OneTrust Targeted Data Discovery (TDD). This allows OneTrust customers to integrate systems that use JDBC driver support and perform other actions with that data in one central location.

Direct SQL query actions are supported in OneTrust integration workflows for following database systems:

  • MS SQL Server

  • MySQL

  • PostgreSQL

  • SAP HANA

  • Oracle RDBMS

  • Snowflake

Important:

OneTrust Integrations only supports simple queries. Custom stored procedures are not currently supported. If an advanced query is performed, an error message will be thrown. For example:

CODE 
"[CALL ADMIN] is Not A Simple : [SELECT, UPDATE, DELETE, INSERT, MERGE, OT_TAG_SYNC_FUNC] Query"

To create the database credentials

For more information, see Configuring Integration Credentials.

  1. On the Integrations menu, select Credentials.

    The Credentials Name screen appears.

  2. Click the Add New button.

    The Select System screen appears.

  3. Enter your system database, then click the Next button.
    Note:

    These instructions will use SAP HANA as the example database.



    The Enter Credential Details screen appears.

  4. Complete the fields, then click the Save button.


    Field
    Description
    Credential Name

    Enter a name to identify the credentials.

    Description

    Enter a description of the credentials.

    Connector Type

    Select Database as the connector type.

    Database Type

    Select the available database type.

    Database Host

    Enter the fully qualified domain name (FQDN) or IP address of your database server/host.

    Username

    Enter a username.

    Password

    Enter a password.

    Database Name

    Enter the name your organization has set for the database.

    Database Port

    Enter the database port.

To create a new workflow

  1. On the Integrations menu, select Integrations.

    The Integrations screen appears.

  2. Click the Add New button.

    The Create New Workflow modal appears.

  3. Create a new workflow for the system.
  4. Add and configure the initial trigger. For TDD, you would use a Data Subject Request System Subtask Created trigger.
    Note:

    For webhook triggers, you can provide the sample that should be retrieved from the database when the integration is triggered.

  5. Click the Add Action to add the Direct SQL Query action from the System tab.


  6. Select your newly created database credentials for the given system on the action's Account Credentials.


  7. On the Request tab, edit the Query body as necessary.
    Note:

    In this example :?email is the placeholder variable that will be replaced with the input value using the syntax specific to SAP HANA. You will need to add/modify as needed to create the appropriate query for the given database.



  8. Click the Generate Schema button. This will query the database and populate the field value titled for the given parameter in the Response tab.


  9. Prior to validating, users should enter an actual value (i.e., email address) found in their database to replace "Some Value".
  10. Click the Validate button. If the query is successful, the sample will generate on the Response tab and the schema on the Request tab.
    Note:

    Ensure the query syntax is correct for SAP HANA or the respective database syntax required for this integration.

    Click the Edit button to modify the Query body as necessary.



    You'll also need to select the value from the prior action the integration will reference once it's validated in the Request tab.



  11. Click the Finish button.
  12. Add a following POST Targeted Data Discovery Summary Results action, and modify as necessary.


  13. Click the Finish button.
  14. Create another PUT Updates an existing Sub-task to complete action, and modify as necessary.


  15. Click the Finish button.
  16. Click the Save button.
  17. Click the Activate button to activate the workflow.