The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How to enable/disable database Audits?

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


  • moshegmosheg Vertica Employee Administrator

    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
    Will display whether your database size follows your Vertica license agreement based on the last audit.

    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');
         2000KB kept in memory, 256000KB kept on disk. Time based retention disabled.
        (1 row)
    SELECT get_data_collector_policy('RequestsCompleted');
     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:

  • moshegmosheg Vertica Employee Administrator

    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;

  • SankarmnSankarmn Community Edition User ✭✭

    @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

  • moshegmosheg Vertica Employee Administrator
    edited May 2020
    1. Consider the following example:

      CREATE USER Mike IDENTIFIED BY 'MikePasword';
      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';"
      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 |

  • moshegmosheg Vertica Employee Administrator

    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.

  • moshegmosheg Vertica Employee Administrator

    @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);
    \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".
    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            |
  • Yes, error message is there. SQL text of failed query is missing.
    For audit investigations, full text of failed SQL is desirable.

  • SruthiASruthiA Vertica Employee Employee

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file