Auditing Database DDL Operations

Jim_Knicely · February 2019

Starting Vertica 9.2, you can now track all DDL operations (i.e. CREATE, ALTER, TRUNCATE, etc.) that are being executed in your database!

The LOG_QUERIES system table provides summary information about those operations.

Example:

dbadmin=> SELECT issued_time,
dbadmin->        user_name,
dbadmin->        audit_type,
dbadmin->        request_type,
dbadmin->        request
dbadmin->   FROM log_queries
dbadmin->  WHERE session_id = current_session()
dbadmin->  ORDER BY issued_time DESC;
issued_time | user_name | audit_type | request_type | request
-------------+-----------+------------+--------------+---------
(0 rows)

dbadmin=> CREATE TABLE test_table (c1 INT, x1 VARCHAR(10));
CREATE TABLE

dbadmin=> ALTER TABLE test_table RENAME x1 TO c2;
ALTER COLUMN
dbadmin=> SELECT issued_time,
dbadmin->        user_name,
dbadmin->        audit_type,
dbadmin->        request_type,
dbadmin->        request
dbadmin->   FROM log_queries
dbadmin->  WHERE session_id = current_session()
dbadmin->  ORDER BY issued_time DESC;
          issued_time          | user_name | audit_type | request_type |                      request
-------------------------------+-----------+------------+--------------+---------------------------------------------------
2019-02-13 06:47:11.093966-05 | dbadmin   | Query      | DDL          | ALTER TABLE test_table RENAME x1 TO c2;
2019-02-13 06:46:52.202358-05 | dbadmin   | Query      | DDL          | CREATE TABLE test_table (c1 INT, x1 VARCHAR(10));
(2 rows)

dbadmin=> TRUNCATE TABLE table_b;
TRUNCATE TABLE

dbadmin=> SELECT issued_time,
dbadmin->        user_name,
dbadmin->        audit_type,
dbadmin->        request_type,
dbadmin->        request
dbadmin->   FROM log_queries
dbadmin->  WHERE session_id = current_session()
dbadmin->  ORDER BY issued_time DESC;
          issued_time          | user_name | audit_type | request_type |                      request
-------------------------------+-----------+------------+--------------+---------------------------------------------------
2019-02-13 06:50:46.250495-05 | dbadmin   | Query      | TRUNCATE     | TRUNCATE TABLE table_b;
2019-02-13 06:47:11.093966-05 | dbadmin   | Query      | DDL          | ALTER TABLE test_table RENAME x1 TO c2;
2019-02-13 06:46:52.202358-05 | dbadmin   | Query      | DDL          | CREATE TABLE test_table (c1 INT, x1 VARCHAR(10));
(3 rows)

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/Security/DatabaseAuditing.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/LOG_QUERIES.htm

Have fun!