OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Wrap Triggers (Oracle 19c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Wrap Triggers [message #687721]

Tue, 16 May 2023 08:30

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

Report message to a moderator

Re: Wrap Triggers [message #687722 is a reply to message #687721]

Tue, 16 May 2023 10:11

Michel Cadot
Messages: 68500
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.

Report message to a moderator

Re: Wrap Triggers [message #687723 is a reply to message #687722]

Tue, 16 May 2023 16:51

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

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 22 22:38:24 CDT 2023