RE: Block connection from SQL developer

I think we also need to know how you are connecting from sql developer.  Do the people with sql developer know the host, port, service name information? 

I am not sure that there is a client side solution since restricting access is really a server side issue. 

It is a weak analogy, but I feel like you are saying here is a key which opens the house and the shed.  How do I prevent people from going into the house without doing anything to the lock on the house or the shed?

Sent from Mail for Windows

Do you control the client machines and their software? IF so, you can build a menu system with a dictionary of which client side programs can be invoked with which connection destinations and make certain they cannot directly access the client programs.

IF you don’t control the client machines and the software they can use, then you would need to deploy a custom access widget on each database server and NOT allow network access to anything else.

This is further complicated in talking about it because of container and pluggable databases as well as your usage of the word “instances.” IF you’re really just talking about certain instances of a database that has other instances that do allow access, you can play some games to keep those instances out of the rotating access list in listener and make an access widget cover routine for a separate listener for the restricted instances (if you indeed want network access of those instances at all.) You might need a secure handshake for the listener to the instances that are not allowed to have developer connections if this is a security issue as per what Thomas Kellerer mentioned. The other possibility is that you are just trying to prevent “good actors” from leaving developer sessions hanging on the 50 row default continue paging (which can in fact wreak havoc if a bullpen of developers have a desktop environment with pre-opened connections to all the Oracle databases they are allowed to work on that boots up when they log on for the day, and especially if that logon does some query to test whether each database is up and responsive.)

This sort of desk top environment conflicts with the fact that neither Oracle session connections and hung waiting to spew another set of rows queries have zero capacity and concurrency implications.

Good luck. Probably you need to tell us a little more explicitly what you mean by database instance.

mwf

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Krishnaprasad Yadav
Sent: Friday, March 11, 2022 7:48 AM
To: thomas.kellerer_at_mgm-tp.com; Oracle L
Subject: Re: Block connection from SQL developer

V$SESSION.PROGRAM is provided by the client - so you can't trust it.

I can make a Java program appear as "SQL*Plus" in V$SESSION.PROGRAM - or even "oracle.exe"


John Thomas schrieb am 11.03.2022 um 13:23:
> You could have a database logon trigger that raises an error if the user's V$SESSION.PROGRAM is SQL Developer.
>
> Depends on your requirement though. If you have privileged users with other means of access - SQL*Plus for instance - they could easily disable the trigger. 
>
>
> Regards, 
>
> John Thomas
>
>
> On Fri, 11 Mar 2022 at 12:08, Krishnaprasad Yadav <chrishna0007_at_gmail.com <mailto:chrishna0007_at_gmail.com>> wrote:
>
>     We are in a requirement   that  certain database instances should not connect from sql developer.
>     incase of 40 Database we can connect 36 by sql developer and remaining 4 database should not connect by SQL developer .
>
>     Is their any sort of Setting or any other alternative available in SQL developer . 
--
http://www.freelists.org/webpage/oracle-l