0

I cannot understand how to create a Unified Audit policy in order to audit all INSERT, UPDATE, DELETE and SELECT statements performed on all the objects within a specific schema.

The goal is to track the tables and views involved in a specific PL/SQL procedure for a reverse engineering task.

For what I understood from the documentation, the only option is to specify each table/view to track within the policy. Is there an "audit all objects within a schema" construct for the create audit policy statement?

0

There is no "audit all objects" setting. In unified auditing, do the following:

-- create the policy
create audit policy my_policy actions all on hr.regions;
alter audit policy my_policy actions all on hr.locations;
...

-- enable the policy
audit policy my_policy;

if you have a schema with a lot of tables, you can use SQL to build your script with something like this:

select 'alter audit policy hr_policy actions all on '||owner||'.'||table_name||';'
  from dba_tables
 where owner in ('HR','OE');
| improve this answer | |
0

Creating a policy is not necessary

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY hr, oe; 

will do the job

| improve this answer | |
  • In unified auditing, creating a policy is necessary. – pmdba Jun 8 at 13:00
  • 1
    also, these commands are for actions committed by hr and oe, not for actions on tables owned by hr and oe. – pmdba Jun 8 at 13:34
0

The following AUDIT statements are not limited only to 1 schema, but to all.

AUDIT ALL BY USER_NAME BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY USER_NAME BY ACCESS;
AUDIT EXECUTE,PROCEDURE BY USER_NAME BY ACCESS;

Keep in mind that your audit_trail has to be set to DB,EXTENDED

Check your current audit_trail setting with:

SHOW PARAMETER AUDIT;

If it's not set to DB,EXTENDED , you have to set it with:

alter system set audit_trail=db,extended scope=spfile;

In order the change of audit_trail take effect, you have to restart your DB instance.

You will find audit records in SYS.AUD$ afterwards.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.