Auditing Database DDL Operations

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

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!

Sign In or Register to comment.