如何将 Oracle 的行级安全性与 MyBatis 集成？
我正在从事的一个项目使用具有行级安全性的 Oracle 数据库。我需要能够调用call DBMS_APPLICATION_INFO.SET_CLIENT_INFO('userId');，然后才能执行任何其他 SQL 语句。我正在尝试找出一种在 MyBatis 中实现此功能的方法。我有一些想法，但无法实现，包括以下内容：
尝试 1
<select id="selectIds" parameterType="string" resultType="Integer">
call DBMS_APPLICATION_INFO.SET_CLIENT_INFO(#{userId});
select id from FOO
</select>
但是，您不能在单个 JDBC 调用中包含两个语句，并且 MyBatis 不支持 JDBC 批处理语句，或者至少我不支持能找到。
尝试2
<select id="selectMessageIds" parameterType="string" resultType="Integer">
<![CDATA[
declare
type ID_TYP is table of AGL_ID.ID_ID%type;
ALL_IDS ID_TYP;
begin
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(#{userId});
select ID bulk collect
into ALL_IDS
from FOO
end;
]]>
</select>
然而，这就是我所得到的，因为我了解到你不能在过程中返回数据，只能在函数中返回数据，所以没有办法返回数据。
尝试 3
我考虑过创建一个简单的 MyBatis 语句来设置客户端信息，并且需要在执行语句之前调用它。这似乎是最有希望的，但是，我们正在使用 Spring 和数据库连接池，并且我担心竞争条件。我想确保客户端信息不会溢出并影响其他语句，因为连接不会关闭，它们会被重用。
软件/框架版本信息
Oracle 10g
MyBatis 3.0.5
Spring 3.0.5
更新
忘了说我也在使用 MyBatis Spring 1.0.1
A project I am working on uses and Oracle database with row level security. I need to be able to invoke call DBMS_APPLICATION_INFO.SET_CLIENT_INFO('userId'); before I can execute any other SQL statements. I am trying to figure out a way to implement this within MyBatis. Several ideas that I had, but were unable to make work, include the following:
Attempt 1
<select id="selectIds" parameterType="string" resultType="Integer">
call DBMS_APPLICATION_INFO.SET_CLIENT_INFO(#{userId});
select id from FOO
</select>
However, you can't two statements within a single JDBC call and MyBatis doesn't have support for JDBC batch statements, or at least not that I could find.
Attempt 2
<select id="selectMessageIds" parameterType="string" resultType="Integer">
<![CDATA[
declare
type ID_TYP is table of AGL_ID.ID_ID%type;
ALL_IDS ID_TYP;
begin
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(#{userId});
select ID bulk collect
into ALL_IDS
from FOO
end;
]]>
</select>
However, that is as far I got because I learned that you can't return data in a procedure, only in a function, so there was no way to return the data.
Attempt 3
I've considered just creating a simple MyBatis statement that will set the client information and it will need to be called before executing statements. This seems the most promising, however, we are using Spring and database connection pooling and I am concerned about race conditions. I want to ensure that the client information won't bleed over and affect other statements because the connections will not get closed, they will get reused.
Software/Framework Version Information
Oracle 10g
MyBatis 3.0.5
Spring 3.0.5
Update
Forgot to mention that I am also using MyBatis Spring 1.0.1
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这听起来像是交易的完美候选者。您可以创建一个
@Transactional服务（或DAO）基类来调用DBMS_APPLICATION函数。所有其他服务类都可以扩展基础并调用必要的 SQL。在基类中，您需要确保只调用 DBMS_APPLICATION 函数一次。为此，请使用
TransactionSynchronizationManager.hasResource()和bindResource()方法将布尔值或类似标记值绑定到当前 TX。检查该值以确定是否需要调用该函数。如果函数调用仅存在于数据库中的“工作单元”，那么这应该就是您所需要的。如果该调用在连接期间存在，则基类将需要在finally 块中以某种方式进行清理。
除了基类之外，另一种可能性是使用 AOP 并在方法调用之前进行函数调用，并按照最终建议进行清理。这里的关键是确保你的拦截器在 Spring 的 TransactionInterceptor 之后被调用（即在 tx 开始之后）。
This sounds like a perfect candidate for transactions. You can create a
@Transactionalservice (or DAO) base class that makes the DBMS_APPLICATION function call. All your other service classes could extend the base and call the necessary SQL.In the base class, you want to make sure that you only call the DBMS_APPLICATION function once. To do this, use the
TransactionSynchronizationManager.hasResource()andbindResource()methods to bind a boolean or similar marker value to the current TX. Check this value to determine if you need to make the function call or not.If the function call exists only for a 'unit of work' in the DB, this should be all you need. If the call exists for the duration of the connection, the base class will need too clean up in a finally block somehow.
Rather than a base class, another possibility would be to use AOP and do the function call before method invocation and the clean up as finally advice. The key here would be to make sure that your interceptor is called after Spring's TransactionInterceptor (i.e. after the tx has started).
最安全的解决方案之一是拥有一个指定的 DatSourceUtils
1: http://static.springsource.org/spring/docs/3.0.x/javadoc-api/org/springframework/jdbc/datasource/DataSourceUtils.html 并覆盖 doGetConnection(DataSource dataSource)并在连接上设置ClientInfo
在 SqlMapClientDaoSupport 传递客户端信息。
One of the safest solution would be to have a specilaized DatSourceUtils
1: http://static.springsource.org/spring/docs/3.0.x/javadoc-api/org/springframework/jdbc/datasource/DataSourceUtils.html and override doGetConnection(DataSource dataSource) and setClientInfo on connection
Write your own abstraction over SqlMapClientDaoSupport to pass client information.