Execute Oracle PL/SQL stored procedures in 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 wr开发者_高级运维ite 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

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.

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?