Home » RDBMS Server » Security » How can I attach FGA to a schema?
icon5.gif  How can I attach FGA to a schema? [message #255276] Tue, 31 July 2007 04:45 Go to next message
fastfreeeasy
Messages: 25
Registered: June 2007
Junior Member
Fine-Grained-Auditing on an object of a schema is possible. But, how can I attach Fine-Grained-Auditing to a schema, so that I can log whatever changes done on any object of that schema? Please help me out.
Re: How can I attach FGA to a schema? [message #255284 is a reply to message #255276] Tue, 31 July 2007 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.
Fine-Grained auditing is an object oriented feature not a schema oriented one.

Regards
Michel
Re: How can I attach FGA to a schema? [message #256235 is a reply to message #255284] Fri, 03 August 2007 08:42 Go to previous messageGo to next message
cbruhn2
Messages: 41
Registered: January 2007
Member
Hi fastfreeeasy,

as stated by Michel there is not a single statement for attaching FGA to at schema.
That said you could use the following PL/SQL to setup for all tables belonging to a schema
DECLARE
  CURSOR Get_Tab IS 
    SELECT Table_Name
    FROM   dba_Tables
    WHERE  Table_Owner = 'crb';
BEGIN
  FOR t IN Get_Tab LOOP
    dbms_fga.Add_Policy(Object_scHema => 'crb',Object_Name => t.tName,
                        Policy_Name => 'chk_'                        ||t.tName,
                       Statement_Types => 'insert,update,delete,select');
  END LOOP;
END;
/

You could run this as system administrator. But beware all tables of the schema would be audited.

Best regards
Carl Bruhn
Re: How can I attach FGA to a schema? [message #256243 is a reply to message #256235] Fri, 03 August 2007 09:17 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is true but take care if you add a table it will not be audited. You will then have to execute dbms_fga.Add_Policy to add a new policy.
This is the main difference between an object level feature and a schema level feature.

Regards
Michel
Previous Topic: Cannot change the password for the user sys
Next Topic: Tracking down a user
Goto Forum:
  


Current Time: Fri Mar 29 07:54:40 CDT 2024