Help Center/ Database Security Service/ API Reference/ API/ Data Analytics/ Querying Audit SQL Statements

Updated on 2024-11-01 GMT+08:00

Online Debugging

CLI Examples

View PDF

Querying Audit SQL Statements

Function

This API is used to query audit SQL statements.

Calling Method

For details, see Calling APIs.

URI

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

**Table 1** URI parameter
Parameter	Mandatory	Parameter Type	Description
project_id	Yes	String	Project ID.
instance_id	Yes	String	Instance ID. You can obtain the value from the ID field in the API for querying the instance list.

Request Parameter

**Table 2** Request header parameter
Parameter	Mandatory	Parameter Type	Description
X-Auth-Token	Yes	String	User token. The token can be queried by calling the IAM API. (The token is the value of X-Subject-Token in the response header.)

**Table 3** Request body parameters
Parameter	Mandatory	Parameter Type	Description
time	Yes	time object	Query time range
risk_levels	No	String	Risk severity. HIGH MEDIUM LOW NO_RISK
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, and OPERATE ALL
rule_name	No	String	Rule name
sql_statement	No	String	SQL statement
sql_response	No	String	Response Result SUCCESS FAILED
page	No	Integer	Page number
size	No	Integer	Number of records.
time_order	No	String	Time sequence. The value can be: DESC ASC

**Table 4** time
Parameter	Mandatory	Parameter 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 HOUR THREE_HOUR TWELVE_HOUR DAY WEEK MONTH
start_time	No	String	Start time. This parameter must be used together 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. This parameter must be used together 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 parameter
Parameter	Parameter Type	Description
total	Integer	Total number
count	Integer	Total number
sqls	Array of sqls objects	SQL statement list

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

**Table 7** sql
Parameter	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 type: LOGIN, CREATE_TABLE, CREATE_TABLESPACE, DROP_TABLE, DROP_TABLESPACE, DELETE, INSERT, INSERT_SELECT, SELECT, SELECT_FOR_UPDATE, UPDATE, CREATE_USER, DROP_USER, GRANT, and OPERATE ALL
operated_obj_info	Array of operated_obj_info objects	Action objects
rule_name	String	Rule name
risk_level	String	Risk severity. HIGH MEDIUM LOW NO_RISK
start_time	String	Audit start time
sql_response	String	Response Result SUCCESS FAILED
db_instance	String	DB instance

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

Status code: 400

**Table 9** Response body parameter
Parameter	Parameter Type	Description
error	Object	Error message.

**Table 10** ErrorDetail
Parameter	Parameter Type	Description
error_code	String	Error code
error_msg	String	Error message

Status code: 403

**Table 11** Response body parameter
Parameter	Parameter Type	Description
error	Object	Error message.

**Table 12** ErrorDetail
Parameter	Parameter Type	Description
error_code	String	Error code
error_msg	String	Error message

Status code: 500

**Table 13** Response body parameter
Parameter	Parameter Type	Description
error	Object	Error message.

**Table 14** ErrorDetail
Parameter	Parameter Type	Description
error_code	String	Error code
error_msg	String	Error message

Example Request

/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"
}

Response Examples

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 rule",
      "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 rule",
      "risk_level" : "",
      "start_time" : "2024-04-22 08:46:02",
      "sql_response" : "FAILED",
      "db_instance" : ""
    }
  } ]
}

Status code: 400

Incorrect request parameter.

{
  "error" : {
    "error_code" : "DBSS.XXXX",
    "error_msg" : "XXX"
  }
}

Status code: 500

Internal server error.

{
  "error" : {
    "error_code" : "DBSS.XXXX",
    "error_msg" : "XXX"
  }
}

Status Code

Status Code	Description
200	Request succeeded.
400	Incorrect request parameter.
403	Authentication failed.
500	Internal server error.

Error Codes

For details, see Error Codes.

Parent topic: Data Analytics

Previous topic: Querying Audit Alarm Information

Next topic: Querying Audit Summary Information

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot