SQL approval integration

Background information

ODC V4.2.0 has a built-in SQL check process that implements database security control in team collaboration scenarios. The SQL check specifications are environment specific. That is, the data sources in the same environment are checked against the same set of SQL check specifications.

ODC V4.2.0 allows system administrators and users with external integration management permissions to configure integrations with external SQL review systems. You can reference the integration of an external SQL approval system on the SQL window specification tab. If the integration of the external SQL approval system is enabled on the SQL window specification tab, ODC calls APIs of the external SQL approval system for SQL statement check when SQL statements are executed in the SQL window.

This topic describes how to configure and manage SQL approval integration.

Execution process

1. The ODC system administrator enables SQL approval integration and sets the API information for accessing the external SQL approval system based on the configuration template provided by ODC, and creates the SQL approval integration configuration.

2. The ODC system administrator enables SQL approval integration on the SQL window specification tab.

3. An ODC user executes an SQL statement in the SQL window. ODC calls the API of the external SQL approval system to initiate an SQL check request.

Configuration

Configuration method

Configuration template

# This is a template for integrating a custom SQL interceptor system.
# You must fill in the required fields according to the requirements to adapt to your SQL interceptor system.


# HTTP configurations
http:
  # HTTP connection timeout in seconds
  connectTimeoutSeconds: 5
  # HTTP socket timeout in seconds
  socketTimeoutSeconds: 30

# API configurations
api:
  # API configurations used to check the SQL content
  check:
    # The request method. Valid values: GET, POST, PUT, and PATCH.
    method: ~
    # Request URI
    url: ~
    # The request header in the <key: value> format.
    headers:
      # Sample of request headers
      Content-Type: application/json;charset=UTF-8
      Accept: application/json
    # Request query parameters in the <key: value> format.
    queryParameters: ~
    # Request body
    body:
      # The type of request body. Valid values: FORM_DATA and RAW.
      type: RAW
      # The content of request body in the <String> or <key: value> format. <String> is supported for the RAW type and <key: value> is supported for the FORM_DATA type.
      # Sample of request content in the RAW type
      content: |-
        {
          "sqlStatement":"${sql.content}"
        }
    # Mark whether the request body is encrypted
    requestEncrypted: false
    # Expression to judge whether the request is successful based on the response body analysis, using the SPEL syntax
    requestSuccessExpression: '[resultCode] == 0'
    # Mark whether the response body is encrypted
    responseEncrypted: false
    # Expression to judge whether the SQL to be executed is in the allowlist based on the response body analysis, using the SPEL syntax
    inWhiteListExpression: '[checkResult] == 1'
    # Expression to judge whether the SQL to be executed is in the blocklist based on the response body analysis, using the SPEL syntax
    inBlackListExpression: '[checkResult] == 2'
    # Expression to judge whether the SQL to be executed need review based on the response body analysis, using the SPEL syntax
    needReviewExpression: '[checkResult] == 3'

Prerequisites

You are the system administrator or have the external integration management permissions.

Create SQL approval integration

1. In the project collaboration window, choose External Integration > SQL Approval Integration > Create SQL Approval Integration.

2. In the Create Approval Integration panel, specify the following parameters.

   Parameter	Description
   Name	The name of the SQL approval integration configuration.
   Status	Specifies whether to enable the SQL approval integration configuration.
   Settings	Edit the YAML file online to configure approval integration. For more information, see Configuration.

3. Click Save. The SQL approval integration configuration is created.

4. In the SQL approval integration list, you can enable, view, edit, and delete the SQL approval integration settings.

5. Reference the SQL approval integration configuration on the SQL window specification tab.

Manage SQL approval integration configurations

In the SQL approval integration list, click View, Edit, or Delete in the Actions column to view, edit, or delete information about the SQL approval integration configuration.

References

• SQL window specification

• Approval integration

• Risk management