Re: Auditing with Unified audit

'Drop table' is not an auditable action. See table 13-1 in the documentation <https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-AUDIT-POLICY-Unified-Auditing.html#GUID-8D6961FB-2E50-46F5-81F7-9AEA314FC693> for the auditable actions.

You'll want to create the policy as such:

> create audit policy all_actions_pol actions all;
>

And then enable it for all users:

> audit policy all_actions_pol
>

Alternately you can enable it for all users with some exceptions:

> audit policy all_actions_pol;
> except dbsnmp;
>

Andy

On Fri, Jun 24, 2022 at 3:29 PM Cee Pee <carlospena999_at_gmail.com> wrote:

> All,
>
> Requirement: we want all actions, including DDL and DML by all users to be
> captured and want to see the SQL statements executed. v19c
>
> I am reading up on Unified auditing. So far I have not come across one
> command that will let me do it. I am looking for something like "CREATE
> AUDIT POLICY test_policy audit all by <user>" or preferably one option to
> audit all actions by all users in one command. Doing it for every table is
> painful and new tables (sometimes even new schemas) get created regularly.
> This is not an OLTP system so not lots of queries but we have long running
> queries by a handful of users (less than 8). So there is going to be little
> overhead. Company security wants all actions by all users to be captured.
>
> It seems there is a command to audit all system actions ("CREATE AUDIT
> POLICY all_actions_pol ACTIONS ALL") which doesnt seem to capture a drop
> table action by a user when I tested; the Unified Audit option is set to
> true in DB after relinking binaries and I also executed
> flush_unified_audit_trail after the drop table session user logged off his
> session.
>
> By the way, we are open to doing either traditional or unified auditing.
>
> CP.
>

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