.NET Forum Control with SQLite Database and
Custom Identity Authentication
By Peter A. Bromberg, Ph.D.

Peter Bromberg

"Associate yourself with men of good quality if you esteem your own reputation;
for 'tis better to be alone than in bad company."
--George Washington

Recently the primary developers of the ADO.NET provider for the SQLite database engine released build 20 on SourceForge.net and stated that it had passed all the tests for the Compact Framework. Well Done! Unfortunately, the one thing they didn't do, at least not with this release, was provide built SQLite C - DLLs for the CF X86 processor so that people could try them out on their Emulators and devices running X86 (e.g., most Pocket PCs and SmartPhones).



Not to be daunted, I downloaded eMbedded Visual C++, installed Service Pack 3, and built myself an X86 compatible version from the C sources. I'm happy to report that it works very nicely on the Compact Framework -- weighing in at just 197K for the SQLite engine, and another 60K for the SQLite ADO.NET provider, this opens up a whole new world for developers interested in targeting the Compact Framework with an open-source, cross-platform database engine that is not only robust and well-tested, but also very fast.

Combined with the use of a WebService on the server, it becomes remarkably easy to build a synchronization mechanism so that work that one has done offline on the device can be sent up via the webservice as a "GetChanges" version of a DataSet, and hooked up at the WebService end to a DataAdapter and used to perform the updates, deletes and inserts all in one WebService call. The resultant DataSet can then be brought back as the return payload from the WebService call, and then used to merge or otherwise synchronize the SQLite database on the device. Sweet!

You may say, "Well, doesn't SQL Server CE do all this?" Yes, of course it does. However, it requires some significant installation and configuration on the server and on the client device, and client licensing fees must be paid. I'm not against that at all, however, I am sure that there are many deployment scenarios where it is neither necessary nor particularly desirable to get this sophisticated.

But I digress, because I'll be presenting my Compact Framework reference app for SQLite at the Orlando .NET User Group on October 21, 2004. If you are intereted, you can download the Powerpoint for the presentation here. This article is really about another use for SQLite - "self - contained, zero-install" .NET components which come with their "own database". An example in point is the NET Ar Forum Control - a very nice, open - source, self contained .NET Server Control that can be used to create a cool little discussion forum, say at the bottom of a page (you can see I like it, because there is a live one at the bottom of this and most other articles I've written recently). The control was originally written to use OleDb, and I spent several hours converting it to use the ADO.NET SqlClient class for our eggheadcafe.com use. Well, why not give it a go for SQLite too, I thought?

It turned out to be a good exercise, because in the process I added a few things. First, the original author didn't have much exception - handling code in his classes. And, as we all know, when you have an unhandled exception that keeps getting repeated when you are dealing with database connections and commands, things can go south pretty fast. So the first thing I did is wire it up extensively with reasonably good exception blocks that were sure to dispose and close my connections, commands and DataAdapters in the "finally" block.

The second thing I thought of was authentication and roles. The control already has a UserInfo class but it isn't really designed to handle roles and similar kinds of things that you might want for an Administrator login. For example, you might only want to enable the EDIT and DELETE buttons for administrators only, and likewise, an Administrator might have a special link visible that can take him to pages that allow him to add a new user, edit a users properties, or even delete a user. I've provided all this, and I did it with a modified version of Forms Authentication that uses the same User table in my new SQLite database that was originally provided by the author, but as an MSAccess database.

Finally, I added a Custom IPrincipal derived class that enables more sophisticated user info properties and methods, including several "IsInRole" type methods. This is stored in Cache upon authentication so that subsequest logins do not require any database activity, and is also set up to pass through the HttpContext from page to page, riding on top of the Forms Authentication Ticket cookie. The result is a very efficient authentication and authorization mechanism, built around the control.

