An Overview of GBase 8c Database Audit Functionality

GBase 8c provides audit functionality, allowing users to configure audit items, view audit logs, enable or disable auditing, and more. This enhances database security and optimizes dynamic loading features.

Audit Overview

Here are some key points to understand about the audit functionality:

• The main audit switch audit_enabled supports dynamic loading. Modifying its value during database runtime takes effect immediately, without needing a database restart. The default value is on, which means auditing is enabled.
• In addition to the main audit switch, each individual audit item has its own switch. The corresponding audit feature only takes effect if the switch is enabled.
• The switches for individual audit items also support dynamic loading. Changes take effect immediately without requiring a database restart.

Configuring Audit Items

Below are some common audit configuration items:

Configuration Item	Description
User Login and Logout Audit	Parameter: audit_login_logout Default value: 7, enabling user login and logout auditing. Setting it to 0 disables this feature. Other values are not recommended.
Database Start, Stop, Recovery, and Failover Audit	Parameter: audit_database_process Default value: 1, enabling auditing for database start, stop, recovery, and failover operations.
User Lock and Unlock Audit	Parameter: audit_user_locked Default value: 1, enabling auditing for user lock and unlock actions.
Unauthorized User Access Audit	Parameter: audit_user_violation Default value: 0, disabling auditing for unauthorized user actions.
Grant and Revoke Permission Audit	Parameter: audit_grant_revoke Default value: 1, enabling auditing for granting and revoking user permissions.
Full Audit for User Operations	Parameter: full_audit_users Default value: an empty string, indicating default settings with no specific users configured for full auditing.
Client Names and IP Addresses Excluded from Audit	Parameter: no_audit_client Default value: an empty string, indicating default settings with no clients or IPs excluded from auditing.
Audit of CREATE, ALTER, DROP Operations on Database Objects	Parameter: audit_system_object Default value: 67121159, auditing only CREATE, ALTER, and DROP operations for DATABASE, SCHEMA, USER, and DATA SOURCE objects.
Audit of INSERT, UPDATE, and DELETE Operations on Specific Tables	Parameter: audit_dml_state Default value: 0, disabling auditing for specific table DML operations (excluding SELECT).
SELECT Operation Audit	Parameter: audit_dml_state_select Default value: 0, disabling auditing for SELECT operations.
COPY Operation Audit	Parameter: audit_copy_exec Default value: 1, enabling auditing for COPY operations.
Audit of Stored Procedures and User-Defined Function Executions	Parameter: audit_function_exec Default value: 0, not recording audit logs for stored procedure and user-defined function executions.
Audit of System Function Executions in Whitelist	Parameter: audit_system_function_exec Default value: 0, not recording audit logs for system function executions.
SET Command Audit	Parameter: audit_set_parameter Default value: 0, disabling auditing for SET commands.
Transaction ID Logging	Parameter: audit_xid_info Default value: 0, disabling transaction ID logging in audit logs.

Operation Steps

1) Enable DML-related Auditing:

gs_guc reload -N all -I all -c "audit_dml_state = 1"
gs_guc reload -N all -I all -c "audit_dml_state_select = 1"

2) Log in to the database, create a table, insert data, and query it:

create table t1 (id int, c1 int);
insert into t1 values (1, 1);
select * from t1;

3) Query Audit Records:

select detail_info, type, result 
from pg_query_audit('2024-09-05 18:00:00','2024-09-05 20:00:00') 
where type in ('dml_action', 'dml_action_select') and detail_info like '%t1%';

The output will show information similar to the following:

GBase 8c database supports a variety of audit items, including user login and logout audits, database start and stop audits, and user lock and unlock audits. These audit items cover various aspects of database operations, sufficient to meet most maintenance scenarios.

Thank you for reading!