Home » RDBMS Server » Security » Change control for PL/SQL
Change control for PL/SQL [message #635566] Wed, 01 April 2015 06:51 Go to next message
John Watson
Messages: 7953
Registered: January 2010
Location: Global Village
Senior Member
Hello - I'm looking for a facility that will track changes to code, some sort of source code control system. The emphasis is not so much on managing change, as detecting change. I've been Googling around, but haven't found anything that would help. The closest is the Oracle Datbase Firewall, which can scan all stored objects and report on any changes. That would do, but it is not possible for me to install the Firewall just for that.

Anyone know of any third party products to do this sort of thing?

Thank you for any insight.
Re: Change control for PL/SQL [message #635567 is a reply to message #635566] Wed, 01 April 2015 07:53 Go to previous messageGo to next message
Michel Cadot
Messages: 66473
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't know any tool but I remember T. Kyte posted a trigger to record code change (of course it is stored PL/SQL one).
If you are interested I will try to search for it.

Re: Change control for PL/SQL [message #635568 is a reply to message #635567] Wed, 01 April 2015 07:59 Go to previous messageGo to next message
John Watson
Messages: 7953
Registered: January 2010
Location: Global Village
Senior Member
Thank you for the idea - I'll look into it, it might be a solution. This is all about security, and if someone has the ability to modify code they may also have the ability to disable triggers. But in combination with audit (and 12.x Unified Audit really is, I think, impossible to hack) it might do.

A tool would be nice, though.
Re: Change control for PL/SQL [message #635569 is a reply to message #635566] Wed, 01 April 2015 08:02 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
John,

In the past I have implemented a LOGIN and an OWNER schema solution to stop unauthorised/uncontrolled changes. The schema objects are all owned by the <app_name>_OWNER schema, for example, whose account is locked and whose password is expired. The <app_name>_LOGIN schema has the required privileges and synonyms pointing at the <app_name>_OWNER schema objects. Is that something like what you have in mind?
Re: Change control for PL/SQL [message #635570 is a reply to message #635568] Wed, 01 April 2015 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 66473
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I found the link on AskTom, the trigger is very simple.

In addition, to gazzag's answer, you can create this trigger in a SYS schema (not recommended but possible, you just have to take care to drop and recreate the trigger and related objects before and after any patch).

Protection against SYS is very difficult. A possibility is Database Vault...

Re: Change control for PL/SQL [message #635571 is a reply to message #635569] Wed, 01 April 2015 08:28 Go to previous messageGo to next message
John Watson
Messages: 7953
Registered: January 2010
Location: Global Village
Senior Member
I don't think I can use that approach because this has to be retro-fitted to an existing environment. Thankyou for taking time to reply.
Re: Change control for PL/SQL [message #635574 is a reply to message #635571] Wed, 01 April 2015 09:15 Go to previous message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
I did also retro-fit it to an existing environment. The existing application schema was renamed "<app_name>_OWNER". Then, if you call the "login" schema the same name as the current application schema as well as creating appropriate synonyms, no changes to the application should be required.
Previous Topic: proxy users - DBA_AUDIT_TRAIL
Next Topic: How to stop self password rotation ?
Goto Forum:
  


Current Time: Fri Jul 19 07:09:09 CDT 2019