Home » RDBMS Server » Server Administration » Unified Auditing Trail to find grants of privileges on an object (Oracle Database Version 19.11.0.0.0)
Unified Auditing Trail to find grants of privileges on an object [message #684692] Tue, 27 July 2021 05:32 Go to next message
wtolentino
Messages: 348
Registered: March 2005
Senior Member
we used to select from this DBA_AUDIT_STATEMENT view to find info about grants of privileges on an object. currently we are now in 19c and using the unified auditing -- UNIFIED_AUDIT_TRAIL.

everytime we issue the command for example:

grant select on table1 to user1;
grant select on table1 to role1;

this didn't appears recording into the UNIFIED_AUDIT_TRAIL view. we don't see any rows that are related to the granting of an objects.

checking the v$option returns true for the parameter 'Unified Auditing':

select value from v$option where parameter='Unified Auditing';

we also tried to enable the audits by this:

audit grant procedure;
audit grant sequence;
audit grant table;

still not working.

how do we set or turn on the audits to grants of privileges on an object? please help.

thank you,
warren
Re: Unified Auditing Trail to find grants of privileges on an object [message #684693 is a reply to message #684692] Tue, 27 July 2021 06:49 Go to previous messageGo to next message
John Watson
Messages: 8622
Registered: January 2010
Location: Global Village
Senior Member
You configure Unified Audit with the CREATE AUDIT POLICY command.
Re: Unified Auditing Trail to find grants of privileges on an object [message #684695 is a reply to message #684693] Tue, 27 July 2021 07:12 Go to previous messageGo to next message
wtolentino
Messages: 348
Registered: March 2005
Senior Member
thanks John. can you give an example. i need to audit on all commands that issues grants like select, insert, update, delete on tables.

for example:

grant select on table1 to user1;
grant select on table1 to role1;

how do use the CREATE AUDIT POLICY? i tried this but looks like it is not correct.

CREATE AUDIT POLICY table_privilege_grant_policy
privileges grant select any table, grant delete any table, grant update any table, grant insert any table;
privileges grant select any table, grant delete any table, grant update any table, grant insert any table
*
ERROR at line 2:
ORA-46355: missing or invalid privilege audit option.

thank you.
Re: Unified Auditing Trail to find grants of privileges on an object [message #684696 is a reply to message #684695] Tue, 27 July 2021 11:19 Go to previous messageGo to next message
John Watson
Messages: 8622
Registered: January 2010
Location: Global Village
Senior Member
orclz>
orclz> CREATE AUDIT POLICY table_privilege_grant_policy
  2  privileges select any table, delete any table,update any table, insert any table;

Audit policy created.

orclz>

Please note How to use [code] tags and make your code easier to read
Re: Unified Auditing Trail to find grants of privileges on an object [message #684697 is a reply to message #684696] Tue, 27 July 2021 13:19 Go to previous messageGo to next message
wtolentino
Messages: 348
Registered: March 2005
Senior Member
thanks john. i successfully created the policy and enabled it. then grant privileges to a user. when i query on the UNIFIED_AUDIT_TRAIL views i don't see the grants in there. i think i am missing something.

SQL> CREATE AUDIT POLICY table_privilege_grant_policy
  2   privileges select any table, delete any table,update any table, insert any table;

Audit policy created.

SQL> audit policy table_privilege_grant_policy;

Audit succeeded.

SQL> grant select on table1 to user1;

Grant succeeded.

SQL> grant insert on table1 to user1;

Grant succeeded.

SQL> grant delete on table1 to user1;

Grant succeeded.

SQL> grant update on table1 to user1;

Grant succeeded.


select action_name, sql_text from UNIFIED_AUDIT_TRAIL order by event_timestamp desc;

ACTION_NAME          SQL_TEXT
-------------------- ------------------------------------------------------------
AUDIT	             audit policy table_privilege_grant_policy
CREATE AUDIT POLICY  "CREATE AUDIT POLICY table_privilege_grant_policy privileges select any table, delete any table,update any table, insert any table
Re: Unified Auditing Trail to find grants of privileges on an object [message #684698 is a reply to message #684697] Tue, 27 July 2021 13:40 Go to previous messageGo to next message
Michel Cadot
Messages: 67996
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You audit the named privileges which do not include GRANT.
If you want to audit GRANT then your audit policy must be something like:
CREATE AUDIT POLICY grant_policy ACTIONS grant;

[Updated on: Tue, 27 July 2021 13:40]

Report message to a moderator

Re: Unified Auditing Trail to find grants of privileges on an object [message #684699 is a reply to message #684698] Tue, 27 July 2021 14:54 Go to previous message
wtolentino
Messages: 348
Registered: March 2005
Senior Member
thanks so much Michel that works.

SQL> create audit policy grant_policy actions grant;

Audit policy created.

SQL> audit policy grant_policy;

Audit succeeded.

-- then i disconnect and reconnect again

SQL> grant select on table1 to user1;

Grant succeeded.

SQL> grant update on table1 to user1;

Grant succeeded.

select action_name, sql_text from UNIFIED_AUDIT_TRAIL order by event_timestamp desc;

ACTION_NAME          SQL_TEXT
-------------------- ------------------------------------------------------------
GRANT	             grant update on table1 to user1
GRANT	             grant select on table1 to user1
CREATE AUDIT POLICY  create audit policy grant_policy actions grant
AUDIT	             audit policy table_privilege_grant_policy

[Updated on: Tue, 27 July 2021 15:48]

Report message to a moderator

Previous Topic: ORA-01805 Timezone files error
Next Topic: Broken Job
Goto Forum:
  


Current Time: Tue Oct 19 00:48:15 CDT 2021