DbConnection.Open connects successfully inspite of incorrect user password

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
jagmagh
Posts: 7
Joined: Fri 23 Jun 2017 07:34

DbConnection.Open connects successfully inspite of incorrect user password

Post by jagmagh » Fri 18 May 2018 04:11

Application is using DotConnect for Oracle driver(v9.4) to connect to an Oracle 11G database from a .net core v1.1application.

Application uses Oracle Users for authentication i.e. connection strings have userid / password for each user being passed to the data layer for connection to the database.

From my application, I am using the oracle command

Code: Select all

ALTER USER xxx IDENTIFIED by yyy ....
to update the users password. Once the ALTER USER command is successful, i am unable to log in to the database from TOAD / SQL*PLUS etc using the userid + old password combination anymore as expected.

However, from within my application, i find that i am still able to log in using the userid + old password combination (in addition to being able to use the userid + new password combination)

I have verified in my code and confirmed that connection is being passed the userid + old password combination as expected but instead of an exception on Connection.Open() as I would expect to see, the dotconnect for Oracle driver succeeds in opening the connection.

Could it be something to do with connection pooling? I added a "Validate Connection=true;" parameter to my connection string but that did not seem to make any difference either.

Does anyone have any ideas or suggestions of what could be causing this kind of behaviour? and or what I might be doing incorrectly here?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: DbConnection.Open connects successfully inspite of incorrect user password

Post by Shalex » Tue 22 May 2018 13:48

Does the issue persist with "Pooling=false;"? If not, you should clear pool explicitly after changing password: https://www.devart.com/dotconnect/oracl ... lean).html.

jagmagh
Posts: 7
Joined: Fri 23 Jun 2017 07:34

Re: DbConnection.Open connects successfully inspite of incorrect user password

Post by jagmagh » Mon 28 May 2018 15:30

Thanks for the suggestion Shalex. I will try it out and post my findings.

jagmagh
Posts: 7
Joined: Fri 23 Jun 2017 07:34

Re: DbConnection.Open connects successfully inspite of incorrect user password

Post by jagmagh » Mon 28 May 2018 17:15

@Shalex - The issue does not persist if I use pooling = false in my connection string. In order to clear the connection pool, instead of using the

Code: Select all

ClearAllPools 
method as you suggested, I tried using

Code: Select all

 ClearPool (OracleConnection) 
but that did not work. I would rather not clear "All" the connection pools but only the Pool for the user whose password has changed. Any reason why ClearPool method would not work in this case?

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: DbConnection.Open connects successfully inspite of incorrect user password

Post by Pinturiccio » Wed 30 May 2018 15:47

The ClearPool and ClearAllPools(false) methods do not clear the pool immediately. The operation is started in approximately 30 seconds. For more information, please refer to
https://www.devart.com/dotconnect/oracl ... rPool.html
https://www.devart.com/dotconnect/oracl ... Pools.html

If you want to clear pool immediately, you need to use ClearAllPools method with the parameter equal to true. Please note that the ClearPool method does not have a boolean parameter and cannot clear the pool immediately.

jagmagh
Posts: 7
Joined: Fri 23 Jun 2017 07:34

Re: DbConnection.Open connects successfully inspite of incorrect user password

Post by jagmagh » Thu 31 May 2018 07:23

@Pinturiccio - Noted and thanks for the clarification.

I dont ideally want to clear "ALL" the user pools just because 1 user has changed their password.

BTW, do you know if there is a way for the connections already in the pool (having the old password) to validate their connection parameters when they are used i..e connection is opened by the application? I did come across the
Validate Connection=true;
parameter but it doesnt work for this scenario.

Can you throw some light on when Validate Connection would kick in if it is used in the connection string?

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: DbConnection.Open connects successfully inspite of incorrect user password

Post by Pinturiccio » Fri 01 Jun 2018 15:35

If a connection using pooling, only the .NET wrapper is closed when you close the connection, but the internal connection is left open. When you change the password and connect with old credentials, the old connection is actually taken from the pool. Validate Connection checks whether a connection is valid by executing a simple command against the server. Since the internal connection was open all this time, the command is executed successfully, and Validate Connection does not change anything in this case. If there were any problems with the network connection, this command would fail on the server, and the internal connection would be deleted.

jagmagh
Posts: 7
Joined: Fri 23 Jun 2017 07:34

Re: DbConnection.Open connects successfully inspite of incorrect user password

Post by jagmagh » Fri 01 Jun 2018 15:50

@Pinturiccio - thanks for the explanation. It helped me understand in what situation the validate connection would kick in. I will probably choose to leave the old connections in the pool and let them die out eventually rather than use ClearAllPools in this situation due to the negative impact of clearing the pools for all the other users connected to the application at that time. Thanks to you and @Shalex for your help on this.

Post Reply