Re: How to parse OBJ_PRIVILEGE column of DBA_AUDIT_TRAIL in 19?

I should have explained that "IND" is the 6th column, which matches perfectly. It does not explain then why READ is the 18th (not 28th, as stated above) column:

GRANT READ ON audtest TO PUBLIC;

SELECT
action_name,
obj_privilege,
grantee
FROM dba_audit_trail
WHERE
obj_name = 'AUDTEST'
AND obj_privilege IS NOT NULL;

ACTION_NAME  OBJ_PRIVILEGE                    GRANTEE
------------ -------------------------------- ------------
GRANT OBJECT -----Y-------------------------- PUBLIC
GRANT OBJECT -----------------Y-------------- PUBLIC

Rich

On Thu, Jul 16, 2020 at 10:00 AM Rich J <rich242j_at_gmail.com> wrote:

> 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_at_gmail.com>
> 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_at_gmail.com> 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_at_oracle.com>
>>> wrote:
>>>
>>>> This is the lookup table for those codes, Rich
>>>>
>>>>
>>>>
>>>> sys.audit_actions
>>>>
>>>>
>>>>
>>>> Jeff
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> *From:* Rich J <rich242j_at_gmail.com>
>>>> *Sent:* Wednesday, July 15, 2020 3:15 PM
>>>> *To:* oracle-l_at_freelists.org
>>>> *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_at_gmail.com> 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
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>

--
http://www.freelists.org/webpage/oracle-l