Rich,
If you look at the view definition of dba_audit_trail you can find:
decode(aud.action#,
108 /* grant sys_priv */, null,
109 /* revoke sys_priv */, null,
114 /* grant role */, null,
115 /* revoke role */, null,
auth$privileges)
/* OBJ_PRIVILEGE */,
"aud" refers to sys.aud$ - from the column comment (dsec.bsq):
action# number not null, /* action responsible for
auditing */
auth$privileges varchar2(32), /* granted
privileges */
The decode() and comments don't give an immediate clue, but if you look at
the table suggested by Jeff (audit_action) - in particular the values
excluded by the decode()
SQL> select * from audit_actions where action in (108,109,114,115);
ACTION NAME
---------- --------------------------------
108 SYSTEM GRANT
109 SYSTEM REVOKE
114 GRANT ROLE
115 REVOKE ROLE
It's a perfect match with the view definition.
Regards
Jonathan Lewis
On Thu, Jul 16, 2020 at 1:50 PM Rich J <rich242j@xxxxxxxxx> wrote:
Hey Jeff,
If that's the case, then how do the 239 rows of that table translate to
the 32 bytes of OBJ_PRIVILEGE column of the DBA_AUDIT_TRAIL view? The name
values of the audit actions would seem to indicate a statement rather than
the "Object privileges granted or revoked by a GRANT or REVOKE statement"
as the documentation says.
Also, the documentation at
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_AUDIT_TRAIL.html
says that OBJ_PRIVILEGE is a VARCHAR2(16), but the dictionaries created on
my 19.6 databases disagree.
Thanks,
Rich
On Wed, Jul 15, 2020 at 3:39 PM Jeff Smith <jeff.d.smith@xxxxxxxxxx>
wrote:
This is the lookup table for those codes, Rich
sys.audit_actions
Jeff
*From:* Rich J <rich242j@xxxxxxxxx>
*Sent:* Wednesday, July 15, 2020 3:15 PM
*To:* oracle-l@xxxxxxxxxxxxx
*Subject:* Re: How to parse OBJ_PRIVILEGE column of DBA_AUDIT_TRAIL in
19?
I think it's interesting that even Oracle Support doesn't know the answer
to this. They keep giving me untested answers from Development, leaving me
to (dis)prove them. It amazes me that I'm apparently the only one who has
asked this question, as the documentation has been missing/wrong for
several versions.
I've come to the conclusion that I must not have auditing (traditional --
not unified yet) configured correctly. That, or no one asks what privs
were granted to trigger an audit row from being generated.
Sigh.
Rich
On Wed, Jun 10, 2020 at 8:04 AM Rich J <rich242j@xxxxxxxxx> wrote:
Hey all,
Looking through DBA_AUDIT_TRAIL of a 19.6 database on OL7.7, I'm trying
to decode the OBJ_PRIVILEGE column on rows with GRANT OBJECT in the
ACTION_NAME column. The official doc on the view:
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_AUDIT_STATEMENT.html
...doesn't say what the column is. AskTom said in v9.2 that it mirrors
the SES_ACTIONS column:
https://asktom.oracle.com/pls/asktom/asktom.search?tag=how-to-track-logon-logout#45725388888922
And that's what the v19 doc also says, but the OBJ_PRIVILEGE column is
now 32 characters, and I've got hits on the upper columns.
Anyone know where that documentation could be? FWIW, the docs for v20
appear to be the same as the v19 ones for this.
Thanks,
Rich
