Hey Jonathan,
The part of OBJ_PRIVILEGE I'm interested in is the auth$privileges part of
the DECODE. Of course I should have led with an example:
CREATE TABLE audtest (mynum NUMBER);
GRANT INDEX ON audtest TO PUBLIC;
SELECT
action_name,obj_privilege,grantee,extended_timestamp
FROM dba_audit_trail
WHERE obj_name = 'AUDTEST'
ORDER BY 3 DESC;
ACTION_NAME OBJ_PRIVILEGE GRANTEE
------------ -------------------------------- ------------
CREATE TABLE
GRANT OBJECT -----Y-------------------------- PUBLIC
SELECT action#, auth$privileges, auth$grantee FROM sys.aud$ WHERE obj$name
= 'AUDTEST';
ACTION# AUTH$PRIVILEGES AUTH$GRANTEE
---------- -------------------------------- ------------
1
17 -----Y-------------------------- PUBLIC
Empirically, I can see that byte 6 is the INDEX priv. However, I have
other rows in the audit for bytes 24, 25, 27, and 28. From other similar
experimentation, byte 28 is the READ privilege. Support (and other, older
sources like AskTom) say the decoding lies in the DBA_OBJ_AUDIT_OPTS column
definitions, which in 19.6 has these audit columns, in order by column ID
number:
alt, aud, com, del, gra, ind, ins, loc, ren, sel, upd, exe, cre, rea, wri,
fbk
The comments on those columns are "Auditing {NAME} WHENEVER SUCCESSFUL /
UNSUCCESSFUL", where "{NAME}" is the name of the privilege corresponding to
the column. So the 2 values for each of the 16 columns could then
correspond to the 32 bytes of OBJ_PRIVILEGE. Checking that with the above
example:
SELECT
action_name,
obj_privilege,
returncode,
SUBSTR(obj_privilege,1,1) "ALT_S",
SUBSTR(obj_privilege,2,1) "ALT_U",
SUBSTR(obj_privilege,3,1) "AUD_S",
SUBSTR(obj_privilege,4,1) "AUD_U",
SUBSTR(obj_privilege,5,1) "COM_S",
SUBSTR(obj_privilege,6,1) "COM_U",
SUBSTR(obj_privilege,7,1) "DEL_S",
SUBSTR(obj_privilege,8,1) "DEL_U",
SUBSTR(obj_privilege,9,1) "GRA_S",
SUBSTR(obj_privilege,10,1) "GRA_U",
SUBSTR(obj_privilege,11,1) "IND_S",
SUBSTR(obj_privilege,12,1) "IND_U",
SUBSTR(obj_privilege,13,1) "INS_S",
SUBSTR(obj_privilege,14,1) "INS_U",
SUBSTR(obj_privilege,15,1) "LOC_S",
SUBSTR(obj_privilege,16,1) "LOC_U",
SUBSTR(obj_privilege,17,1) "REN_S",
SUBSTR(obj_privilege,18,1) "REN_U",
SUBSTR(obj_privilege,19,1) "SEL_S",
SUBSTR(obj_privilege,20,1) "SEL_U",
SUBSTR(obj_privilege,21,1) "UPD_S",
SUBSTR(obj_privilege,22,1) "UPD_U",
SUBSTR(obj_privilege,23,1) "EXE_S",
SUBSTR(obj_privilege,24,1) "EXE_U",
SUBSTR(obj_privilege,25,1) "CRE_S",
SUBSTR(obj_privilege,26,1) "CRE_U",
SUBSTR(obj_privilege,27,1) "REA_S",
SUBSTR(obj_privilege,28,1) "REA_U",
SUBSTR(obj_privilege,29,1) "WRI_S",
SUBSTR(obj_privilege,30,1) "WRI_U",
SUBSTR(obj_privilege,31,1) "FBK_S",
SUBSTR(obj_privilege,32,1) "FBK_U"
FROM
dba_audit_trail
WHERE
obj_name = 'AUDTEST' AND obj_privilege IS NOT NULL;
ACTION_NAME OBJ_PRIVILEGE RETURNCODE
ALT_S ALT_U AUD_S AUD_U COM_S COM_U
---------------------------- -------------------------------- ----------
----- ----- ----- ----- ----- -----
GRANT OBJECT -----Y-------------------------- 0 -
- - - - Y
*(output truncated by width for formatting)*
The "COM" column comment in DBA_OBJ_AUDIT_OPTS says it's for the COMMENT
priv, and not INDEX. So, what am I missing here?
Thanks,
Rich
On Thu, Jul 16, 2020 at 8:44 AM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
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