I've seen several examples lately of what I consider poor programming tactics where developers kind of "create their own" version of role-based site authorization either because they don't fully understand how to use the built-in .NET IIdentity and IPrincipal derived classes or the HttpContext.Current.User property, or else they simply weren't even aware that they were there for our use. So first, let's take a look at an easy way to extend the IPrincipal object.

Probably the cleanest way to implement a custom Identity and authentication scheme is with an HttpModule, which will kick in automatically on every request. However, this is not always necessary. If authenticated IPrincipal instances that have been extended to provide role information can be cached (and they do not have to be "cached" in Session, as we will soon see), then we can achieve the same economies of "not having to hit the database on every request" (or even on every login) as with an HttpModule.

The first step is to set up the basic Forms auth directives in the web.config:

<authentication mode="Forms" >
<forms loginUrl="Logon.aspx" name="AuthCookie" timeout="60" path="/">
</forms>
</authentication>

<authorization>
<deny users="?" />
<allow users="*" />
</authorization>

Next, we add the following code to Global.asax, in the AuthenticateRequest handler:

protected void Application_AuthenticateRequest(Object sender, EventArgs e)
  {
   string cookieName = FormsAuthentication.FormsCookieName;
   HttpCookie authCookie = Context.Request.Cookies[cookieName];
   if(null == authCookie)
   {
    return;
   } 
           // set up regular Forms Auth ticket with encrypted cookie
   FormsAuthenticationTicket authTicket = null;
   try
   {
    authTicket = FormsAuthentication.Decrypt(authCookie.Value);
   }
   catch(Exception ex)
   {
    // Could Log details here
    return;
   }

   if (null == authTicket)
   {
    // Cookie didn't decrypt.
    return; 
   } 
              // get our roles from the ticket
   string[] roles = authTicket.UserData.Split('|');  
   FormsIdentity id = new FormsIdentity( authTicket ); 
   // Custom Principal will flow throughout the request.
   CustomPrincipal principal = new CustomPrincipal(id, roles);
     Context.User = principal;
  }

And finally, of course we provide our CustomPrincipal class, derived from IPrincipal:

using System;
using System.Security.Principal;
namespace TestApp
{
 public class CustomPrincipal : IPrincipal
 {
  private IIdentity _identity;
  private string [] _roles;

  public CustomPrincipal(IIdentity identity, string [] roles)
  {
   _identity = identity;
   _roles = new string[roles.Length];
   roles.CopyTo(_roles, 0);
   Array.Sort(_roles);
  }

  public bool IsInRole(string role)
  {
   return Array.BinarySearch( _roles, role ) >= 0 ? true : false;
  }
  public IIdentity Identity
  {
   get
   {
    return _identity;
   }
  }

  public bool IsInAllRoles( params string [] roles )
  {
   foreach (string searchrole in roles )
   {
    if (Array.BinarySearch(_roles, searchrole) < 0 )
     return false;
   }
   return true;
  }

  public bool IsInAnyRoles( params string [] roles )
  {
   foreach (string searchrole in roles )
   {
    if (Array.BinarySearch(_roles, searchrole ) > 0 )
     return true;
   }
   return false;
  }
 }
}


One can easily see that in the constructor above, we can pass in our FormsIdentity and the string array of roles for the user, and now we have a handy way of getting at this information which we can attach to and pass along right with the HttpContext. Now, lets switch over to the required Logon.aspx page to see how this all comes together:

private string[] GetRoles(string userName)
{
NetAr.Components.UserInfo u=(NetAr.Components.UserInfo)Cache[userName];
 return u.Roles ;
}

