Question
Chris Bebek · Sep 20

Audit failed crud queries/grants/revokes

Trying to identify which records in the %SYS.Audit table are fails.

Eg, as user "WORKER", I run an attempted a grant, the terminal returns:

SQL> GRANT SELECT ON newschema.patients TO COORDINATOR
[S1000][Iris ODBC][State : S1000][Native Code 112]
[libirisodbc35.so]
[SQLCODE: <-112>:<Access violation>]
[Location: <ServerLoop>]
[%msg: <User WORKER does not have required privileges to grant the privilege(s)>]
[ISQL]ERROR: Could not SQLExecute
 

but the record in the audit table gives

Description: "SQL GRANT Statement"

EventData: "GRANT SELECT ON newschema.patients TO COORDINATOR"

Is there any indication in the audit table that this grant failed?

##############

Separately, I run a select without sufficient permissions

SQL> SELECT * FROM newschema.table2
[S1000][Iris ODBC][State : S1000][Native Code 99]
[libirisodbc35.so]
[SQLCODE: <-99>:<Privilege violation>]
[Location: <Prepare>]
[%msg: <User WORKER is not privileged for the operation>]
[ISQL]ERROR: Could not SQLPrepare

I don't get a record for the attempted query in the audit table at all -  just the surrounding login/logout.

How do I capture failed CRUD in the audit table?

 

TIA

Product version: IRIS 2021.2
$ZV: 1.0
0
0 98
Discussion (2)2
Log in or sign up to continue

For the second part of the question do you have the '%System/%SQL/PrivilegeFailure' System Audit Event enabled?

hi, yes every system audit event is enabled

1) failed select due to insufficient privileges aren't recorded

2) moreover, failed queries due to syntax error aren't recorded either

can anybody confirm whether the audit feature is behaving correctly? i.e. cases 1 and 2 are not expected to be added to the %SYS.Audit table ?