对事务中需要的 DDL 语句进行单元测试
我正在开发一个使用 Oracle 内置身份验证机制来管理用户帐户和密码的应用程序。 该应用程序还使用行级安全性。 基本上，通过应用程序注册的每个用户都会获得 Oracle 用户名和密码，而不是“USERS”表中的典型条目。 用户还会收到某些桌子上的标签。 此类功能要求在许多实例中组合执行 DML 和 DDL 语句，但这会带来问题，因为 DDL 语句执行隐式提交。 如果执行 DDL 语句后发生错误，事务管理不会回滚所有内容。 例如，当新用户在系统中注册时，可能会发生以下情况：
- 启动事务
- 将人员详细信息插入表中。 （即名字、姓氏等） -DML
- 创建oracle帐户（创建由密码标识的用户testuser；） -DDL 隐式提交。 交易结束。
- 新的交易开始。
- 执行更多 DML 语句（插入、更新等）。
- 发生错误，事务仅回滚到步骤 4。
我知道上述逻辑按设计工作，但我发现很难对此类功能进行单元测试并在数据访问层中对其进行管理。 我在单元测试期间发生了数据库故障或错误，导致测试模式被本应回滚的测试数据污染。 发生这种情况时，擦除测试模式很容易，但我担心生产环境中的数据库故障。 我正在寻找管理这个问题的策略。
这是一个 Java/Spring 应用程序。 Spring提供事务管理。
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:
- Start transaction
- Insert person details into a table. (i.e. first name, last name, etc.) -DML
- Create an oracle account (create user testuser identified by password;) -DDL implicit commit. Transaction ends.
- New transaction begins.
- Perform more DML statments (inserts,updates,etc).
- 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.
This is a Java/Spring application. Spring is providing the transaction management.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您应该将 Oracle 代理身份验证与行级安全性结合使用。
阅读此内容：http://www.oracle.com/technology /pub/articles/dikmans-toplink-security.html
You should use Oracle proxy authentication in combination with row level security.
Read this: http://www.oracle.com/technology/pub/articles/dikmans-toplink-security.html
首先我必须说：这样做是个坏主意。 有两个原因：
不确定您为什么选择这样做，但我强烈建议您在应用程序而不是数据库层实现用户。
至于如何解决你的问题，基本上是无法解决的。 与在序列中间创建表或索引相同。
First off I have to say: bad idea doing it this way. For two reasons:
Not sure why you've chosen to do it this but I would strongly recommend you implement users at the application and not the database layer.
As for how to solve your problem, basically you can't. Same as if you were creating a table or an index in the middle of your sequence.
我不同意之前的一些评论，并指出使用内置 Oracle 帐户安全性有很多优点。 如果您必须使用某种带有附加信息的用户影子表来扩充它，那么如何将 Oracle 帐户创建包装在声明为 PRAGMA AUTONOMOUS_TRANSACTION 的单独包中，并向执行插入操作的包返回成功/失败状态影子表？ 我相信这会将 Oracle 帐户创建与交易隔离开来。
I'll disagree with some of the previous comments and say that there are a lot of advantages to using the built-in Oracle account security. If you have to augment this with some sort of shadow table of users with additional information, how about wrapping the Oracle account creation in a separate package that is declared PRAGMA AUTONOMOUS_TRANSACTION and returns a sucess/failure status to the package that is doing the insert into the shadow table? I believe this would isolate the Oracle account creation from the transaction.