Help Center> Database and Application Migration UGO> Getting Started> SQL Audit> Step 1: Create an Audit Task> Creating a File Audit Task
Updated on 2024-06-11 GMT+08:00
View PDF

Creating a File Audit Task

Scenarios

You want to check whether SQL statements in a file comply with specifications and affect performance.

Constraints

  • Only four types of nested statements listed in Table 1 are supported.
  • The table name and table alias must be different. The aliases of different tables must be different.
  • Tables in views cannot be audited.
  • Database system tables and views cannot be audited.
  • There is no # or /* in table structures, statements, and thresholds.
  • You cannot click Retry when the file is being audited or after the audit is complete.
  • If a system template is used, the audit results before and after a version upgrade will be inconsistent. You are advised to use a custom template.
  • Up to 110 SQL tasks can be created and up to 10 SQL tasks can be executed concurrently in a given period.

Procedure

  1. Log in to the UGO console.
  2. In the navigation pane on the left, choose SQL Audit > Statement Audit. Click the SQL from Files tab.
  3. Click Upload File.

    Figure 1 Upload a file
    Table 1 Parameter description

    Parameter

    Description

    Database Type

    Select a database type. Currently, only GaussDB and MySQL are supported.

    Data Source

    Select a data source type. Currently, only GaussDB and MySQL data sources are supported.

    This parameter is optional. If no data source is provided, the audit rules that depend on the data source are skipped by default.

    Schema

    Select a schema. This parameter is optional and only available for GaussDB databases.

    • If the SQL statement contains a schema name, use the schema in the SQL statement.
    • If the SQL statement does not contain a schema name, the selected schema is used.
    • If the SQL statement does not contain a schema name and no schema is selected, use the public schema.

    Rule Template

    Select a template based on the selected database type.

    You can set the template information by referring to Adding a Rule Template.

    Upload Data File

    Upload a SQL file that meets the requirements. Requirements for the file to be uploaded:

    • The SQL object name can contain only lowercase letters. If you enter an uppercase SQL object name, the system automatically converts it to lowercase letters.
    • The file can contain only simple SQL statements, such as INSERT, ALTER, DELETE, SELECT operations, and cannot contain stored procedures, functions, triggers, packages, or anonymous blocks, which is regarded as one SQL statement for audit.
      NOTE:

      If there are both simple SQL statements and complex statements, all SQL statements in the entire file are audited as one SQL statement.

    • Only .zip, .xml, .sql, .java, and .json files can be uploaded.
      • The file name can contain only digits, letters, underscores (_), and hyphens (-).
      • Maximum file name length: 240 characters
    • Max. file size: 5 MB.
    • File types in a ZIP package: xml, sql, java, and json
      • Max.size per file: 10 MB
      • Max. files: 10,000
      • The file name can contain only digits, letters, underscores (_), and hyphens (-).
      • Maximum file name length: 240 characters
      • Files to be uploaded cannot contain sensitive data such as binary files, passwords, and keys to ensure data security.

    Description

    (Optional) Enter the description of the file. The description contains up to 100 characters.

  4. Click OK and view the file task you created on the task list page.

    During the creation of a file audit task, if the task is terminated due to UGO restart, you can click Retry to continue the task.
    Figure 2 Viewing file audit task list

    The status can be one of the following:

    • Pending: The SQL audit task is to be scheduled.
    • Collecting objects: SQL statements scanned in the file and DDL statements in the schema of a specified database.
    • Auditing SQL statements: The file is being audited.
    • Audit completed: The audit is complete only after all SQL statements in the file are audited.
    • Audit failed: An exception occurred during the audit.