OraFAQ Forum: Security » Allow access from UI and not from back-end

Home » RDBMS Server » Security » Allow access from UI and not from back-end (Oracle 11g and 12c)

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

Allow access from UI and not from back-end [message #671305]

Tue, 21 August 2018 07:11

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.

Report message to a moderator

Re: Allow access from UI and not from back-end [message #671308 is a reply to message #671305]

Tue, 21 August 2018 07:18

Michel Cadot
Messages: 67650
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?

Report message to a moderator

Re: Allow access from UI and not from back-end [message #671311 is a reply to message #671308]

Tue, 21 August 2018 07:36

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.

Report message to a moderator

Re: Allow access from UI and not from back-end [message #671312 is a reply to message #671305]

Tue, 21 August 2018 07:40

John Watson
Messages: 8458
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.

Report message to a moderator

Re: Allow access from UI and not from back-end [message #671325 is a reply to message #671312]

Tue, 21 August 2018 08:51

Michel Cadot
Messages: 67650
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?

Report message to a moderator

Re: Allow access from UI and not from back-end [message #671327 is a reply to message #671311]

Tue, 21 August 2018 09:02

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

Report message to a moderator

Re: Allow access from UI and not from back-end [message #671328 is a reply to message #671327]

Tue, 21 August 2018 10:58

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

Report message to a moderator

Re: Allow access from UI and not from back-end [message #673061 is a reply to message #671328]

Sun, 04 November 2018 03:37

Michel Cadot
Messages: 67650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

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

Report message to a moderator

Previous Topic:	Database security issues
Next Topic:	DBA_USER privilege

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 23 14:50:35 CST 2021