Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Chris Saxon

Thanks for the question.

Asked: March 13, 2023 - 3:47 pm UTC

Last updated: November 08, 2024 - 5:04 pm UTC

Version: 19.16.0.0.0.

Viewed 1000+ times

You Asked

Dear Sirs,

I'm using database version 19c and trying to set a filter on schema level before inserting into UNIFIED_AUDIT_TRAIL view.

I tried these example policies but without success:

CREATE AUDIT POLICY test1
 ACTIONS 
 UPDATE ON <schemaname>.*
WHEN 'INSTR(UPPER(SYS_CONTEXT(''USERENV'',''CLIENT_PROGRAM_NAME'')), ''FRMWEB'') = 0'
EVALUATE PER SESSION
ONLY TOPLEVEL;

CREATE AUDIT POLICY test2
 ACTIONS 
 UPDATE ON <schemaname>
WHEN 'INSTR(UPPER(SYS_CONTEXT(''USERENV'',''CLIENT_PROGRAM_NAME'')), ''FRMWEB'') = 0'
EVALUATE PER SESSION
ONLY TOPLEVEL;


Also there is in my opinion no paramter, like obj_owner, in the namespace userenv in sys_context.

Do you have a hint please or is logging on schema level not possible?

Many thanks

Juergen

and Chris said...

You are correct. Currently, there is no option to do this at the schema level. There are plans to support this.

To help the team build this it would help if you could let us know:

How many objects (tables) are to be audited under the specific schema?
How frequently this list would change? Ie, how frequently new objects are created under the schema? Once a month or Once 3 months (whenever application patch applied, etc)

Rating

  (1 rating)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

ER for Unified Auditing

T1DSoldier, November 08, 2024 - 3:33 pm UTC

Hello All,
There is an Enhancement Request in for unified auditing. I am unable to see it but hopefully its getting traction
Enhancement Request# 32784347 - NEED A GENERIC POLICY : ACTIONS=ALL , EXCEPT SELECT,...ETC

I had asked a question there earlier this year and this was that was the reply. Im not sure if someone else had requested similar and its not my ER so I cant view it.
I did submit an SR checking if there is an update on it.

https://community.oracle.com/mosc/discussion/4563783/enhancement-request-for-unified-auditing#latest

I really enjoy these collaboration communities, its really a great place to see different views and how many different ways to accomplish a goal.

Thanks again
Dave
Chris Saxon
November 08, 2024 - 5:04 pm UTC

I don't have any timescales for when that ER will be delivered. If this is important for you then raise this with support - this can help raise its priority
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.