Home » RDBMS Server » Security » Allow access from UI and not from back-end (Oracle 11g and 12c)
Allow access from UI and not from back-end [message #671305] Tue, 21 August 2018 07:11 Go to next message
sinpeak
Messages: 55
Registered: January 2011
Location: india
Member
Hello,

I chose the "Security" forum for this topic.
Forgive me if the topic is not relevant to Security and please guide me to the right forum.

Our product has Windows based GUI that connects to our Oracle database and invokes PLSQL procedures to perform certain business functions on the entity data.
So for e.g.
Function-A requires invocation of 3 PLSQL procedures in order.

So when the user clicks the Function-A button from the Windows GUI the GUI code will internally invoke the 3 PLSQL procedures in order.

Our client has a requirement where they want the user to perform the function from the GUI but do not want the same user to execute the related 3 PLSQL procedures by directly logging into the database ( from TOAD, PLSQL developer etc. ).

I cannot think of a way to do this in Oracle.
We could do this by adding an app/source input parameter and logic to the PLSQL procedure but I dont think this would be full-proof.
If anyone has any suggestions/approach to implement such a requirement, then please share.

Thanks.

Re: Allow access from UI and not from back-end [message #671308 is a reply to message #671305] Tue, 21 August 2018 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It depends of the client (the GUI), does it use an OCI or a thin client? What is the API it uses to access the database?

Re: Allow access from UI and not from back-end [message #671311 is a reply to message #671308] Tue, 21 August 2018 07:36 Go to previous messageGo to next message
sinpeak
Messages: 55
Registered: January 2011
Location: india
Member
Hi,
Thanks for your response.

We use OCI.

We use a wrapper of OCI dll is used to connect to the database.

In this situation, is there anything that can be done to implement the requirement mentioned in the original post message ?

Thanks.
Re: Allow access from UI and not from back-end [message #671312 is a reply to message #671305] Tue, 21 August 2018 07:40 Go to previous messageGo to next message
John Watson
Messages: 8075
Registered: January 2010
Location: Global Village
Senior Member
On your release 12 databases, you can use the ACCESSIBLE BY clause to white list your three procedures so that they can be invoked only by your function, no matter what client is used. I don't know of any way to do it in release 11.
Re: Allow access from UI and not from back-end [message #671325 is a reply to message #671312] Tue, 21 August 2018 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Isn't the ACCESSIBLE BY clause just refers other PL/SQL procedural objects not to application?

Re: Allow access from UI and not from back-end [message #671327 is a reply to message #671311] Tue, 21 August 2018 09:02 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The simplest way is to set client_info of the session to a secret passphrase (encrypted) that is checked inside the procedure.

Re: Allow access from UI and not from back-end [message #671328 is a reply to message #671327] Tue, 21 August 2018 10:58 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can also grant the EXECUTE privilege on the procedure to ONLY a secure application role which can be set only by the GUI and no other application.

Re: Allow access from UI and not from back-end [message #673061 is a reply to message #671328] Sun, 04 November 2018 03:37 Go to previous message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So how did you solve your issue it in the end?

Previous Topic: Database security issues
Next Topic: DBA_USER privilege
Goto Forum:
  


Current Time: Sun Dec 08 17:44:09 CST 2019