如何在ASP.NET中编程阻止Oracle写入操作（非只读用户补充方案）

问题根源 #

你的代码在SQL Server/MySQL生效但Oracle失效，核心原因是Oracle的DDL语句会强制隐式提交事务，比如CREATE/ALTER/DROP/TRUNCATE这类操作，执行时会自动提交当前事务，后续的RollbackAsync()根本回滚不了已经提交的操作。另外，Oracle默认连接的AutoCommit为true，如果事务启动不彻底，也可能导致部分操作直接提交。

解决方案 #

1. 拦截所有DDL及事务控制语句 #

必须在执行前过滤掉包含以下关键字的SQL（不区分大小写）：

• DDL类：CREATE、ALTER、DROP、TRUNCATE、RENAME、COMMENT
• 事务控制类：COMMIT、ROLLBACK、SAVEPOINT

2. 开启Oracle只读事务 #

使用SET TRANSACTION READ ONLY启动只读事务，此时任何写入/DDL操作都会直接抛出错误，从根源阻止提交，比事后回滚更可靠。

3. 显式关闭自动提交 #

在打开连接后，强制设置conn.AutoCommit = false，避免默认自动提交行为干扰。

修正后的代码 #

private async Task<List<Dictionary<string, object>>> ExecuteQueryAsync(string host, string connectionString, string query)
{
    var results = new List<Dictionary<string, object>>();
    // 定义需要拦截的危险关键字（不区分大小写）
    var forbiddenKeywords = new[] { "CREATE", "ALTER", "DROP", "TRUNCATE", "RENAME", "COMMENT", "COMMIT", "ROLLBACK", "SAVEPOINT" };
    var queryUpper = query.ToUpperInvariant();
    
    // 检查是否包含危险关键字
    if (forbiddenKeywords.Any(keyword => queryUpper.Contains(keyword)))
    {
        var errorResult = new Dictionary<string, object>
        {
            { "host", host },
            { "Error", "Query contains forbidden operations that cannot be rolled back in Oracle" }
        };
        results.Add(errorResult);
        return results;
    }

    try
    {
        await using var conn = new OracleConnection(connectionString);
        _logger.LogInformation($"[{DateTime.Now}] [Thread: {Thread.CurrentThread.ManagedThreadId}] Attempting to open connection to {host}");
        await conn.OpenAsync();
        _logger.LogInformation($"[{DateTime.Now}] [Thread: {Thread.CurrentThread.ManagedThreadId}] Successfully opened connection to {host}");

        // 显式关闭自动提交
        conn.AutoCommit = false;

        await using var tranCmd = conn.CreateCommand();
        // 启动只读事务，彻底阻止写入操作
        tranCmd.CommandText = "SET TRANSACTION READ ONLY";
        await tranCmd.ExecuteNonQueryAsync();

        try
        {
            await using var cmd = conn.CreateCommand();
            cmd.CommandText = query;

            await using var reader = await cmd.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                var row = new Dictionary<string, object>();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    var value = reader.IsDBNull(i) ? null : reader.GetValue(i);
                    row[reader.GetName(i)] = value;
                }
                results.Add(row);
            }
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"Error executing query on host {host}");
            throw;
        }
        finally
        {
            // 只读事务无需回滚，执行ROLLBACK确保连接状态干净
            await using var rollbackCmd = conn.CreateCommand();
            rollbackCmd.CommandText = "ROLLBACK";
            await rollbackCmd.ExecuteNonQueryAsync();
        }
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, $"Error executing query on host {host}");
        var errorResult = new Dictionary<string, object>
        {
            { "host", host },
            { "Error", $"General error: {ex.Message}" }
        };
        results.Add(errorResult);
    }

    return results;
}

额外说明 #

• 只读事务不仅能阻止DML写入，还会拦截所有DDL操作，一旦执行会直接抛出ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction类错误。
• 关键字过滤是第一道防线，只读事务是第二道双重保障，避免漏网的危险操作。
• 无需创建只读用户，仅通过客户端逻辑即可实现防护。

内容的提问来源于stack exchange，提问作者yochai