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

FWIW I’m sticking with my initial reply - don’t give passwords out to the people that shouldn’t have access.

Just to note, login triggers aren’t able to see what’s set by dbms_application_info as you need to be logged in already to execute pl/sql.

Thanks,
Andy

On Fri, 11 Mar 2022 at 17:21, Powell, Mark <mark.powell2_at_dxc.com> wrote:

> 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://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>
> 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://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>
>
>
>
> -- http://www.freelists.org/webpage/oracle-l
> <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>
>
>
>
>

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