在 NHibernate 中执行 Oracle PL/SQL 存储过程
首先，对不起我的英语。
我现在正在创建Oracle数据库，其中所有DML都打包到存储过程中（例如客户端不能直接执行“INSERT ONTO Articles(...”，它必须调用SP“ArticleInsert()”)。数据库也有它有自己的安全系统 - 特殊表，例如“Users”、“Privileges”、“Users_Privileges”。当我建立与数据库的连接时，我必须执行特殊的存储过程才能“登录系统”，例如“ Login(database_user, user_password)”。在这个数据库“知道”之后，我是谁，并且知道我的权限。
我计划使用 NHibernate 用 C# 编写客户端应用程序。但是这个 ORM 对于每个新对象修改都使用池中的新数据库连接那么，当我写的时候 类“Article”并抑制标准 NHib DML 行为（“INSERT/UPDATE/DELETE”将被执行适当的存储过程替换），当 NHibernate 从水池？
或者说这些任务是如何解决的？
谢谢
First of all, sorry for my english.
I am creating now Oracle database, in which all DML packed into stored procedures (for example client can't directly execute "INSERT ONTO Articles(...", it has to call SP "ArticleInsert()" instead). Database also has it's own security system - special tables, such as "Users", "Privileges", "Users_Privileges". When I establish connection to the database, I must to execute special stored procedure in order to "Log into the system", for example "Login(database_user, user_password)". After this database "knows", who am I, and knows my privileges.
I plan to write client application in C# using NHibernate. But this ORM for every new object modification uses new database connection from the pool. So, when I shall write
class "Article" and supress standard NHib DML behaviour ("INSERT/UPDATE/DELETE" will be replaced with executing of the appropriating stored procedures), how can I execute "Login(database_user, user_password)" when NHibernate invokes new db connection from the pool?
Or how are such tasks solved?
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我认为你有点重新发明轮子。 Oracle 允许与任何 RDBMS 一样细的记录访问粒度，并且超出您可能需要的粒度。为什么不利用这种权力来分配数据特权呢？
无论如何，对于 CRUD 操作，是的，您可以通过为插入、更新和删除操作指定存储过程来覆盖 NHibernate 的默认 SQL 生成。查询必须按照 NH 的条款进行；通过调用检索存储过程强制延迟加载对象的每个字段和子对象，您绕过了 NHibernate 90% 的功能。因此，您至少需要重新设计安全性，以根据需要允许经过身份验证的用户对架构/表具有 SELECT 权限。
I think you're reinventing the wheel a bit. Oracle allows as fine a granularity of record access as any RDBMS, and more than you probably need. Why aren't you using that power to lay out data privileges?
Anyway, for the CRUD operations, yes, you can override NHibernate's default SQL generation by specifying a stored procedure for the Insert, Update and Delete operations. Querying MUST be on NH's terms; you're bypassing 90% of NHibernate's power by forcing lazy-loading of every field and child of an object through calling a retrieval stored proc. So, you'll need to at least rework your security to allow SELECT permissions for authenticated users on schemas/tables as necessary.
这不是第一个轮子，由于缺乏知识，我正在重新发明它，:)。
假设我们为某个组织实施人力资源管理系统。该组织由一组部门组成，这些部门有自己的子部门等。这样，我们就有了层次结构，每个部门至少有三个属性：
- ID
- PARENT_ID（上级部门的ID）
- NAME
例如，我们在数据库中有 SP - DeptChangeName(Id, NewName)，我们希望一个数据库用户只能更改 ID = 112 的部门及其子部门的名称，而其他用户只能更改 ID = 112 的部门的名称34、还有孩子。因此，权限不仅是“执行 DeptChangeName”，而且是“执行 ID = 112 或其子级的 DeptChangeName”。
另一个例子 - 用户“Xxx”可以在 ID = 112 下创建深度不确定的新子部门。
如果这些情况可以用 Oracle 的方式解决，而不需要“手工制作的漩涡”，你能给我一个合适的 Google 链接或短语吗？
This isn't a first wheel, which I'm reinventing because of lack of knowledge, :).
Let's assume, that we implement Human Resources Management System for some organization. This organization consists of a set of departments, which has it's own subdepartments and so on. So, we has the hierarchical structure, and every department has at least three attributes:
- ID
- PARENT_ID (ID of the parent department)
- NAME
For example, we have SP in database - DeptChangeName(Id, NewName) and we want that one db user can change names only for department with ID = 112 and its children, and other user can change name only for dept with ID = 34 and it's children. So, the privilegy - not only "execute DeptChangeName", but - "Execute DeptChangeName with ID = 112 or children of it".
Another example - user "Xxx" can create new subdepartments under ID = 112 with indeterminate depth.
If these situations can be solved with Oracle means without "hand-made wheals", can you give me the an appropriate link or phrase for Google?