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

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
------------ -------------------------------- ------------

GRANT OBJECT -----Y-------------------------- PUBLIC

   ACTION# AUTH$PRIVILEGES                  AUTH$GRANTEE
---------- -------------------------------- ------------
         1
        17 -----Y-------------------------- PUBLIC

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"

ACTION_NAME                  OBJ_PRIVILEGE                    RETURNCODE

---------------------------- -------------------------------- ----------
----- ----- ----- ----- ----- -----
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

>
>

>

>


>     ACTION NAME

> ---------- --------------------------------


>        108 SYSTEM GRANT

>        109 SYSTEM REVOKE

>        114 GRANT ROLE

>        115 REVOKE ROLE

>

>

>
>

>
>> 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