Queries failed with error "Permission denied for relation XXX" are missed from dc_request_issued

edited March 2023 in General Discussion

Hi,

On v 12.0.3, queries that failed with error "Permission denied for relation XXX" are missed from dc_request_issued.
That is a problem for audit - I would like to see SQL text in dc_requests_issued. For same reason failed logins are saved in login_failures.

Problem is easy to reproduce. Create table, do not give privileges on it to some user, and run select from this table by this user. Then, try to find this select in dc_requests_issued.

Vertica log still have this select, it is being logged properly there, together with error text.

Thank you

Answers

  • SruthiASruthiA Vertica Employee Administrator

    @Sergey_Cherepan_1 : Yes.. you are right.. I am able to reproduce it on 12.0.3. I will create a JIRA for it.

    vsql -U test
    Password:
    Welcome to vsql, the Vertica Analytic Database interactive terminal.

    Type: \h or \? for help with vsql commands
    \g or terminate with semicolon to execute query
    \q to quit

    eonv1203=> select * from test_perm;
    ERROR 4367: Permission denied for relation test_perm
    eonv1203=> \q

    Login as dbadmin user:

    vsql
    Welcome to vsql, the Vertica Analytic Database interactive terminal.

    Type: \h or \? for help with vsql commands
    \g or terminate with semicolon to execute query
    \q to quit

    eonv1203=> select * from dc_requests_issued where request ilike '%test_perm%';
    -[ RECORD 1 ]-----+--------------------------------------------------------------------
    time | 2023-03-03 08:57:26.871179-05
    node_name | v_eonv1203_node0001
    session_id | v_eonv1203_node0001-291888:0xe82b5
    user_id | 45035996273704962
    user_name | dbadmin
    transaction_id | 45035996273727406
    statement_id | 1
    request_id | 1
    request_type | DDL
    label |
    client_label |
    search_path | "$user", public, v_catalog, v_monitor, v_internal, v_func
    query_start_epoch | 17
    request | create table test_perm(a int);
    is_retry | f
    digest |
    -[ RECORD 2 ]-----+--------------------------------------------------------------------
    time | 2023-03-03 08:58:07.69841-05
    node_name | v_eonv1203_node0001
    session_id | v_eonv1203_node0001-291888:0xe82e6
    user_id | 45035996273704962
    user_name | dbadmin
    transaction_id | 45035996273727409
    statement_id | 1
    request_id | 1
    request_type | QUERY
    label |
    client_label |
    search_path | "$user", public, v_catalog, v_monitor, v_internal, v_func
    query_start_epoch | 17
    request | select * from dc_requests_issued where request ilike '%test_perm%';
    is_retry | f
    digest | -8998542267728365721

  • Big thanks Sruthi!

  • SruthiASruthiA Vertica Employee Administrator

    @Sergey_Cherepan_1 : since it is erroring out with permission denied, it is not getting logged in dc_requests_issued. Do you want it to be added to dc table?

  • That is correct, I want to see it added to dc_requests_issued.
    Otherwise user activity is leaving no traces.

  • SruthiASruthiA Vertica Employee Administrator

    thank you for confirming. may be it will be added to a new dc table in future.

This discussion has been closed.