private void Button1_Click(object sender, System.EventArgs e)
{
 string userName=this.txtUserName.Text;
 string password =this.txtPassword.Text;
 bool isAuthenticated = IsAuthenticated( txtUserName.Text, 
  txtPassword.Text );
 if (isAuthenticated == true )
 {
  string[] roles = GetRoles( txtUserName.Text );      
  FormsAuthenticationTicket authTicket = new 
   FormsAuthenticationTicket(1, // version
   txtUserName.Text,           // user name
   DateTime.Now,               // creation
   DateTime.Now.AddMinutes(60),// Expiration
   false,                      // Persistent
   String.Join("|",roles) );   // User data
 string encryptedTicket = FormsAuthentication.Encrypt(authTicket);    HttpCookie authCookie = 
  new HttpCookie(FormsAuthentication.FormsCookieName,
   encryptedTicket);
  Response.Cookies.Add(authCookie); 
  Response.Redirect( FormsAuthentication.GetRedirectUrl(
   txtUserName.Text, 
   false ));
 }
}

private bool IsAuthenticated( string userName, string password )
{
 bool IsAuthenticated=false;
 if(Cache["userName"]!=null)
 {
  NetAr.Components.UserInfo u=(NetAr.Components.UserInfo)Cache["userName"];
 }
 else
 {
  SQLiteConnection cn = new SQLiteConnection(Application["connString"].ToString());
  SQLiteCommand cmd = new SQLiteCommand("Select * from User where userName='"
+userName+ "' and password='"+password+"'"); cmd.Connection =cn; cn.Open(); IDataReader rdr= cmd.ExecuteReader(CommandBehavior.CloseConnection); // populate user object rdr.Read(); string firstName=Convert.ToString(rdr.GetValue(9)); if( firstName!=String.Empty ) { IsAuthenticated= true; NetAr.Components.UserInfo u= new NetAr.Components.UserInfo(rdr.GetInt32(0)); u.FirstName=Convert.ToString(rdr.GetValue(rdr.GetOrdinal("FirstName"))); u.LastName=Convert.ToString(rdr.GetValue(rdr.GetOrdinal("LastName"))); string strRoles=Convert.ToString(rdr.GetValue(rdr.GetOrdinal("roles"))); string[] roles=strRoles.Split(Convert.ToChar("|")); u.Roles=roles; Cache[userName]=u; } rdr.Close(); cmd.Dispose(); cn.Close(); } return IsAuthenticated; }


The "Button1_Click handler of course is where we go when we hit the login button after supplying our username and password. This calls our IsAuthenticated method which first checks if we have cached the user's credentials so as to avoid an unncessary database call. The rest of this method should be self-explanatory. If it returns true, we attach the user's roles and a fresh AuthTicket, add the auth Cookie, and do the normal RedirectFromLoginUrl. Simple, easy, and reliable, Custom Forms Authentication with a cached custom IPrincipal object, against a SQLite database!

To check role membership on a particular page for authorization of a logged-in user, we can use code similar to the following:

protected CustomPrincipal cp=null;
private void Page_Load(object sender, System.EventArgs e)
{
cp = HttpContext.Current.User as CustomPrincipal;
if(!cp.IsInRole("Administrator"))
{
Response.Write("Unauthorized Access!");
Response.End();
}
. . .

You can download the complete solution, which includes a sample database, the Login, default (forums) page with Admin links illustrating sample usage of the revised Net Ar Forums Control, and Admin pages to display users in a datagrid, edit, add and delete users and set their roles. Simply unzip the files into the folder of your choice, and be sure to mark the "TestApp" subfolder as an IIS Application in IIS Manager before loading the solution.

IMPORTANT NOTE: There is already a user with username="test" and password="test" that is in an Administrator role with ID=49 that you can use to log in as an admin and get to "see" everything. I've left this in as ID 49 in the User table because that is the ID that the Net Ar Forum control decrypts to with its "secret code" decryption key for an Administrator. This, of course, can be changed. I have also provided a modified build of SQLiteQA, a .NET "Query Analyzer" clone written by one of the principal SQLite.NET developers, which I have modified to work with SQLite 3.0. You can download this separately here.

Download the Visual Studio.NET 2003 solution accompanying this article


Peter Bromberg is a C# MVP, MCP, and .NET consultant who has worked in the banking and financial industry for 20 years. He has architected and developed web - based corporate distributed application solutions since 1995, and focuses exclusively on the .NET Platform.