Audit

Hello,
There are partner tools that are available to audit ddl and dml changes. The tools that support Vertica for auditing are Datasunrise or IBM Guardium.
In addition we have published document for Datasunrise.
https://www.vertica.com/kb/DataSunriseCG/Content/Partner/DataSunriseCG.htm
For Guardium, You will have to check IBM Guardium documentation.
Thanks,
Jawahar.

will it work with vertica 9. as per the link above support vertica 7-8.1

yes, it will work. The only reason, it say that because it was tested with those version.

I will ask Steve from my team to test it out again and update the document.
Thanks,
Jawahar.

doesnt the tool do row level monitoring - mantain trace of row level update/insert by any user with old and new data.

Probably the tools do offer row level monitoring. However we have not tested it.

I confirmed with DataSunrise that they did qualify it with Vertica V9. They are in the process of updating the Admin doc supported database list to reflect that. To expand a bit on the prior comment regarding row level auditing. One of DataSunrises' features is Auditing. This is done by creating and activating data rules. These rules tell it what to monitor for, what filtering if any you want to apply, and what thresholds you might want to apply to minimize noise in the audit. The rules allow monitoring of any combination of select, update, delete, and others, as well as to the Database, Schema, Table, and column level. We did not try to do any row level monitoring as our initial objectives were to just to make sure it didn't impact performance and handled all our datatypes correctly. With a few pieces of info they allow download of the User Guide. I'd recommend pulling it down and reviewing to see if it meets your needs. I hope it helps.

Vertica 9.2 can audit DDL.

See:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/Security/DatabaseAuditing.htm
https://forum.vertica.com/discussion/240384/auditing-database-ddl-operations

thanks Jim , any option to even track DML .

You can track DML operations via several system tables (i.e. QUERY_REQUESTS, QUERY_PROFILES, DC_REQUESTS_ISSUED).

Example:

dbadmin=> CREATE TABLE test (c1 INT);
CREATE TABLE

dbadmin=> INSERT INTO test SELECT 1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> UPDATE test SET c1 = 1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT time, node_name, user_name, request FROM dc_requests_issued WHERE request_type = 'QUERY' AND (request ILIKE 'INSERT%' OR request ILIKE 'UPDATE%' OR request ILIKE 'DELETE%' OR request ILIKE 'MERGE%') ORDER BY time DESC limit 10;
             time              |     node_name      | user_name |                   request
-------------------------------+--------------------+-----------+---------------------------------------------
 2019-02-21 09:34:25.612086-05 | v_test_db_node0001 | dbadmin   | UPDATE test SET c1 = 1;
 2019-02-21 09:30:40.363076-05 | v_test_db_node0001 | dbadmin   | INSERT INTO test SELECT 1;
 2019-02-20 10:27:19.011133-05 | v_test_db_node0001 | dbadmin   | insert into lap select 2, 4;
 2019-02-20 10:19:32.506009-05 | v_test_db_node0001 | dbadmin   | insert into lap select 2, 2;
 2019-02-20 10:19:27.471826-05 | v_test_db_node0001 | dbadmin   | insert into lap select 1, 1;
 2019-02-19 21:19:24.851337-05 | v_test_db_node0001 | dbadmin   | INSERT INTO fact_flat (c1, c2) SELECT 2, 3;
 2019-02-19 21:19:24.832732-05 | v_test_db_node0001 | dbadmin   | INSERT INTO fact_flat (c1, c2) SELECT 2, 3;
 2019-02-19 21:19:23.77946-05  | v_test_db_node0001 | dbadmin   | INSERT INTO fact_flat (c1, c2) SELECT 1, 1;
 2019-02-19 21:19:23.762446-05 | v_test_db_node0001 | dbadmin   | INSERT INTO fact_flat (c1, c2) SELECT 1, 1;
 2019-02-19 21:18:10.532602-05 | v_test_db_node0001 | dbadmin   | INSERT INTO dim SELECT 3, 'TEST3';
(10 rows)

yeah i do check those tables .But these tables have some retention size/time due to which they get cleared up.

Yup. You can change the retention time on DC_REQUESTS_ISSUED.

See:
https://forum.vertica.com/discussion/comment/241989#Comment_241989

Or you can do what I do what I do, copy the data from DC_REQUESTS_ISSUED into my own table every 4 hours (just the new records).

thanks jim , i was looking at second option and maybe tweak to store only non selects