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

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$>
>
>
>

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