How to enable/disable database Audits?
Sankarmn
Community Edition User ✭✭
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
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:
SELECT DISTINCT table_name, component, description FROM data_collector where table_name ilike '%requests%'; table_name | component | description ---------------------------+----------------------+------------------------------------------------------ dc_lock_requests | LockRequests | History of lock requests dc_requests_retried | RequestsRetried | History of all SQL requests issued that were retried dc_mergeout_requests | MergeoutRequests | History of mergeout request tracker requests dc_requests_issued | RequestsIssued | History of all SQL requests issued dc_import_export_requests | ImportExportRequests | History of import/export requests dc_requests_completed | RequestsCompleted | History of all SQL requests completed (6 rows)To see those components collector retention policy do:
SELECT get_data_collector_policy('requestsissued'); get_data_collector_policy ------------------------------------------------------------------------------ 2000KB kept in memory, 256000KB kept on disk. Time based retention disabled. (1 row) SELECT get_data_collector_policy('RequestsCompleted'); get_data_collector_policy ------------------------------------------------------------------------------ 2000KB kept in memory, 256000KB kept on disk. Time based retention disabled.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:
WITH mytimes AS (SELECT now() - interval '1 day' AS qstart, now() AS qend) SELECT user_name, CASE WHEN qr.request ilike '%swap_partitions_between_tables%' THEN 'SWAP_PARTITIONS' ELSE UPPER(left(qr.request, 5)) END AS DMLDDL, COUNT(1) FROM query_requests qr , mytimes WHERE ( qstart between qr.start_timestamp and qr.end_timestamp OR qend between qr.start_timestamp and qr.end_timestamp OR (qr.start_timestamp < qstart AND qr.end_timestamp > qend) OR (qr.start_timestamp > qstart AND qr.end_timestamp < qend) ) AND qr.node_name = (select local_node_name()) GROUP BY 1,rollup(DMLDDL) ORDER BY 1,3; user_name | DMLDDL | COUNT -----------+-----------------+------- dbadmin | SWAP_PARTITIONS | 7 dbadmin | ALTER | 24 dbadmin | COPY | 25 dbadmin | DROP | 25 dbadmin | CREAT | 50 dbadmin | WITH | 61 dbadmin | SELEC | 131 dbadmin | | 323 (8 rows)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