Creating a Text Audit Task

Scenarios

You want to check whether a SQL statement complies with specifications and affects performance.

Constraints

• Only four types of syntax for nested statements can be audited. For details, see Table 1.
• When WITH AS is used, only SELECT subqueries are supported.
• The table name and table alias must be different. The aliases of different tables must be different.
• Tables in the audit view cannot be audited.
• Database system tables and system views cannot be audited.
• MySQL statements containing number signs (#) cannot be audited.

  Table 1 Supported nested statements

  No.	SQL Statement
  1	select id, (select subquery) as name from table;
  2	select id from table where id in (select subquery);
  3	select * from table1, (select ...);
  4	with e as (select ...) select * from e;

Rule Constraints

• In all UPDATE and DELETE rules, multiple tables cannot be updated and deleted at a time.
• Multi-table UPDATE and DELETE operations are audited based on the rule Do not use a single UPDATE or DELETE statement to update or delete multiple tables.
• For rules In the PL/pgSQL, use uppercase for keywords and lowercase for non-keywords and In SQL statements, use uppercase for keywords and lowercase for non-keywords, you are not advised using object names as non-reserved keywords, or the audit may be inaccurate. For example, in SELECT ID FROM name, name is a non-reserved keyword.
• After the database system parameter enable_gpi_auto_update is modified, the rule Global indexes must be updated when partitions are swapped, merged, separated, cleared, or deleted in a partitioned table may not take effect. To clear sessions in the database, you can log in to the database.

  CLEAN CONNECTION TO ALL FORCE FOR DATABASE db_name;

• When object names are used as filters for querying system views, use lowercase object names. For details about supported system views, see Table 2.
• Exercise caution when deleting database objects and data. For supported SQL Syntax, see Table 3.

  Table 2 View audit

  View Name	Schema	Object Name Column
  adm_arguments	pg_catalog, sys	owner, object_name, package_name, argument_name
  adm_audit_object	pg_catalog, sys	username, owner, obj_name, action_name
  adm_audit_session	pg_catalog, sys	username, action_name
  adm_audit_statement	pg_catalog, sys	username, obj_name, action_name
  adm_col_comments	pg_catalog, sys	owner, table_name, column_name, schema
  adm_col_privs	pg_catalog, sys	grantor, owner, grantee, table_schema, table_name, column_name, privilege
  adm_coll_types	pg_catalog, sys	owner, type_name, elem_type_mod, elem_type_owner, elem_type_name
  adm_constraints	pg_catalog, sys	owner, constraint_name, table_name, index_owner, index_name
  adm_indexes	pg_catalog, sys	owner, index_name, table_name, table_owner, tablespace_name
  adm_ind_columns	pg_catalog, sys	index_owner, index_name, table_name, table_owner, column_name
  adm_objects	pg_catalog, sys	owner, object_name, subobject_name
  adm_procedures	pg_catalog, sys	owner, object_name, procedure_name, impltypeowner, impltypename
  adm_role_privs	pg_catalog, sys	grantee, granted_role
  adm_tab_col_statistics	pg_catalog, sys	owner, table_name, column_name, schema
  adm_roles	pg_catalog, sys	role
  adm_source	pg_catalog, sys	owner, name
  adm_sys_privs	pg_catalog, sys	grantee, privilege
  adm_tab_cols	pg_catalog, sys	owner, table_name, column_name, data_type_owner, schema, qualified_col_name
  adm_tab_privs	pg_catalog, sys	grantee, owner, table_name, grantor, privilege
  adm_tables	pg_catalog, sys	owner, table_name, tablespace_name
  adm_tab_columns	pg_catalog, sys	owner, table_name, column_name, data_type_owner, schema
  adm_tab_comments	pg_catalog, sys	owner, table_name, column_name, schema
  adm_tab_statistics	pg_catalog, sys	owner, table_name
  adm_triggers	pg_catalog, sys	owner, trigger_name, table_owner, table_name
  adm_type_attrs	pg_catalog, sys	type_name, attr_name, attr_type_name, character_set_name
  adm_types	pg_catalog, sys	owner, type_name
  adm_users	pg_catalog, sys	username, default_tablespace, temporary_tablespace, default_collation
  adm_views	pg_catalog, sys	owner, view_name
  db_all_tables	pg_catalog, sys	owner, table_name, tablespace_name
  db_arguments	pg_catalog, sys	owner, object_name, package_name, argument_name
  db_col_comments	pg_catalog, sys	owner, table_name, column_name, schema
  db_col_privs	pg_catalog, sys	grantor, owner, grantee, table_schema, table_name, column_name, privilege
  db_coll_types	pg_catalog, sys	owner, type_name, elem_type_mod, elem_type_owner, elem_type_name
  db_constraints	pg_catalog, sys	owner, constraint_name, table_name, index_owner, index_name
  db_indexes	pg_catalog, sys	owner, index_name, table_name, table_owner, tablespace_name
  db_ind_columns	pg_catalog, sys	index_owner, index_name, table_name, table_owner, column_name
  db_objects	pg_catalog, sys	owner, object_name, subobject_name
  db_procedures	pg_catalog, sys	owner, object_name
  db_tab_col_statistics	pg_catalog, sys	owner, table_name, column_name, schema
  db_source	pg_catalog, sys	owner, name
  db_tab_columns	pg_catalog, sys	owner, table_name, column_name, data_type_owner, schema
  db_tab_comments	pg_catalog, sys	owner, table_name, schema
  db_tables	pg_catalog, sys	owner, table_name, tablespace_name
  db_triggers	pg_catalog, sys	trigger_name, table_owner, table_name
  db_types	pg_catalog, sys	owner, type_name
  db_users	pg_catalog, sys	username
  db_views	pg_catalog, sys	owner, view_name
  dict	pg_catalog, sys	table_name
  dictionary	pg_catalog, sys	table_name
  my_col_comments	pg_catalog, sys	owner, table_name, column_name, schema
  my_col_privs	pg_catalog, sys	grantor, owner, grantee, table_schema, table_name, column_name, privilege
  my_coll_types	pg_catalog, sys	owner, type_name, elem_type_mod, elem_type_owner, elem_type_name
  my_constraints	pg_catalog, sys	owner, constraint_name, table_name, index_owner, index_name
  my_indexes	pg_catalog, sys	owner, index_name, table_name, table_owner, tablespace_name
  my_ind_columns	pg_catalog, sys	index_owner, index_name, table_name, table_owner, column_name
  my_objects	pg_catalog, sys	object_name, subobject_name
  my_procedures	pg_catalog, sys	owner, object_name, procedure_name, impltypeowner, impltypename
  my_role_privs	pg_catalog, sys	grantee, granted_role
  my_tab_col_statistics	pg_catalog, sys	table_name, column_name, schema
  my_source	pg_catalog, sys	owner, name
  my_tab_columns	pg_catalog, sys	owner, table_name, column_name, data_type_owner, schema
  my_tab_comments	pg_catalog, sys	owner, table_name, column_name, schema
  my_tab_statistics	pg_catalog, sys	table_name
  my_tables	pg_catalog, sys	owner, table_name, tablespace_name
  my_triggers	pg_catalog, sys	owner, trigger_name, table_owner, table_name
  my_type_attrs	pg_catalog, sys	type_name, attr_name, attr_type_name, character_set_name
  my_types	pg_catalog, sys	type_name
  my_views	pg_catalog, sys	owner, view_name
  pg_indexes	pg_catalog, sys	schemaname, tablename, indexname, tablespace
  pg_roles	pg_catalog, sys	rolename
  pg_tables	pg_catalog, sys	schemaname, tablename, tableowner, tablespace, tablecreator
  pg_user	pg_catalog, sys	username, nodegroup
  pg_views	pg_catalog, sys	schemaname, viewname, viewowner
  column_privileges	information_schema, sys	grantor, grantee, table_catalog, table_schema, table_name, column_name
  columns	information_schema, sys	table_catalog, table_schema, table_name, column_name
  constraint_column_usage	information_schema, sys	table_catalog, table_schema, table_name, column_name, constraint_catalog, constraint_schema, constraint_name
  constraint_table_usage	information_schema, sys	table_catalog, table_schema, table_name, constraint_catalog, constraint_schema, constraint_name
  enabled_roles	information_schema, sys	role_name
  schemata	information_schema, sys	catalog_name, schema_name, schema_owner, default_character_set_catalog, default_character_set_schema, default_character_set_name
  table_constraints	information_schema, sys	constraint_catalog, constraint_schema, constraint_name, table_catalog, table_schema, table_name
  table_privileges	information_schema, sys	grantor, grantee, table_catalog, table_schema, table_name
  tables	information_schema, sys	table_catalog, table_schema, table_name, self_referencing_column_name, user_defined_type_catalog, user_defined_type_schema, user_defined_type_name
  triggers	information_schema, sys	trigger_catalog, trigger_schema, trigger_name, event_object_catalog, event_object_schema, event_object_table, action_reference_old_table, action_reference_new_table
  usage_privileges	information_schema, sys	grantor, grantee, object_catalog, object_schema, object_name
  views	information_schema, sys	table_catalog, table_schema, table_name

  Table 3 SQL syntax that can be audited

  DDL Type	SQL syntax
  DROP	DROP TABLE, DROP TABLESPACE, DROP AGGREGATE, DROP AUDIT POLICY, DROP CAST, DROP DATABASE, DROP DATA SOURCE, DROP DIRECTORY, DROP EVENT, DROP FOREIGN TABLE, DROP GLOBAL CONFIGURATION, DROP GROUP, DROP MASKING POLICY, DROP MATERIALIZED VIEW, DROP MODEL, DROP OPERATOR, DROP OWNED, DROP PACKAGE, DROP PACKAGE BODY, DROP PROCEDURE, DROP RESOURCE LABEL, DROP RESOURCE POOL, DROP ROLE, DROP ROW LEVEL SECURITY POLICY, DROP RULE, DROP PUBLICATION, DROP SCHEMA, DROP SEQUENCE, DROP FUNCTION, DROP SERVER, DROP SUBSCRIPTION, DROP SYNONYM, DROP TEXT SEARCH CONFIGURATION, DROP TEXT SEARCH DICTIONARY, DROP TRIGGER, DROP TYPE, DROP USER, DROP USER MAPPING, DROP VIEW, DROP WEAK PASSWORD DICTIONARY
  ALTER	ALTER DROP PARTITION, ALTER TRUNCATE PARTITION, ALTER DROP COLUMN, ALTER DROP CONSTRAINT, ALTER DROP FOREIGN TABLE, ALTER DROP AUDIT POLICY, ALTER DROP MASKING POLICY, ALTER DROP SERVER, ALTER DROP TEXT SEARCH CONFIGURATION, ALTER DROP USER MAPPING, ALTER DROP DATA SOURCE
  TRUNCATE	TRUNCATE

Procedure

1. Log in to the UGO console.
2. In the navigation pane, choose SQL Audit > Statement Audit. The SQL Text page is displayed by default.
3. Configure parameters as needed and view that the Submit button is highlighted.
   Figure 1 Audited Text
   

   Table 4 Parameter description

   Parameter	Description
   Database Type	Select a database type. Currently, only GaussDB and MySQL are supported.
   Data Source	Select a data source. Currently, only GaussDB and MySQL 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.
   SQL Statement	Enter the SQL statement to be audited. Text audit is supported only for a single statement. If there are multiple statements, only the first statement is audited. If a SQL object name is in uppercase and is not enclosed in double quotation marks, the system automatically converts the name to lowercase and then queries the corresponding table structure in the database.

4. Click Submit. The View Details dialog box is displayed, and a corresponding record is generated.
   Figure 2 Viewing details
   

   Click the template name next to Rule Template to view template information.

   If the statement fails to be audited, the cause is displayed.

   Failure causes (examples):

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