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

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

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<mailto: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<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> On Behalf Of Michael Brown Sent: Friday, March 11, 2022 9:07 AM
To: mwf_at_rsiz.com<mailto:mwf_at_rsiz.com>; chrishna0007_at_gmail.com<mailto:chrishna0007_at_gmail.com>; thomas.kellerer_at_mgm-tp.com<mailto:thomas.kellerer_at_mgm-tp.com>; Oracle L <oracle-l_at_freelists.org<mailto: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<mailto:mwf_at_rsiz.com> Sent: Friday, March 11, 2022 8:38 AM
To: chrishna0007_at_gmail.com<mailto:chrishna0007_at_gmail.com>; thomas.kellerer_at_mgm-tp.com<mailto:thomas.kellerer_at_mgm-tp.com>; Oracle L<mailto: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> [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<mailto: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<mailto: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> <mailto: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$>


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