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.

Comments

  • 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
    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

  • 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';
      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 |

  • 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);
    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            |
    
  • 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 Administrator

    @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
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file