Home » SQL & PL/SQL » SQL & PL/SQL » Wrap Triggers (Oracle 19c)
Wrap Triggers [message #687721] Tue, 16 May 2023 08:30 Go to next message
sinpeak
Messages: 59
Registered: January 2011
Location: india
Member
Hello,

I would like to seek advice about a situation related to Oracle triggers.

Our software is used by our client but the software is managed by a third party team working for the same client.
The software has many Oracle tables in which data is inserted by software applications by reading input data from text files.
The data to be inserted has to go through lot of checks and validations implemented in the software applications.

Our client reported that the third party team is inserting data directly into tables bypassing the checks/validations. This is leading to massive inconsistencies and chaos in the system.
After deliberation we found that the third party team is allowed to do anything on Oracle tables except disabling/changing triggers on the tables.

So, we planned to move the checks/validations logic to Oracle BEFORE INSERT triggers on the tables.
BUT, this would expose our code and our management won't agree to expose the code.

Therefore, I would like to seek advice - how can we implement logic/checks/validations which will be invoked from a TRIGGER without letting anyone else see the code ?
My technical team suggested we can do some kind of "wrap" on PLSQL body but I have also learnt that a wrapped PLSQL body can be easily unwrapped.
So, I tend to ask - is there a way to wrap a PLSQL body so that no one can unwrap it ?

Any help/advice would be highly helpful.

Thanks
Re: Wrap Triggers [message #687722 is a reply to message #687721] Tue, 16 May 2023 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Therefore, I would like to seek advice - how can we implement logic/checks/validations which will be invoked from a TRIGGER without letting anyone else see the code ?

Not possible.


Quote:
My technical team suggested we can do some kind of "wrap" on PLSQL body but I have also learnt that a wrapped PLSQL body can be easily unwrapped.

Sure, see McUnwrap.


Quote:
is there a way to wrap a PLSQL body so that no one can unwrap it ?

No.
The only solution I see is to call an external procedure to make the checks but 1) it would lead to poor performances 2) it would be not reliable and 3) not so secure.

Re: Wrap Triggers [message #687723 is a reply to message #687722] Tue, 16 May 2023 16:51 Go to previous message
mbobak
Messages: 1
Registered: May 2023
Junior Member
What about constraints?  Referental constraints, check constraints, unique constraints?

That would be the way to go.  The constraints are hardcoded into the table DDL, and there is no 'code' to see.  If they had access to DBA_ views, they may be able to see the constraints, but there would be nothing they could do to change or bypass them.

Hope that helps,

-Mark
Previous Topic: How to get count of records that do not have more than 1 specific value in 1 column
Next Topic: check all the distinct value match
Goto Forum:
  


Current Time: Fri Mar 29 09:47:34 CDT 2024