Auditing Database DDL Operations
Jim_Knicely
- Select Field - Administrator
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!
1