7 Replies Latest reply on Oct 8, 2010 6:52 PM by gdarling - oracle

    Change user password using "ALTER USER..." command

    541346
      Hello Forum Members,

      I am experiencing very interesting problem in my ASP.NET application when I am changing the user password in ORACLE database using "ALTER USER..." command from inside of my code.
      Example:
      The user "TestUserName" has the current password, let's say "User1".
      After I change the password to "User2", I can login into my application with the OLD and with the NEW passwords by some reason?
      If I check the password for the current "TestUserName" in the database, it has the newest value ("User2"). From the SQLPlus I can login into the database only with the newest password. But from my application I can still login using both passwords? If I close the application, close the browser and start the application again, I still can login using two passwords. But, if I restart the PC, the old password disappeared. What is going on?

      Any suggestions?

      Thanks in advance,
      -Dmitriy
        • 1. Re: Change user password using "ALTER USER..." command
          gdarling - oracle
          Hi,

          I assume you're using connection pooling? (It's on by default). Changing a user's password doesnt kill previously established connections, so pooled connections already created would continue to work, until it came time for new connections to be added to the pool which would then go by the new password.
          I'm not sure why restarting the application wouldnt clear out the pool, but I'd bet if you checked v$session you'd still see the connection there.

          Cheers,
          Greg
          • 2. Re: Change user password using "ALTER USER..." command
            541346
            Hi Greg,

            This is the Sub code I am using to change the password:

            Private Sub cmdChangePassword_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdChangePassword.Click
            Dim sCommandText As String
            Dim da As OracleDataAdapter
            Dim ds As DataSet
            Dim par As OleDbParameter
            Dim cmdBuilder As OracleCommandBuilder
            Dim iPos As Integer
            Dim sTmp As String
            Dim lTemp As Long
            Dim dr As DataRow
            Dim oTmp As Object
            Dim bRecExist As Boolean

            Dim sUserId As String
            Dim sPassword As String
            Dim cmd As OracleCommand

            Dim objTransaction As OracleTransaction = Nothing

            Try
            'Create the connection to the datasource:
            wsDataBasesService = New DataBasesService
            If wsDataBasesService.MERCURY_OpenConnection(Session("UserSignonID"), Session("UserPW")) = 1 Then

            'Begin Transaction here:
            objTransaction = CType(wsDataBasesService.GetMERCURYConnection(), OracleConnection).BeginTransaction()

            sUserId = Trim(txtUserID.Text)
            sPassword = Trim(txtPasswordAgain.Text)

            sCommandText = "ALTER USER " & sUserId & " IDENTIFIED BY " & sPassword & ""
            cmd = New OracleCommand("", CType(wsDataBasesService.GetMERCURYConnection(), OracleConnection))
            cmd.CommandType = CommandType.Text
            cmd.CommandText = sCommandText
            cmd.ExecuteNonQuery()
            ''cmd.Transaction = objTransaction
            cmd.Dispose()
            cmd = Nothing

            'Commit transaction and dispose:
            objTransaction.Commit()
            objTransaction.Dispose()
            objTransaction = Nothing

            wsDataBasesService.MERCURY_CloseConnection()
            wsDataBasesService.Dispose()
            wsDataBasesService = Nothing

            'Response.Redirect(Session("BackOnCancel"), False)
            'Exit Sub

            'TEST!!!
            Session.Abandon()
            Response.Redirect("LoginForm.aspx", False)
            Exit Sub
            Else
            'ERROR opening the connection to the DataSource
            Session("Message") = "ERROR on creating connection to the DataSource inside the cmdSave_Click() function). Contact customer support,please. "
            Response.Redirect("ErrorForm.aspx", False)
            Exit Sub
            End If
            Catch ex As Exception 'System.IndexOutOfRangeException
            'do clean up...
            cmd.Dispose()
            cmd = Nothing
            If Not wsDataBasesService Is Nothing Then
            If Not wsDataBasesService.GetMERCURYConnection() Is Nothing Then
            If wsDataBasesService.MERCURY_ConnectionState() = 1 Then
            'Rollback transaction and rollback:
            objTransaction.Rollback()
            objTransaction.Dispose()
            objTransaction = Nothing

            wsDataBasesService.MERCURY_CloseConnection()
            wsDataBasesService.Dispose()
            wsDataBasesService = Nothing
            End If
            End If
            End If
            If ex.GetType.Name = "ThreadAbortException" Then
            Exit Sub
            End If

            'ERROR : There is no row at position 0.(no row to delete)
            Session("Message") = "ERROR inside the cmdSave_Click() function). " + ex.Message + " You’ve attempted to delete a non-existing record. "
            Response.Redirect("ErrorForm.aspx")
            Exit Sub
            End Try
            Response.Redirect(Session("BackOnCancel"), False)
            Exit Sub
            End Sub


            The "wsDataBasesService" is the class I've created to do all database manipulations. This class works OK in my application. As you can see I am closing the connection and abandoning the session explicitly. But the problem still exists?
            Are you suggesting to check v$session? Should I check it from inside SQLPlus? How do I have to login into SQLPlus? As system administrator user or as a regular user?


            -Dmitriy
            • 3. Re: Change user password using "ALTER USER..." command
              gdarling - oracle
              Hi,
              You need to be a dba user to query v$session.
              If you have pooling turned on (the default), closing and disposing a connection doesnt actually "close it". It places the open connection back in the pool.
              If you turn off connection pooling, the closing and disposing the connection will actually close the connection. You probably dont want to do that though, pooling is a good thing.
              You may want to look into using OracleConnection::ClearPool after changing the password. Perhaps the following example will help.

              Cheers,
              Greg


              CODE
              ========
              using System;
              using Oracle.DataAccess.Client;
              using Oracle.DataAccess.Types;

              class Program
              {
              static void Main(string[] args)
              {
              string constr = "user id=bob1;password=bob1;data source=orcl";
              OracleConnection con = new OracleConnection();
              con.ConnectionString = constr;
              con.Open();
              OracleCommand cmd = new OracleCommand("alter user bob1 identified by newpass", con);
              cmd.ExecuteNonQuery();
              con.Dispose();

              OracleConnection con2 = new OracleConnection();
              con2.ConnectionString = constr;
              con2.Open();
              Console.WriteLine("still connected, with old password in constr");
              con2.Dispose();

              // clear out the pool
              OracleConnection.ClearPool(con2);

              OracleConnection con3 = new OracleConnection();
              con3.ConnectionString = constr;
              try
              {
              con3.Open();
              Console.WriteLine("still connected, with old password in constr");
              }
              catch (Exception ex)
              {
              Console.WriteLine(ex.Message);
              }
              finally
              {
              con3.Dispose();
              }
              }
              }




              OUTPUT
              ===========
              still connected, with old password in constr
              ORA-1017: invalid username/password; logon denied
              • 4. Re: Change user password using "ALTER USER..." command
                541346
                Hi Greg,

                Thank you for the response, but you probably using ODP.NET version 10.2 and ORACLE version 10. I am currently using ODP.NET (Oracle.DataAccess.dll) version 9.2.0.700 and ORACLE 9i. I do not have such method available as OracleConnection.ClearPool() for this version. Is there any other way to clear the pool in version I am currently using?

                Thanks in advance,
                Dmitriy
                • 5. Re: Change user password using "ALTER USER..." command
                  541346
                  Hi Greg,

                  Thanks very much. I've downloaded the newest version of the Oracle10g Release 2 ODAC and Oracle Developer Tools for Visual Studio .NET for Framework 1.x from the site:
                  http://www.oracle.com/technology/software/tech/dotnet/utilsoft.html
                  After I've replaced the old reference of the Oracle.DataAccess.dll to newest one and added the line of code you've advised, the application start doing exactly I wanted it to do.


                  CODE:
                  ====================================
                  Public Function MERCURY_CloseConnection() As Integer
                  Try
                  If Not currentMERCURYConnection Is Nothing Then
                  If currentMERCURYConnection.State = ConnectionState.Open Then
                  currentMERCURYConnection.Close()
                  currentMERCURYConnection.Dispose()
                  'clear out the pool:
                  Oracle.DataAccess.Client.OracleConnection.ClearPool(currentMERCURYConnection)
                  currentMERCURYConnection = Nothing
                  Exit Try
                  Else
                  currentMERCURYConnection.Dispose()
                  'clear out the pool:
                  Oracle.DataAccess.Client.OracleConnection.ClearPool(currentMERCURYConnection)
                  currentMERCURYConnection = Nothing
                  Exit Try
                  End If
                  Else
                  Return 0
                  End If
                  Catch e As OleDb.OleDbException
                  Return 0
                  End Try
                  Return 1
                  End Function


                  Thanks again,
                  -Dmitriy
                  • 6. Re: Change user password using "ALTER USER..." command
                    anca
                    Hi Greg,

                    I hope you're still around... 4 years later.
                    Can you confirm that the method OpenWithNewPassword also clears the pool for the old connection?
                    This has to be a 'fix' which occured between 10.1 and 11.2. I had a ClearPool after changing the password and this seems to lock the Oracle Account now!?

                    Thanks,
                    Anca
                    • 7. Re: Change user password using "ALTER USER..." command
                      gdarling - oracle
                      Hi Anca,

                      I don't have the source, but just did a quick test and it doenst seem to clear the pool for me, I still show 6 connections after OpenWithNewPassword. (I'm testing 11.2.0.1.0 for the record)

                      Greg
                      //grant connect, resource to u1 identified by p1;
                      
                      using System;
                      using System.Data;
                      using Oracle.DataAccess.Client;
                      
                      class Program
                      {
                          static void Main(string[] args)
                          {
                              string constr = "user id=u1;password=p1;data source=orcl";
                              OracleConnection con = new OracleConnection(constr);
                              con.Open();
                              OracleConnection con2 = new OracleConnection(constr);
                              con2.Open();
                              con2.Dispose();
                              con.Dispose();
                              Console.WriteLine("check v$session, there should be 6 connections from U1..");
                              Console.WriteLine("now,   alter user u1 password expire  , then hit enter");
                              Console.ReadKey();
                              OracleConnection con3 = new OracleConnection(constr);
                              con3.OpenWithNewPassword("p2");
                              Console.WriteLine("check v$session again");
                              Console.ReadKey();
                          }
                      }