Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Mauro.

Asked: January 14, 2025 - 8:03 am UTC

Last updated: January 20, 2025 - 8:59 am UTC

Version: 19.16

Viewed 100+ times

You Asked

I activated audit on some just for unsuccessful connection attempts ( my purpose was finding who/what locks some users ).
This is something I had already done many times, always fine.
However on a 19 version I noted something I could not find an explanation for:
It appears that an additional audit popped out of nowhere, a select audit on sys owned tables:
HIST_HEAD$ and HISTGRM$
I had to change audit_trail parameter, so no chance that they are some kind of inheritance from previous audit ( and I truncated aud$ in order to have a clean start ).
However I would never ever dream of touching sys owned tables, my first commandment is "you shall not touch sys onwed tables ( unless under oracle support supervision, of course and with the only exception of aud$ ).
I perused docs and Metalink but I was unable to find any relevant info on this.
On old 10 and 11 version I never saw this.
Is this a new kind of feature of 19 version?
I even tried do disable this audit, no luck, from inside the pluggable db it complains because the operation is not allowed, from the root it gives me another error ...

I hope that my poor English is clear enough

Have a nice day

Mauro Papandrea

and Chris said...

What exactly were the commands you used to enable auditing? What exactly are the audit records on these sys owned tables?

While I believe you haven't queried these sys tables, it's possible the tools you're using and/or background jobs do.

To provide effective help we need to see what the commands you used to enable this are though - otherwise we're just guessing here.

Side note: if you want to reset the audit trail, you should use

exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,FALSE);


Instead of truncating the aud$.

Rating

  (3 ratings)

Comments

Mauro Papandrea, January 15, 2025 - 4:03 pm UTC

First of all, thank you for your time
I used this:
AUDIT CONNECT WHENEVER NOT SUCCESSFUL;

Here there are 2 sample records:
Scott.Tiger scott_RW scott1 tiget 13/01/2025 09:13:57 SYS HISTGRM$ 3 SELECT 15255520 11 39 0 13/01/2025 09:13:57,468123 +01:00 0 12881 15001800DD29CA00 10568259494109 #1(6):571092 #2(1):2 SELECT BUCKET, ENDPTHASH, ENDPTVAL, UTL_RAW.CAST_TO_RAW(ENDPTVAL) ENDPTVAL_RAW FROM SYS.EXU8HSTU WHERE POBJID = :1 AND INTCOL = :2 ORDER BY BUCKET 3271397905 scott_RW
Scott.Tiger scott_RW scott1 tiget 13/01/2025 09:13:57 SYS HIST_HEAD$ 3 SELECT 15255520 15 39 0 13/01/2025 09:13:57,538437 +01:00 0 12881 1800120010F72400 10568259494550 #1(6):571092 #2(1):3 SELECT BUCKET, ENDPTHASH, ENDPTVAL, UTL_RAW.CAST_TO_RAW(ENDPTVAL) ENDPTVAL_RAW FROM SYS.EXU8HSTU WHERE POBJID = :1 AND INTCOL = :2 ORDER BY BUCKET 3271397905 scott_RW

I just changed e few values ( username, terminal, os_username )


I used:
SELECT * FROM DBA_AUDIT_TRAIL
where owner = 'SYS'
and a few filter conditions, just to get a couple of records


I am pretty sure that non even a tool can have queried sys table because that user ( and all the others as well ) have just a minimum set of privileges that allows only to query ( and write ) tables from a single schema, no system wide privileges such as select any table

I am not using unified auditing, is your command for reset audit trail still valid?

Regards

Mauro
Chris Saxon
January 15, 2025 - 6:01 pm UTC

Those queries return histogram details. While it's unlikely anything runs those queries directly, they may be run recursively for anything that's getting column information - for example the info command in SQLcl/SQL Developer. I can see that this generates similar audit records for me.

e.g. try running:

info <some_table>

and you'll likely see new entries in the audit.

Good spot on the command - the idea is still valid; set the first parameter to whatever it is you want to clean. Find the values at:

https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_AUDIT_MGMT.html#GUID-1EB3BBB1-4FC3-4BC3-8490-8300C950AC27__BABIDGEA

That said, traditional auditing is desupported in 23ai. You really should be moving to unified auditing.

Mauro Papandrea, January 16, 2025 - 2:22 pm UTC

However this behavior does not show in previous versions ( at least not in versions < 12, I have no 12 at hand to try right now ).
I wonder what might be the usage of those records, namely the origin behind this change.
Of course it is not a great problem, just curious about it

As to version 23, was it already released on premises as well?
I was unable to find it, the last version I can see is 19

Regards

Mauro Papandrea

Chris Saxon
January 20, 2025 - 8:59 am UTC

Accessing histogram information was added to the list of mandatorily audited activities. So this will always be audited, regardless of your other audit policies. I'm not sure exactly when this changed.

https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/administering-the-audit-trail.html#GUID-AA781864-5756-464E-AFB6-675625AF0EF5

23ai is not available on premises yet; see MOS note for timescales: Release Schedule of Current Database Releases (Doc ID 742060.1)

https://support.oracle.com/epmos/faces/DocContentDisplay?id=742060.1

If you want to use 23ai now, you can download Oracle Database Free https://www.oracle.com/database/free/
Or sign up for an Always Free Autonomous Database on Oracle Cloud: https://www.oracle.com/free/

Mauro Papandrea, January 20, 2025 - 10:08 am UTC

Thank you very much

Have a nice day

Mauro
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 latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Multitenant

Need more information on Multitenant? Check out the Multitenant docs for the Oracle Database