Re: Auditing with Unified audit

Right, from the documentation it looks like that sort of a shorthand option for DDL just isn't offered.

Maybe you could get what you need with the enable_ddl_logging <https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/ENABLE_DDL_LOGGING.html#GUID-6FBA6147-D545-4E7D-94F0-A97EC1C721AE> parameter. I've never used it, so I cannot say whether it captures user or session information.

Andy

On Mon, Jun 27, 2022 at 2:00 PM Cee Pee <carlospena999_at_gmail.com> wrote:

> 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