Data Management:Access a database instance over HTTPS

Prerequisites

• The secure access proxy feature is enabled for the database instance.

  Note

  To check whether the secure access proxy feature is enabled for a database instance, perform the following steps: Log on to the Data Management (DMS) console. In the top navigation bar, choose Security and Specifications > Secure Access Proxy > Proxy List. On the Created tab of the Proxy List page, the instances for which the secure access proxy feature is enabled are displayed. For more information about how to enable this feature for a database instance, see Enable the secure access proxy feature.

• You are authorized to access the database instance by using proxy endpoints.

  Note

  On the Details page of a database instance, check whether you are an authorized user of the database instance. If you are not an authorized user, apply for the permissions to use proxy endpoints to access the database instance. For more information, see Apply for the permissions to access a database instance by using proxy endpoints.

Usage notes

If your database instance is managed in Security Collaboration mode, it is affected by security rules. A maximum of 100,000 rows can be returned for each query.

Request parameters

Response parameters

If you access the instance over HTTPS, data is returned in the JSON format.

The following table describes the response parameters in the JSON format.

Sample responses:

• Successful data query

  {
    "columnMetas": [
      {
        "columnName":"column1",
        "columnLabel":"column1",
        "columnTypeName":"varchar",
        "precision":10,
        "scale":2,
        "nullable":true,
        "autoIncrement":true,
        "tableName":"table1"
      },
      {
        "columnName":"column2",
        "columnLabel":"column2",
        "columnTypeName":"varchar",
        "precision":10,
        "scale":2,
        "nullable":true,
        "autoIncrement":true,
        "tableName":"table1"
      } 
    ],
    "updateCount": 0,
    "requestId": "xhqej0xgcytbhc8scjopgqsywcaibi",
    "rowCount": 1,
    "rows": [
      {
        "col1": 1,
        "col2": "xxxx"
      }
    ],
    "success": true
  }

• Successful data update

  {
    "updateCount": 0,
    "requestId": "xhqej0xgcytbhc8scjopgqsywcaibi",
    "success": true
  }

• Access failure

  {
    "message": 'AccessID is required.',
    "requestId": "xhqej0xgcytbhc8scjopgqsywcaibi",
    "success": false
  }

Examples of accessing a database instance for which the secure access proxy feature is enabled

You can use commands, an SQL client, or program code to access a database instance for which the secure access proxy feature is enabled.

For example, the domain name of the instance is dpxxxx-xxxxxxxx.proxy.dms.aliyuncs.com, the name of the database to be accessed is database, the AccessKey ID is user, the AccessKey secret is pwd, and the SQL statement to be executed is SHOW DATABASES.

• Use the curl command

  # GET request syntax
  curl 'https://[Instance domain name]/server/[Database name]?accessId=[AccessKey ID]&accessSecret=[AccessKey secret]&sql=[SQL statement]'

  # Sample GET request
  curl 'https://dpxxxx-xxxxxxxx.proxy.dms.aliyuncs.com/server/database?accessId=user&accessSecret=pwd&sql=SHOW%20DATABASES'

  # POST request syntax
  curl 'https://[Instance domain name]/server/[Database name]' -H 'accessId:[AccessKey ID]' -H 'accessSecret:[AccessKey secret] -H 'Content-Type:text/plain' -d '[SQL statement]'

  # Sample POST request
  curl 'https://dpxxxx-xxxxxxxx.proxy.dms.aliyuncs.com/server/database' -H 'accessId:user' -H 'accessSecret:pwd -H 'Content-Type:text/plain' -d 'SHOW DATABASES'

• Use a Python 2 program

  Note

  In this example, a Python 2 program is used to access the database instance.

  GET request syntax

  import requests
  
  url = "https://[Instance domain name]/server/[Database name]?accessId=[AccessKey ID]&accessSecret=[AccessKey secret]&sql=[SQL statement]"
  print requests.get(url).text

  Sample GET request

  import requests
  
  url = "https://dpxxxx-xxxxxxxx.proxy.dms.aliyuncs.com/server/database?accessId=user&accessSecret=pwd&sql=SHOW DATABASES"
  print requests.get(url).text

  POST request syntax

  import requests
  
  url = "https://[Instance domain name]/server/[Database name]"
  headers = {
      "Content-Type": "text/plain;charset=utf-8",
      "accessId": "[AccessKey ID]",
      "accessSecret": "[AccessKey secret]"
  }
  print requests.post(url, headers=headers, data='[SQL statement]').text

  Sample POST request

  import requests
  
  url = "https://dpxxxx-xxxxxxxx.proxy.dms.aliyuncs.com/server/database"
  headers = {
      "Content-Type": "text/plain;charset=utf-8",
      "accessId": "user",
      "accessSecret": "pwd"
  }
  print requests.post(url, headers=headers, data='SHOW DATABASES').text

• Use a Node.js program

  GET request syntax

  const https = require("https");
  
  https.get("https://[Instance domain name]/server/[database name]?accessId=[AccessKey ID]&accessSecret=[AccessKey secret]&sql=SHOW DATABASES", resp => {
      resp.on("data", data => {
          console.log(JSON.parse(data));
      });
  });

  Sample GET request

  const https = require("https");
  
  https.get("https://dpxxxx-xxxxxxxx.proxy.dms.aliyuncs.com/server/database?accessId=user&accessSecret=pwd&sql=SHOW DATABASES", resp => {
      resp.on("data", data => {
          console.log(JSON.parse(data));
      });
  });

  POST request syntax

  const https = require("https");
  
  var req = https.request({
      hostname: '[Instance domain name]',
      port: 443,
      path: '/server/[Database name]',
      method: 'POST',
      headers: {
          'Content-Type': 'text/plain; charset=UTF-8',
          accessId: '[AccessKey ID]',
          accessSecret: '[AccessKey secret]'
      }
  }, resp => {
      resp.on("data", data => {
          console.log(JSON.parse(data));
      });
  });
  req.write("[SQL statement]");
  req.end();

  Sample POST request

  const https = require("https");
  
  var req = https.request({
      hostname: 'dpxxxx-xxxxxxxx.proxy.dms.aliyuncs.com',
      port: 443,
      path: '/server/database',
      method: 'POST',
      headers: {
          'Content-Type': 'text/plain; charset=UTF-8',
          accessId: 'user',
          accessSecret: 'pwd'
      }
  }, resp => {
      resp.on("data", data => {
          console.log(JSON.parse(data));
      });
  });
  req.write("SHOW DATABASES");
  req.end();

• Use the Postman client

  GET request syntax

  POST request syntax