Re: [EXTERNAL] RE: RE: Block connection from SQL developer

In addition to not providing passwords, ensure your DB servers sit behind firewalls with appropriate access control lists.

On Fri, Mar 11, 2022 at 5:50 PM Krishnaprasad Yadav <chrishna0007_at_gmail.com> wrote:

> Hi Guru's ,
>
> Thank you all for your support and reverting back to me .
>
> Will follow suggested path .
>
> Thanks,
> Krishna
>
>
> On Fri, 11 Mar 2022 at 23:09, Jeff Smith <jeff.d.smith_at_oracle.com> wrote:
>
>> That’s easily circumvented
>>
>>
>>
>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
>> Behalf Of *Powell, Mark
>> *Sent:* Friday, March 11, 2022 12:21 PM
>> *To:* oracle-l <oracle-l_at_freelists.org>
>> *Subject:* Re: [EXTERNAL] RE: RE: Block connection from SQL developer
>>
>>
>>
>> Oracle's SQL Developer uses dbms_application_info to identify itself to
>> the instance so you could write a database logon trigger that checks for
>> and terminates session running the program.
>>
>>
>>
>> Mark Powell
>>
>> Database Administration
>>
>> (313) 592-5148
>>
>>
>>
>>
>> ------------------------------
>>
>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
>> behalf of Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org>
>> *Sent:* Friday, March 11, 2022 9:35 AM
>> *To:* chrishna0007_at_gmail.com <chrishna0007_at_gmail.com>; Jeff Smith <
>> jeff.d.smith_at_oracle.com>
>> *Cc:* dba_at_michael-brown.org <dba_at_michael-brown.org>; mwf_at_rsiz.com <
>> mwf_at_rsiz.com>; thomas.kellerer_at_mgm-tp.com <thomas.kellerer_at_mgm-tp.com>;
>> Oracle L <oracle-l_at_freelists.org>
>> *Subject:* [EXTERNAL] RE: RE: Block connection from SQL developer
>>
>>
>>
>> +1 if you do not want them to connect, do not give them an account or
>> password.
>>
>>
>>
>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
>> Behalf Of *Krishnaprasad Yadav
>> *Sent:* Friday, March 11, 2022 9:30 AM
>> *To:* Jeff Smith <jeff.d.smith_at_oracle.com>
>> *Cc:* dba_at_michael-brown.org; mwf_at_rsiz.com; thomas.kellerer_at_mgm-tp.com;
>> Oracle L <oracle-l_at_freelists.org>
>> *Subject:* Re: RE: Block connection from SQL developer
>>
>>
>>
>> Dear All,
>>
>>
>>
>> Thanks for helping me out and reverting back to me .
>>
>> i provide the situation in more details , sql developer tool is hosted in
>> application server , where these server is able to communicate with oracle
>> database servers .
>>
>> Since sql developer is used to connect to all DB(i.e Critical and
>> noncritical) , application team performs their task in non critical DB ,
>> now customer wanted to know is their any way they prevent access to some
>> critical database from sql developer just to avoid any accident/human
>> errors on those critical DB
>>
>> we recommend few things :
>>
>> 1.disable the port number of listeners from App server , but it was not
>> done as its application server it has other dependency too.
>>
>> 2.we recommended use PUP , but customer dont want to make any changes in
>> DB.
>>
>>
>>
>> Hence only option was trying to explore that was of doing something from
>> SQL Developer so access to critical systems was restricted .
>>
>>
>>
>> Hope this clears situation and need ,please do revert in case any further
>> question .
>>
>>
>>
>> Regards,
>>
>> Krishna
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> On Fri, 11 Mar 2022 at 19:41, Jeff Smith <jeff.d.smith_at_oracle.com> wrote:
>>
>> Your requirement only leads to more questions.
>>
>>
>>
>> If you don’t want someone to connect to your database, then don’t give
>> them a password for any of its accounts.
>>
>>
>>
>>
>>
>>
>>
>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
>> Behalf Of *Michael Brown
>> *Sent:* Friday, March 11, 2022 9:07 AM
>> *To:* mwf_at_rsiz.com; chrishna0007_at_gmail.com; thomas.kellerer_at_mgm-tp.com;
>> Oracle L <oracle-l_at_freelists.org>
>> *Subject:* [External] : 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
>> <https://urldefense.com/v3/__https:/clicktime.symantec.com/36GDKb3xdJaAyusHbpE2VNS7VN?u=https*3A*2F*2Fgcc02.safelinks.protection.outlook.com*2F*3Furl*3Dhttps*253A*252F*252Furldefense.com*252Fv3*252F__https*253A*252Fgo.microsoft.com*252Ffwlink*252F*253FLinkId*253D550986__*253B*21*21ACWV5N9M2RV99hQ*21aD8um5OvJ3Q8-ZZ9pyGl5E5I9hV1oFOGuhEDUSksOUVQkVSjTx-Yezjo-qHFUm3xzbM*2524*26data*3D04*257C01*257Cjbeckstrom*2540gcrta.org*257C7a1623c5e7e9471fab5008da036bbb29*257Cebe8e20736ec47f48cb8f5f757605f5d*257C1*257C0*257C637826058639462469*257CUnknown*257CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0*253D*257C3000*26sdata*3DWwfKfDFupgyjGDcQswcBMgLp0lFCh2WZsa3cjX7CLqg*253D*26reserved*3D0__;JSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJSU!!ACWV5N9M2RV99hQ!fSNsJX2yshrPh16ZKcFDxJaD2cfnMQi3mXDLKzqjBLZOPPXqm6dmTGICxE-1MvAsA9Y$>
>> for Windows
>>
>>
>>
>> *From: *Mark W. Farnham <mwf_at_rsiz.com>
>> *Sent: *Friday, March 11, 2022 8:38 AM
>> *To: *chrishna0007_at_gmail.com; thomas.kellerer_at_mgm-tp.com; Oracle L
>> <oracle-l_at_freelists.org>
>> *Subject: *RE: Block connection from SQL developer
>>
>>
>>
>> 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 <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
>>
>>
>>
>> Hi Thomas,
>>
>>
>>
>> we dont want to enforce changes from db end , no modification on DB side
>> is required, any changes or restrictions from client to restrict from sql
>> developer will be helpful .
>>
>>
>>
>> Regards,
>>
>> Krishna
>>
>>
>>
>> On Fri, 11 Mar 2022 at 18:08, Thomas Kellerer <
>> dmarc-noreply_at_freelists.org> wrote:
>>
>> 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
>> <https://urldefense.com/v3/__https:/clicktime.symantec.com/3LdN2rWJ3NRdQax9ytR2E2u7VN?u=https*3A*2F*2Fgcc02.safelinks.protection.outlook.com*2F*3Furl*3Dhttps*253A*252F*252Furldefense.com*252Fv3*252F__http*253A*252Fwww.freelists.org*252Fwebpage*252Foracle-l__*253B*21*21ACWV5N9M2RV99hQ*21aD8um5OvJ3Q8-ZZ9pyGl5E5I9hV1oFOGuhEDUSksOUVQkVSjTx-Yezjo-qHFr-kAWEk*2524*26data*3D04*257C01*257Cjbeckstrom*2540gcrta.org*257C7a1623c5e7e9471fab5008da036bbb29*257Cebe8e20736ec47f48cb8f5f757605f5d*257C1*257C0*257C637826058639462469*257CUnknown*257CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0*253D*257C3000*26sdata*3DeiCiCNgOBwRXt*252FMpJ7h*252FsiUuL*252BqrNLxyBMHNcW34bn4*253D*26reserved*3D0__;JSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUl!!ACWV5N9M2RV99hQ!fSNsJX2yshrPh16ZKcFDxJaD2cfnMQi3mXDLKzqjBLZOPPXqm6dmTGICxE-1pX9K3FQ$>
>>
>>
>>
>> -- http://www.freelists.org/webpage/oracle-l
>> <https://urldefense.com/v3/__https:/clicktime.symantec.com/3LdN2rWJ3NRdQax9ytR2E2u7VN?u=https*3A*2F*2Fgcc02.safelinks.protection.outlook.com*2F*3Furl*3Dhttps*253A*252F*252Furldefense.com*252Fv3*252F__http*253A*252Fwww.freelists.org*252Fwebpage*252Foracle-l__*253B*21*21ACWV5N9M2RV99hQ*21aD8um5OvJ3Q8-ZZ9pyGl5E5I9hV1oFOGuhEDUSksOUVQkVSjTx-Yezjo-qHFr-kAWEk*2524*26data*3D04*257C01*257Cjbeckstrom*2540gcrta.org*257C7a1623c5e7e9471fab5008da036bbb29*257Cebe8e20736ec47f48cb8f5f757605f5d*257C1*257C0*257C637826058639462469*257CUnknown*257CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0*253D*257C3000*26sdata*3DeiCiCNgOBwRXt*252FMpJ7h*252FsiUuL*252BqrNLxyBMHNcW34bn4*253D*26reserved*3D0__;JSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUl!!ACWV5N9M2RV99hQ!fSNsJX2yshrPh16ZKcFDxJaD2cfnMQi3mXDLKzqjBLZOPPXqm6dmTGICxE-1pX9K3FQ$>
>>
>>
>>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l