Queries failed with error "Permission denied for relation XXX" are missed from dc_request_issued
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
-5
This discussion has been closed.
Answers
@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!
@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.
thank you for confirming. may be it will be added to a new dc table in future.