DEV Community

Michael
Michael

Posted on • Originally published at gbase.cn

How to Audit Only Critical Operations Like DROP TABLE in GBase 8a

#gbase #database #数据库 #security

GBase 8a’s audit log can capture virtually every SQL operation. To keep logs lean and focused on security, you can configure a policy that records only high‑risk actions — like DROP TABLE — using the CREATE AUDIT POLICY command. This post shows you exactly how to set it up in a gbase database.

What the Audit Log Can Record

The audit framework covers all major SQL categories:

  • DDL: CREATE, ALTER, DROP (including DROP_TABLE, DROP_DB), TRUNCATE, RENAME_USER
  • DML: SELECT, INSERT, DELETE, UPDATE, LOAD, MERGE
  • DCL & Users: GRANT, REVOKE, CREATE_USER, DROP_USER
  • OTHERS: any SQL not explicitly listed

Step‑by‑Step: Record Only High‑Risk Operations

1. Enable Audit Logging to a Table 

SET GLOBAL log_output = 'table';
SET GLOBAL audit_log = 1;

-- Verify
SHOW VARIABLES LIKE '%audit_log%';
SHOW VARIABLES LIKE '%log_output%';
Enter fullscreen mode Exit fullscreen mode

2. Create an Audit Policy (the key part)

Use CREATE AUDIT POLICY and list the exact commands you want to capture. Commands must be separated by commas with no spaces.

-- Only record DROP_TABLE, DROP_DB, TRUNCATE, DROP_USER
CREATE AUDIT POLICY audit_critical (
    enable = 'Y',
    sql_commands = 'DROP_TABLE,DROP_DB,TRUNCATE,DROP_USER'
);
Enter fullscreen mode Exit fullscreen mode

3. Test and Verify

Run a forbidden operation and check the log:

DROP TABLE t1;
DROP DATABASE test_db;

SELECT start_time, user_host, sql_command, LEFT(sql_text, 100) AS sql_sample
FROM gbase.audit_log
ORDER BY start_time DESC;
Enter fullscreen mode Exit fullscreen mode

If the policy is correct, you’ll see only DROP_TABLE and DROP_DB events — ordinary SELECT statements won’t appear.

4. Clean Up (Optional) 

TRUNCATE SELF audit_log;
Enter fullscreen mode Exit fullscreen mode

Going Further: Fine‑Grained Filtering

You can combine sql_commands with other dimensions for even tighter control:

Parameter Description Example
user Limit to a specific user user='app_user'
hosts Filter by IP pattern (supports %, _) hosts='192.168.1.%'
db Limit to a particular database db='finance_db'
long_query_time Only log slow queries (seconds) long_query_time=10
status Capture only failed or successful ops status='FAILED'

Example: record DROP_TABLE only when executed by admin from host 192.168.1.100 on the prod_db database.

CREATE AUDIT POLICY audit_admin_drop (
    enable = 'Y',
    user = 'admin',
    hosts = '192.168.1.100',
    db = 'prod_db',
    sql_commands = 'DROP_TABLE'
);
Enter fullscreen mode Exit fullscreen mode

With a targeted policy like this, your gbase database keeps a tight security audit trail without drowning in unnecessary log data. It’s a simple but powerful capability every DBA should have in their toolkit.

Top comments (0)