如何防止自动连接到Oracle数据库？ [英] How Can I Prevent Recurring Automatic Connections to Oracle Database?

问题描述

背景

我们有一个C＃/ VB.net客户端应用程序使用连接到Oracle数据库的WCF服务。 Web服务使用.NET框架的Oracle数据提供程序连接到数据库（不要与ODP混淆）。我们的测试人员经历了偶尔的Oracle帐户锁定，这似乎发生在更改用户的Oracle密码后不久。 dba_audit_trail日志显示了在没有任何用户或客户端活动的情况下，以非常规则的间隔（即点上每两分钟）进行的自动连接尝试。许多日志（IIS，WCF跟踪，消息日志等）已经确认这些连接尝试不是由客户端应用程序直接启动的;它们必须独立于Web服务或从System.Data.OracleClient库内部。

We have a C#/VB.net client application consuming a WCF service which connects to an Oracle database. The web service connects to the database using the .NET framework's data provider for Oracle (not to be confused with ODP). Our testers have experienced sporadic Oracle account locking which seems to happen shortly after changing the user's Oracle password. The dba_audit_trail logs have revealed what seem to be automated connection attempts at very regular intervals (i.e. every two minutes on the dot) without any user or client activity. Numerous logs (IIS, WCF tracing, message logging, etc.) have confirmed these connection attempts are not initiated directly by the client application; they must be coming independently from the web service or from inside the System.Data.OracleClient library. The automatic attempts continue forever until the web service's worker process (single worker) dies from inactivity.

在某些情况下，这些自动尝试会在密码更改之前开始，并且它们会自动尝试，连接成功到数据库，但一旦密码更改，下一次尝试失败的无效的用户名/密码。三次尝试后，帐户被锁定。我们正在尝试找到这些定期连接尝试的来源。

In some instances, these automatic attempts get started before the password change, and they connect successfully to the database, but as soon as the password changes, the next attempt fails for invalid username/password. After three attempts, the account gets locked. We are trying to find the source of these periodic connection attempts.

我在Oracle论坛上发现了一个非常类似，但没有回答的问题此处。

I found a very similar, but unanswered, problem on Oracle's forum here.

当前想法

Current Thoughts

我们最近的调查使我们相信这是连接池的一个意想不到的行为。如果用户在密码更改之前连接到Web服务，将为原始连接字符串创建一个连接池。在更改密码并重新登录到Web服务后，数据提供者将根据新的连接字符串创建一个新的连接池。

Our latest investigations have led us to believe it is an unexpected behavior from connection pooling. If the user connected to the web service before the password change, a connection pool would be created for the original connection string. After changing the password and logging back into the web service, the data provider would create a new connection pool based on the new connection string.

试图保持旧的连接活着从第一个连接池？也许第一个连接池是丢弃旧的连接，并尝试用一个新的连接（用现在无效的连接字符串）补充它。什么原因可能导致？注意：我们使用默认设置的最小/最大池大小（0/100）。

Could something inside the data provider be trying to keep the old connection alive from the first connection pool? Perhaps the first connection pool is discarding the old connection and attempting to replenish it with a new one (with the now invalid connection string). What could cause this? Note: we are using the default settings for min/max pool size (0/100).

我们不相信我们的代码是直接尝试访问连接从第一个连接池。用户的会话没有上一个会话密码的任何内存，因此不会使用旧的连接字符串来引用第一个连接池。另外，我们的代码中没有什么可以解释我们看到的非常精确的连接间隔。

We do not believe our code is directly attempting to access a connection from the first connection pool. The user's session does not have any memory of the previous session's password, and therefore would not be using the old connection string to reference the first connection pool. Additionally, nothing in our code would explain the very precise connection intervals we are seeing.

推荐答案

连接。当连接被打开时，它被检出连接池。如果连接从未关闭，池认为它仍在使用。这导致池管理逻辑使用原始连接字符串来周期性地与数据库重新认证。当密码更改时，这会快速导致登录尝试失败和帐户锁定。

The underlying problem ended up being unreleased database connections. When a connection is opened, it gets checked out of the connection pool. If the connection is never closed, the pool thinks it is still being used. This causes the pool management logic to periodically re-authenticate with the database using the original connection string. When the password changes, this quickly leads to failed login attempts and account locking.

// Problem logic; connection is never closed/returned to the connection pool.
public static void ConnPoolTest1()
{
    OracleConnection conn = new OracleConnection(connectionStringWithPooling);
    conn.Open();

    //...Do some work

    // Sit on this line for 5-10 minutes and examine Oracle's dba_audit_trail.
    Console.ReadKey(); // Since connection was never released back to the connection pool, the
                       // data provider's pool management will regularly re-authenticate with DB.
                       // If user's password changes before this process dies (releasing the
                       // connection pools), you start accumulating failed password attempts.
}

此问题的正确解决方法是确保始终返回连接

// Best practice: ALWAYS CLOSE YOUR CONNECTIONS WHEN YOU ARE DONE!
public static void ConnPoolTest2()
{
    OracleConnection conn = new OracleConnection(connectionStringWithPooling);
    conn.Open();

    //...Do some work

    conn.Close();

    // Sit on this line for 5-10 minutes and examine Oracle's dba_audit_trail.
    Console.ReadKey(); // No problem here! No recurring authentication attempts because the
                       // connection has been returned to the pool.
}

注意：其他答案建议关闭池和清除旧的连接池改变。这些建议对我们来说是一个临时补丁，而我们搜索未发布的资源，他们大大帮助我们孤立的问题。

NOTE: Other answers suggested turning off pooling and clearing old connection pools when the password changed. These suggestions worked for us as a temporary patch while we searched for the unreleased resources, and they greatly helped us to isolate the problem.

这篇关于如何防止自动连接到Oracle数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！