Is it possible to get the all changes that is made to schema as in what privileges was first assigned to the schema on creation, creation date, if later modified with different privilege what was that priv, when the schema was dropped, basically this would be needed for audit purpose at the end of the year where they would be asking for all the info for the whole year for select schemas.

Is is possible to fetch this data from the database? I have enabled standard DB auditing for sys operations to be TRUE. Version and 11gR2.


That's exactly the kind of thing that oracle AUDIT is created for. The basic view is DBA_AUDIT_TRAIL, though there are several others. By defalt, Oracle has some auditing already enabled, but you may want to review and modify. Also you want to use the dbms_audit_mgmt package to move the audit tables out of the SYSTEM tablespace into their own dedicated tablespaces, as well as to do normal housekeeping. Details are in Part VI of the Security Guide.

|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.