How to enable/disable database Audits?

New to Vertica and couldn't find documents as how database Audits are enable/disabled in Vertica.
Also how long the audit records are stored inside database to review later.
0
New to Vertica and couldn't find documents as how database Audits are enable/disabled in Vertica.
Also how long the audit records are stored inside database to review later.
Comments
What exactly do you want to audit?
If you mean DB size Audit, then SELECT audit_license_size();
Will trigger an immediate audit of the database size and
select GET_COMPLIANCE_STATUS();
Will display whether your database size follows your Vertica license agreement based on the last audit.
See: https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/AdministratorsGuide/Licensing/CalculatingTheDatabaseSize.htm
If you mean Audit the requests history, do SELECT DATA_COLLECTOR_HELP(); to get online usage instructions about the Data Collector, the DATA_COLLECTOR system table, and the Data Collector control functions.
For example, you can get a history of all SQL requests issued from a data_collector table name: dc_requests_issued
And a history of all completed requests from table: dc_requests_completed
To see the relevant tables component do:
To see those components collector retention policy do:
Data Collector retention policies hold the following information:
1. Which component to monitor
2. How much memory to retain
3. How much disk space to retain
Explained here: https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/AdministratorsGuide/Monitoring/Vertica/ConfiguringDataRetentionPolicies.htm
Data Collector is on by default and retains information for all sessions.
A superuser can disable Data Collector at any time.
To disable the Data Collector: ALTER DATABASE DEFAULT SET EnableDataCollector = 0;
To re-enable the Data Collector: ALTER DATABASE DEFAULT SET EnableDataCollector = 1;
@mosheg, Above information is so helpful.
In the data_collector/interval_time, if the value is greater than 0 then is it days/hours?
I am looking for the below audits options:
1. Failed login attempts and the reason.
2. Insert, Update, Delete SQL's
3. Truncate & Drop
4. Login/logoff
5. Failed attempt on the above #2 & #3
Consider the following example:
CREATE USER Mike IDENTIFIED BY 'MikePasword';
CREATE USER
vsql -U Mike -w BadPas
Will get an error message: vsql: FATAL 3781: Invalid username or password
This will be reflected in the last vertica.log using for example the following command:
grep -B 3 -e "Invalid user" $(admintools -t list_db -d EEVDB | grep -e "Catalog Dir" | cut -d: -f2)/vertica.log
2020-05-15 18:36:45.941 Init Session:0x7ef8fb117700 @v_eevdb_node0001: 00000/4686: Authentication - sendAuthRequest: user=Mike database=dbadmin host=::1 authType=3
2020-05-15 18:36:45.942 Init Session:0x7ef8fb117700-a0000000001984 [Txn] Begin Txn: a0000000001984 'check_login_history'
2020-05-15 18:36:45.942 Init Session:0x7ef8fb117700-a0000000001984 [Txn] Rollback Txn: a0000000001984 'check_login_history'
2020-05-15 18:36:45.942 Init Session:0x7ef8fb117700 @v_eevdb_node0001: {SessionRun} 28000/3781: Invalid username or password
In addition to the record in table: dc_login_failures
vsql -xc "select * from dc_login_failures;"
-[ RECORD 1 ]--------------+------------------------------
time | 2020-05-15 18:36:45.942189-04
node_name | v_eevdb_node0001
database_name | dbadmin
user_name | Mike
client_hostname | ::1
client_pid | 479837
client_label |
client_version |
client_os_user_name | dbadmin
ssl_client_subject |
ssl_client_fingerprint |
authentication_method | Password
client_authentication_name | default: Password
requested_protocol | 3.8
effective_protocol | 3.8
reason | FAILED
Consider the following example:
SELECT * FROM dc_requests_issued where left(request,10) ilike '%insert%' and (NOW() - time) < '24 hours';
-[ RECORD 1 ]-----+----------------------------------------------------------
time | 2020-05-15 19:10:48.472227-04
node_name | v_eevdb_node0001
session_id | v_eevdb_node0001-290527:0x163ae
user_id | 45035996273704962
user_name | dbadmin
transaction_id | 45035996273711574
statement_id | 1
request_id | 3
request_type | QUERY
label |
client_label |
search_path | "$user", public, v_catalog, v_monitor, v_internal, v_func
query_start_epoch | 62
request | insert into my_test values(1);
is_retry | f
SELECT * FROM dc_requests_issued where UPPER(left(request,8)) in ('DROP TAB','TRUNCATE') and (NOW() - time) < '24 hours';
OR
grep -e 'sendAuthRequest: user=Mike' $(admintools -t list_db -d EEVDB | grep -e "Catalog Dir" | cut -d: -f2)/vertica.log
SELECT * FROM error_messages WHERE user_name='Mike' and message ilike '%permission%' ;
-[ RECORD 1 ]---+---------------------------------------
event_timestamp | 2020-05-15 19:42:03.662103-04
node_name | v_eevdb_node0001
user_id | 45035996273851018
user_name | Mike
session_id | v_eevdb_node0001-290527:0x168ac
request_id | 0
transaction_id | 45035996273711676
statement_id | 1
error_level | ERROR
error_code | 16797828
message | Permission denied for relation my_test
detail |
hint |
Here is another example:
Vertica do not have audit of failed queries, if they failed with permission denied error.
@Sergey_Cherepan_1 Please consider the following:
Yes, error message is there. SQL text of failed query is missing.
For audit investigations, full text of failed SQL is desirable.
@Sergey_Cherepan_1 : I had already created a new feature request for this scenario in the past. VER-86008