单元测试需要在事务中的DDL语句 [英] Unit testing DDL statements that need to be in a transaction

问题描述

我正在开发一个使用Oracle内置身份验证机制来管理用户帐户和密码的应用程序。该应用程序还使用行级安全性。基本上，通过应用程序注册的每个用户都获得Oracle用户名和密码，而不是USERS表中的典型条目。用户还会在某些表格上收到标签。这种类型的功能要求在许多实例中组合执行DML和DDL语句，但这会产生问题，因为DDL语句执行隐式提交。如果在执行DDL语句后发生错误，则事务管理不会回滚所有内容。例如，当新用户向系统注册时，可能会发生以下情况：

I am working on an application that uses Oracle's built in authentication mechanisms to manage user accounts and passwords. The application also uses row level security. Basically every user that registers through the application gets an Oracle username and password instead of the typical entry in a "USERS" table. The users also receive labels on certain tables. This type of functionality requires that the execution of DML and DDL statements be combined in many instances, but this poses a problem because the DDL statements perform implicit commits. If an error occurs after a DDL statement has executed, the transaction management will not roll everything back. For example, when a new user registers with the system the following might take place:

1. 启动交易


2. 将人员详细信息插入表格中。 （即名字，姓氏等）-DML


3. 创建一个oracle帐户（创建用密码标识的用户testuser;）-DDL隐式提交。交易结束。


4. 新交易开始。


5. 执行更多DML规则（插入，更新等）。


6. 发生错误，事务只回滚到第4步。

1. Start transaction
2. Insert person details into a table. (i.e. first name, last name, etc.) -DML
3. Create an oracle account (create user testuser identified by password;) -DDL implicit commit. Transaction ends.
4. New transaction begins.
5. Perform more DML statments (inserts,updates,etc).
6. Error occurs, transaction only rolls back to step 4.

我知道上述逻辑按设计工作，但我'我发现很难对这类功能进行单元测试并在数据访问层进行管理。我的数据库出现故障或单元测试期间发生错误导致测试模式被应回滚的测试数据污染。当发生这种情况时，很容易擦除测试模式，但我担心生产环境中的数据库故障。我正在寻找管理它的策略。

I understand that the above logic is working as designed, but I'm finding it difficult to unit test this type of functionality and manage it in data access layer. I have had the database go down or errors occur during the unit tests that caused the test schema to be contaminated with test data that should have been rolled back. It's easy enough to wipe the test schema when this happens, but I'm worried about database failures in a production environment. I'm looking for strategies to manage this.

这是一个Java / Spring应用程序。 Spring提供事务管理。

This is a Java/Spring application. Spring is providing the transaction management.

推荐答案

您应该将Oracle代理身份验证与行级安全性结合使用。

You should use Oracle proxy authentication in combination with row level security.

阅读： http ：//www.oracle.com/technology/pub/articles/dikmans-toplink-security.html

这篇关于单元测试需要在事务中的DDL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！