How to enable/disable database Audits?

Sankarmn

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.

mosheg

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

mosheg

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;

Sankarmn

@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

mosheg

1. 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

2. 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

3. SELECT * FROM dc_requests_issued where UPPER(left(request,8)) in ('DROP TAB','TRUNCATE') and (NOW() - time) < '24 hours';

4. vsql -xc "select * from USER_SESSIONS where user_name='Mike';"
   OR
   grep -e 'sendAuthRequest: user=Mike' $(admintools -t list_db -d EEVDB | grep -e "Catalog Dir" | cut -d: -f2)/vertica.log

5. 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 |

mosheg

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)

Sergey_Cherepan_1

Vertica do not have audit of failed queries, if they failed with permission denied error.

mosheg

@Sergey_Cherepan_1 Please consider the following:

SELECT VERSION(); --  Vertica Analytic Database v12.0.4-6
CREATE USER user104;
CREATE TABLE dbadmin_table (f1 int);
INSERT INTO dbadmin_table VALUES (1);
COMMIT;

\c - user104
You are now connected as user "user104".
SELECT * FROM dbadmin_table;
vsql:3test.sql:11: ERROR 4367:  Permission denied for relation dbadmin_table

\c - dbadmin
You are now connected as user "dbadmin".
\x
SELECT * FROM error_messages WHERE user_name='user104';
-[ RECORD 1 ]---+---------------------------------------------
event_timestamp | 2023-05-28 17:42:00.412878+03
node_name       | v_eondb_node0001
user_id         | 45035996273851866
user_name       | user104
session_id      | v_eondb_node0001-359523:0x18f25e
request_id      | 0
transaction_id  | 45035996273742525
statement_id    | 1
error_level     | ERROR
error_code      | 16797828
message         | Permission denied for relation dbadmin_table
detail          |
hint            |

Sergey_Cherepan_1

Yes, error message is there. SQL text of failed query is missing.
For audit investigations, full text of failed SQL is desirable.

SruthiA

@Sergey_Cherepan_1 : I had already created a new feature request for this scenario in the past. VER-86008