Querying Audit SQL Statements (To Be Brought Offline)

Function

This API is used to query audit SQL statements.

URI

POST /v1/{project_id}/{instance_id}/audit/sqls

**Table 1** Path Parameters
Parameter	Mandatory	Type	Description
project_id	Yes	String	Definition: Specifies the project ID. You can obtain the value by calling the IAM API for querying the project list of a specified IAM user. Constraints N/A Range: The value is subject to the return value of the IAM service interface. The value contains 32 to 64 characters. Default Value: N/A
instance_id	Yes	String	Definition: Instance ID. The value can be obtained from the ID field of the API for querying the instance list. Constraints N/A Range: The value is subject to the value of the API for querying the instance list. The value contains 32 to 64 characters. Default Value: N/A

Request Parameters

**Table 2** Request header parameters
Parameter	Mandatory	Type	Description
X-Auth-Token	Yes	String	Parameter description: User token. You can obtain the token by calling the IAM API used to query the user token. The token is the value of X-Subject-Token in the response header. Restrictions: N/A Range: The value is subject to the user token query interface of the IAM service. Default Value: N/A

**Table 3** Request body parameters
Parameter	Mandatory	Type	Description
time	Yes	time object	Query time range
risk_levels	No	String	Risk level HIGH: high MEDIUM: medium LOW: low NO_RISK: none
client_ip	No	String	Client IP address
client_name	No	String	Client name
db_ip	No	String	Database IP address
db_user	No	String	Database User
query_type	No	String	Query type LOGIN,CREATE_TABLE,CREATE_TABLESPACE,DROP_TABLE, DROP_TABLESPACE,DELETE,INSERT,INSERT_SELECT,SELECT,SELECT_FOR_UPDATE, UPDATE,CREATE_USER,DROP_USER,GRANT,OPERATE ALL
rule_name	No	String	Rule Name
sql_statement	No	String	SQL statement
sql_response	No	String	Response Result SUCCESS FAILED
page	Yes	Integer	Page number
size	Yes	Integer	Number of records.
time_order	Yes	String	Time sequence. The value can be: DESC ASC

**Table 4** time
Parameter	Mandatory	Type	Description
time_range	No	String	Time range. This parameter cannot be used together with start_time and end_time. If they are used together, this parameter has a higher priority. The value can be: HALF_HOUR: half an hour HOUR: 1 hour THREE_HOUR: 3 hours TWELVE_HOUR: 12 hours DAY: 1 day WEEK: one week MONTH: one month
start_time	No	String	Start time, which must be in pairs with end_time. The format must be yyyy-MM-dd HH:mm:ss. Time when an action occurred, in UTC time.
end_time	No	String	End time, which must be in pairs with start_time. The format must be yyyy-MM-dd HH:mm:ss. Time when an action occurred, in UTC time.

Response Parameters

Status code: 200

**Table 5** Response body parameters
Parameter	Type	Description
total	Integer	Total number of license files
count	Integer	Total number of license files
sqls	Array of sqls objects	SQL statement list

**Table 6** sqls
Parameter	Type	Description
sql	sql object	SQL information

**Table 7** sql
Parameter	Type	Description
id	String	ID
sql_statement	String	SQL statement
client_ip	String	Client IP address
client_name	String	Client name
db_ip	String	Database IP address
db_user	String	Database Username
query_type	String	Query typeLOGIN,CREATE_TABLE,CREATE_TABLESPACE,DROP_TABLE, DROP_TABLESPACE,DELETE,INSERT,INSERT_SELECT,SELECT,SELECT_FOR_UPDATE, UPDATE,CREATE_USER,DROP_USER,GRANT,OPERATE ALL
operated_obj_info	Array of operated_obj_info objects	Action objects
rule_name	String	Rule Name
risk_level	String	Risk level HIGH MEDIUM LOW NO_RISK
start_time	String	Audit start time
sql_response	String	Response Result SUCCESS FAILED
db_instance	String	Database instance

