I am planning to create a FGA on a table with below criteria and facing a difficulty as given below. Please let me know if someone has come across similar situation and have handled it.
Application Table - APPTBL
It has three columns 1) USERID 2) ROLEID 3) STATUS
I need to create a FGA on APPTBL with the criteria
statement_type - INSERT
audit_condition - USERID LIKE '%_%' AND ROLEID <> '34'
I appears I cannot use "AND" operator when using DML statement_type. In my case it is INSERT. Because I am getting ORA-28138 error. It will work for SELECT but NOT for DML statements.
I created a helper function thinking I can use USERENV(CURRENT_SQL), USERENV(CURRENT_BIND) and compare the values using INSTR or regexp_count. but Those values are passed as NULL to the helper function. I verified Oracle doc and realized that is the way it is suppose to be.
I also looked into Oracle document(How to use DBMS_FGA with a complex audit_condition (include/exclude multiple users) (Doc ID 832856.1)) but NO luck.
My requirement is to capture dynamic values from two columns and audit the transaction if the values match the audit requirement.