Re: Auditing with Unified audit

Thanks Andy and Mark. Perhaps I should have been clearer earlier. We want to capture all DDL and DML statements (executed by all end users). There are not very many concurrent users in the system, at most there will be 3 to 5 concurrent users, normally a couple of them. The queries run in terms of minutes, not seconds. So auditing all DDL and DML statements will not cause a big overhead.

Andy, per the manual "create audit policy all_actions_pol actions all" only captures DDL statements. It would be nice to know if there is an equivalent for DML.

On Mon, Jun 27, 2022 at 10:25 AM Powell, Mark <mark.powell2_at_dxc.com> wrote:

> Cee Pee, what you have stated you want to do involves a lot of
> performance overhead and storage space. Personally, I do not think it is
> a practical requirement and you can find support for my position in various
> Oracle support documents.
>
> "Auditing everything is not realistic. Auditing specific sensitive columns
> is what is intended."
> 12c Unified Auditing used with Data Guard (Doc ID 2021747.1)
>
>
> Mark Powell
> Database Administration
> (313) 592-5148
>
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Andy Wattenhofer <dmarc-noreply_at_freelists.org>
> *Sent:* Friday, June 24, 2022 4:55 PM
> *To:* carlospena999_at_gmail.com <carlospena999_at_gmail.com>
> *Cc:* Oracle-L Freelists <oracle-l_at_freelists.org>
> *Subject:* Re: Auditing with Unified audit
>
> 'Drop table' is not an auditable action. See table 13-1 in the
> documentation
> <https://clicktime.symantec.com/38h79ECGzGM1s79q6RbhgHx6xn?u=https%3A%2F%2Fdocs.oracle.com%2Fen%2Fdatabase%2Foracle%2Foracle-database%2F19%2Fsqlrf%2FCREATE-AUDIT-POLICY-Unified-Auditing.html%23GUID-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