Cong LiGBase 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_enabledsupports dynamic loading. Modifying its value during database runtime takes effect immediately, without needing a database restart. The default value ison, 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_logoutDefault 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_processDefault value: 1, enabling auditing for database start, stop, recovery, and failover operations. |
| User Lock and Unlock Audit |
Parameter: audit_user_lockedDefault value: 1, enabling auditing for user lock and unlock actions. |
| Unauthorized User Access Audit |
Parameter: audit_user_violationDefault value: 0, disabling auditing for unauthorized user actions. |
| Grant and Revoke Permission Audit |
Parameter: audit_grant_revokeDefault value: 1, enabling auditing for granting and revoking user permissions. |
| Full Audit for User Operations |
Parameter: full_audit_usersDefault 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_clientDefault 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_objectDefault 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_stateDefault value: 0, disabling auditing for specific table DML operations (excluding SELECT). |
| SELECT Operation Audit |
Parameter: audit_dml_state_selectDefault value: 0, disabling auditing for SELECT operations. |
| COPY Operation Audit |
Parameter: audit_copy_execDefault value: 1, enabling auditing for COPY operations. |
| Audit of Stored Procedures and User-Defined Function Executions |
Parameter: audit_function_execDefault 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_execDefault value: 0, not recording audit logs for system function executions. |
| SET Command Audit |
Parameter: audit_set_parameterDefault value: 0, disabling auditing for SET commands. |
| Transaction ID Logging |
Parameter: audit_xid_infoDefault 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!
