This discussion is archived
5 Replies Latest reply: Nov 12, 2013 4:08 AM by Christian Neumueller RSS

Application Change Audit

Shaun Newbie
Currently Being Moderated

Hi

 

Is there anyway to track(audit) the changes made to an application running in our production environment?

 

We only have the runtime installed, so it looks like when we run the page update SQL statements, it does not get populated in the WWV_FLOW_BUILDER_AUDIT_TRAIL table.

 

Any help?

 

We need to see what pages/components where changed when. We dont need to see who did it as we can track the database session using the time the change was made.

 

Regards,

Shaun

  • 1. Re: Application Change Audit
    Christian Neumueller Expert
    Currently Being Moderated

    Hi Shaun,

     

    we do not update such metadata during install.

     

    Maybe you should consider a different approach, via version control. A good practice is to only ever install changes to production from a release branch of your repository. All patch scripts (for data model changes and data migrations), packages and APEX applications should be in version control. You can use the APEX export splitter to partition applications in pages and shared components. The version control system can then tell you what parts changed between the releases, when and why.

     

    Regards,

    Christian

  • 2. Re: Application Change Audit
    Shaun Newbie
    Currently Being Moderated

    Hi Christian

     

    We currently run VisualSVN, so do have version control in place.

     

    Our company audit requirements are such that we need to show all application changes made to the PROD environment, and be able to link them back to a change request.

     

    Is there no way to get the data similar to what the builder captures?  Our DEV environment has the builder installed, and we can see all application changes via page 42 of app 4350 (Administration -> Monitor Activity -> Application Changes, detailed). Even querying the underlying table does not show the same amount of detail in our PROD environment.

     

    Regards,

    Shaun

  • 3. Re: Application Change Audit
    Christian Neumueller Expert
    Currently Being Moderated

    Hi Shaun,

     

    I am not sure how you would provide the link back from an audit record to the change request, but maybe that's specific to your company.

     

    The audit records get written via triggers on the metadata tables. The triggers look at a global variable to detect whether an import is in progress or whether audit logs should be created. Import sets the global before executing the wwv_flow_api procedures, there is no supported way to prevent this.

     

    Another solution would be to create a nightly job that exports all applications from production and checks them into a separate VC repository. The diffs in this repository would represent changes to production. You could again use the splitter to get fine grained information about the changes.

     

    Regards,

    Christian

  • 4. Re: Application Change Audit
    Shaun Newbie
    Currently Being Moderated

    Hi Christian

     

    So if I remove this line from the import scripts

     

    begin wwv_flow.g_import_in_progress := true; end;

     

    Then the audit records will be generated?

     

    We do have translations setup on our app, so need to reseed and publish after any page changes.

     

    What impact would removing that line have?

     

    Regards,

    Shaun

  • 5. Re: Application Change Audit
    Christian Neumueller Expert
    Currently Being Moderated

    Hi Shaun,

     

    that should work, but you would have to try it out. I don't recommend going down this road, though. Bear in mind that these are APEX internals. The g_import_in_progress variable could also be set in other places, it might even be removed or renamed between releases. For example, in 5.0, the assignment is in an API call. You will have to decide whether this audit requirement really can not be met in another way, that you want to make unsupported changes to your production environment. Also, what if somebody forgets to modify the export script before installing on prod, or if the script that changes the install file fails (easy in case of an APEX version change)? The audit log would be incomplete, which would defeat the purpose.

     

    Regards,

    Christian

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points