Audit user activities

SK21

I have certain tables which have sensitive data .
Sometimes we have some problems in which our sensitive data is leaked.
Their are several users around 65 which access vertica db through dbvisualizer.

What are the best practices to review these user activities on those tables?
Is their anyway to know whether they have downloaded reports from dbvisualizer?

Jim_Knicely

You can check system tables like QUERY_REQUESTS and QUERY_PROFILES or the Data Collector table DC_REQUESTS_ISSUED. They track user queries.

Doc links:

https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/QUERY_REQUESTS.htm
https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/QUERY_PROFILES.htm

SK21

@JimKnicely any thing similar to trigger in vertica?

Jim_Knicely

No. Way too much overhead for an Analytic Database!

One option is to set up a cronjob that runs every so often to check if a user accessed the tables in question and store that info in an audit table for later analysis.

If you want to mask/hide data from users, have you looked into Column and Row Level Access Policies?

Doc Page:
https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/DBUsersAndPrivileges/AccessPolicies/AccessPoliciesOverview.htm