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%';

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'
);

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;

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;

Going Further: Fine‑Grained Filtering

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

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'
);

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.