Auditing Database DDL Operations: Quick Tip

Posted February 14, 2019 by Jim Knicely, Big Data Solutions Architect, Vertica

Shot of two businesswomen using a digital tablet together during a collaboration at work
Starting with 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/latest/HTML/Content/Authoring/Security/DatabaseAuditing.htm

https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/LOG_QUERIES.htm

Have fun!