Querying Audit Task Details

Scenarios

You can view details about a database audit task.

Prerequisites

A database audit task has been created

Procedure

1. Log in to the UGO console.
2. In the navigation pane on the left, choose SQL Audit > Database Audit.
3. Locate a task, and click View Details in the Operation column to view the audit details, object statistics, SQL risk statistics, and SQL statements.
4. View information on the Database Audit Details area.
   Figure 1 Viewing database audit details
   
   • Click the value next to Rule Template to view template information.
   • To modify the task description, click next to Description.

5. View information on the Object Type Statistics area.
   Figure 2 Viewing object type statistics
   
   

   The statistics are displayed only when the task status is Audit completed.

   • Object Statistics: The number of object types to be audited in the database are displayed.
   • Object Types Involved in Risky SQLs that Violate Rules: Objects that violate rules are displayed. You can move the cursor to the bar chart to view the number of objects.
     • You can click the drop-down list box in the upper right corner to view the first 5 or 10 object types that violate rules. By default, the first 5 objects are displayed.

6. View information on the Risky SQL Statistics area.
   Figure 3 Viewing risky SQL statistics
   
   

   Others refer to statements that cannot be parsed.

   • Risk Object Statistics: Total number of risky objects in the audit database is collected.
   • Violation Rules for Risky SQLs: Rules that are violated in DDL statements are displayed. You can move the cursor to the bar chart to view the number of rules.
     • You can click the drop-down list box in the upper right corner to view the first 5 or 10 rules that are violated. By default, the first 5 rules are displayed.

7. View information on the SQL Statements area.
   Figure 4 Viewing SQL statements
   

   View the audited object types in the left navigation tree. After you click an object type, the Object Statistics and Risky SQL Statistics areas show the object type details.

   • Export SQL: Select one or more SQL statements to be exported and click Export SQL to export them to a .tar.gz file.
     • By default, all SQL statements are exported. You can also select SQL statements to export, or set search criteria to export desired ones.
     • By default, The exported Excel file contains existing SQL statements is selected. If the existing SQL statements are too large, do not select this option.
     • If the existing SQL statements in the exported file are too large, save them in a separate file whose name will be displayed in the SQL Statement column in the Excel file.
     • You can export a maximum of 5,000 objects to an Excel file.
     • If the table structure information which the SQL statement to be audited depends on contains more than 30,000 characters, it is exported as a SQL file.
   • The status can be:
     • New: The object SQL statement has just been collected and is waiting for audit.
     • Completed: The audit is complete.
     • Analyze_error: Failed to parse SQL statements.
     • Error: An exception occurred during the audit.
     • Ignore: The SQL statement cannot be audited or the size of a single SQL statement exceeds the threshold. The default threshold is 100 KB.

   Failure cause:

   • line:1, position:14, token:table indicates the SQL statement contains table.
   • line:1, position:3, token:<EOF> indicates that the SQL statement is incomplete.

8. Locate a SQL statement and click View Details in the Operation column. The basic information, violated rules, and table structure of the SQL statement are displayed.
   • If multiple SQL statements are collected, you can click Previous or Next.
   Figure 5 Viewing SQL audit details
   
   • SQL Statement: The SQL statements for object creation are displayed.
   • Violated Rules: Based on the selected rule template, the system provides the violated rule name, risk level, and suggestions.

     Click the content in the Problem Segment column to highlight the problem segment in the text. To cancel the highlight, click the content again. The PL/SQL code may have multiple problem fragments. Click on the left to display all problem fragments.

   • Table Structure: Based on the selected rule template, the system provides the table structure information that the SQL statement to be audited depends on, including the object name, object type, and owner.
   • Execution Plans: The execution plans of the SELECT, UPDATE, DELETE, and INSERT statements are displayed.
   

   To obtain the table structure, you must have the permissions to read system catalogs and system views.

   • GaussDB system catalogs: tables, pg_class, pg_namespace, relnamespace, pgxc_class, pg_settings, pg_get_viewdef, columns, pg_tables, pg_index, table_constraints, pg_partition, pg_get_tabledef, check_constraints, key_column_usage, pg_get_keywords, db_constraints, and adm_ind_columns.
   • Currently, table structures supported by index and view objects can be viewed.