**Table 8** operated_obj_info
Parameter	Type	Description
column_name	String	Parameter
object_type	String	Operation Object Type
schema_name	String	Schema name
sql_type	String	SQL type
sys_name	String	System Name
table_name	String	Table Name

Status code: 400

**Table 9** Response body parameters
Parameter	Type	Description
details	Array of DbssErrorDetail objects	PDP5 authentication exception information
encoded_authorization_message	String	PDP5 authentication exception information
error_code	String	Error Codes
error_msg	String	Error message.

**Table 10** DbssErrorDetail
Parameter	Type	Description
error_code	String	Error Codes
error_msg	String	PDP5 error information

Status code: 403

**Table 11** Response body parameters
Parameter	Type	Description
details	Array of DbssErrorDetail objects	PDP5 authentication exception information
encoded_authorization_message	String	PDP5 authentication exception information
error_code	String	Error Codes
error_msg	String	Error message.

**Table 12** DbssErrorDetail
Parameter	Type	Description
error_code	String	Error Codes
error_msg	String	PDP5 error information

Status code: 500

**Table 13** Response body parameters
Parameter	Type	Description
details	Array of DbssErrorDetail objects	PDP5 authentication exception information
encoded_authorization_message	String	PDP5 authentication exception information
error_code	String	Error Codes
error_msg	String	Error message.

**Table 14** DbssErrorDetail
Parameter	Type	Description
error_code	String	Error Codes
error_msg	String	PDP5 error information

Example Requests

/v1/{project_id}/{instance_id}/audit/sqls

{
  "time" : {
    "time_range" : "DAY",
    "start_time" : null,
    "end_time" : null
  },
  "risk_levels" : null,
  "client_ip" : null,
  "client_name" : null,
  "db_ip" : null,
  "db_user" : null,
  "query_type" : null,
  "rule_name" : null,
  "sql_statement" : null,
  "sql_response" : null,
  "page" : 1,
  "size" : 50,
  "time_order" : "DESC"
}

Example Responses

Status code: 200

Success

{
  "total" : 2,
  "count" : 2,
  "sqls" : [ {
    "sql" : {
      "id" : "o1n8BI8BDdIO3rwS4Rea",
      "sql_statement" : "create table test(name varchar(1000), age int)",
      "client_ip" : "fe80::f816:3eff:feca:22f5",
      "client_name" : "",
      "db_ip" : "fe80::f816:3eff:feca:22f5",
      "db_user" : "root",
      "query_type" : "CREATE TABLE",
      "operated_obj_info" : [ {
        "column_name" : "",
        "object_type" : "TABLE",
        "schema_name" : "test",
        "sql_type" : "CREATE TABLE",
        "sys_name" : "",
        "table_name" : "test"
      }, {
        "column_name" : "",
        "object_type" : "TABLE",
        "schema_name" : "test",
        "sql_type" : "CREATE",
        "sys_name" : "",
        "table_name" : "test"
      } ],
      "rule_name" : "Full audit rules",
      "risk_level" : "",
      "start_time" : "2024-04-22 08:46:02",
      "sql_response" : "SUCCESS",
      "db_instance" : ""
    }
  }, {
    "sql" : {
      "id" : "pFn8BI8BDdIO3rwS4Rea",
      "sql_statement" : "create table test",
      "client_ip" : "fe80::f816:3eff:feca:22f5",
      "client_name" : "",
      "db_ip" : "fe80::f816:3eff:feca:22f5",
      "db_user" : "root",
      "query_type" : "CREATE",
      "operated_obj_info" : [ {
        "column_name" : "",
        "object_type" : "",
        "schema_name" : "test",
        "sql_type" : "CREATE",
        "sys_name" : "",
        "table_name" : ""
      } ],
      "rule_name" : "Full audit rules",
      "risk_level" : "",
      "start_time" : "2024-04-22 08:46:02",
      "sql_response" : "FAILED",
      "db_instance" : ""
    }
  } ]
}