Menu

Nakov.com logo

Thoughts on Software Engineering

Using Oracle Membership Provider in a Transaction

ASP.NET membership providers are nice framework for managing users and roles in ASP.NET Web applications but they have a strong weakness: they do not support transactions.

Why We Need Transactions During User Registration?

We may need to use transactions for example when we want to register new user account, assign few roles to it and configure some other tables for the new user, e.g. a table keeping user’s postal address and phone number. In this case if user registration fails for some reason and we are not in a transaction, the user could be partially registered and the database could become in inconsistent state. Imagine that during the registeration of new user called “peter” his address it too long to fit in the corresponding table column. The user registration will fail but the user will be partially registered and when Peter try to register again with a shorter address his preffered username “peter” will no longer be available. This is called “partially registered user”. Obviously we need to perform the entire user registration process in a transaction to avoid partially registered users, right?

Standard ASP.NET Membership Providers Do Not Support Transactions

To avoid incorrect behaviour during the user registration process described above we need to use transactions. We have two options:

  • Traditional ADO.NET transactions.
  • Using TransactionScope based transactions.

Standard ADO.NET Transactions

Using the traditional transactions from ASP.NET is not directly possible with ASP.NET membership providers because each operation they provide is supposed to open a new (their own) database connection with separate autonomous transaction. You cannot pass an open database connection and already started transaction as parameter to Membership.CreateUser(…), at least with the latest Oracle providers (ODP.NET 11.1.7.20). The membership provider will always establish a new database transaction and will register the user in separate database session and transaction.

TransactionScope and Distributed Transactions with Oracle

Using TransactionScope could potentially solve the problem but it will cause use of a distributed transaction (one transaction for your database connection and another transaction for the connection that the membership provider internally opens). This is a little bit tricky with the latest Oracle 11g Data Providers for .NET (ODP.NET). First you need to configure the connection parameters in a specific way (see the ODP.NET documentation). Second you need to install and run a special service called OracleMTSRecoveryService (which failed to install on 2 of my 3 testing environments with freshly installed Windows). Third, the Microsoft Dictributed Transaction Coordinator (MSDTC) should be running to handle the two-phase commits. Last, but very important problem is that distributed transactions just do not work with some combinations of Oracle database, Oracle Client and ODP.NET (I don’t remember the exact versions but some combinations just fail with an internal Oracle.DataAccess unmanaged exception). After few hours of testing TransactionScope and distributed transactions with Oracle database on various environments and reading lost of negative comments in the community forums I concluded that using distributed transactions with Oracle in tricky and I prefer to avoid it.

Hacking the Oracle ASP.NET Membership Provider

Due to the problems with the distributed transactions described above I decided to extend the Oracle.Web.dll library that implements the Oracle membership provider, Role provider, SiteMap provider, etc. with functionality allowing to pass an open database connection with an associated active transaction to all major operations (e.g. CreateUser, DeleteUser, etc.). What I did was to decompile the Oracle.Web.dll with .NET Reflector and its plugin called Reflector.FileDisassembler and obtain C# source code and Visual Studio 2008 project. The provider was not obfuscated and the bugs introduced during the compilation were fixable in few minutes (decompilation always is imperfect and the code is uncompilable). Adding additional parameter to all major provider functionality could take an hour or so and you can compile the Oracle.Web.dll library for x86 or x64 platforms. Note that Oracle.Web.dll is platform independed but uses Oracle.DataAccess.dll which has unmanaged code and is platform specific (32-bit or 64-bit).

The final result was my libraries called Extended.Oracle.Web.dll and Extended.Oracle.Web-x64.dll which extend the standard Oracle Membership provider (comming with ODP.NET) with ability to provide an open database connection when using the provider functionality. Using such functionality is simple:

try
{
  // Start database transaction (I use NHibernate but you could use ADO.NET instead)
  NHibernateSessionManager.Instance.BeginTran();
  
  // Persits the address first
  Address address = new Address(...);
  AddressDAO.Save(address);
  
  // Persits the user account
  OracleMembershipProvider membershipProvider = (OracleMembershipProvider) Membership.Provider;
  OracleConnection dbConnection = (OracleConnection)NHibernateSessionManager.Instance.GetSession().Connection;
  user = membershipProvider.CreateUser(profile.Username, profile.Password, profile.Email, dbConnection);

  // Assign roles to the user
  OracleRoleProvider roleProvider = (OracleRoleProvider)Roles.Provider;
  foreach (string role in new string[]{"Login", "Insurances", "Reports"})
  {
    roleProvider.AddUsersToRoles(new string[]{user.UserName}, new string[]{role}, dbConnection);
  }
  
  // Commit the transaction (if the entire user registration is successfull)
  NHibernateSessionManager.Instance.CommitTran();
}
catch (Exception ex)
{
  // Something failed during the registration process -> abort transaction
  NHibernateSessionManager.Instance.RollbackTran();
}

Finally we have transactions with the Oracle membership provider but without distributed transactions. Happy hacking!

P.S. I will not provide the modified x86 and x64 versions of Oracle.Web.dll and Oracle.DataAccess.dll because I believe that decompiling and redistributing non-open-source software from Oracle is “a little bit illegal” and I did the above decompilations just for training purposes and experiments (until the opposite is proven).

Comments (0)

RSS feed for comments on this post. TrackBack URL

LEAVE A COMMENT