I am trying to enable auditing on a Schema for all the Select/Insert statements fired on the objects of this schema.
SQL> audit select on abc_owner by access;
audit select on abc_owner by access
ERROR at line 1:
ORA-00942: table or view does not exist
From here i can understand that i will have to mention objects of this schema.But i have around 500 objects in this schema.
Is there any option through which i can enable auditing at schema level?
SQL> connect scott/tiger
SQL> select 'audit select on ' || table_name || ' to FOOBAR;' from user_tables;
audit select on DEPT to FOOBAR;
audit select on EMP to FOOBAR;
audit select on BONUS to FOOBAR;
audit select on SALGRADE to FOOBAR;
audit select on STAT_TABLE to FOOBAR;
audit select on EMPLOYEE_TERMINATIONS_LOG to FOOBAR;
audit select on PROCESS_DATA to FOOBAR;
audit select on DATA_STAGING_REPOS to FOOBAR;
8 rows selected.
yes,you are absolutely right. I was also thinking for this.But i am looking for some option like
Audit SELECT on Schema_Name;
You can't audit a schema. It has to be an object IN that schema use SQL to generate the sql statements to do that
select 'audit select on '||owner||'.'||table_name||';' from dba_tables where owner = '<schema_name>';
spool that output to a file, then run the script
What will be the reason behind this,such schema level auditing is not available?
it does not exist because it is impractical to implement.
for the sake of discussion say that one could enable "schema level auditing".
What must occur within the DB when a new object is CREATEd within this schema?
How does Oracle remember that this new object should be audited & for which types of access?
There is no way to enable auditing for particular schema's objects as there is no such a syntax.
All type of audit statements have one rule. You can not refer user(schema) name in Audit statement if you have refer Object name in it and vice-verse. So the way by which you can achieve what you want is to use
SQL > select 'audit select on ' || owner.table_name || ' by access ' from dba_tables where owner='target_schema;
If you have less numbers of sensitive objects then alternatively you can use FGA.