| Unified Auditing Trail to find grants of privileges on an object [message #684692] |
Tue, 27 July 2021 05:32  |
wtolentino
Messages: 421
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 #684695 is a reply to message #684693] |
Tue, 27 July 2021 07:12  |
wtolentino
Messages: 421
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 #684697 is a reply to message #684696] |
Tue, 27 July 2021 13:19 |
wtolentino
Messages: 421
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 #684699 is a reply to message #684698] |
Tue, 27 July 2021 14:54 |
wtolentino
Messages: 421
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
