Codemuch 01010101011101101010110001...

This tutorial demonstrates the basic setup necessary to get the ASP.NET 2.0 standard security functionality working with the Firebird.net client dlls. Specifically it concentrates on the FirebirdSql.Web.Providers functionality such as FbMembershipProvider, FbRoleProvider and FbProfileProvider. The tutorial code can be download from this link: Firebird Membership/Role/Profile Example.

The security.zip file should contain the following database files in addition to the code: ./security/Firebird:

• AspNetSecurity.sql: Firebird SQL script containing all the default scripts merged into a single database with corrections.
• AspNetSecurityTest.fdb.Backup: A transportable backup you can just restore to your server. Same as building from the script.
• AspNetSecurity.IBW: IBDataWorks schema file if you happen to own IBDataWorks.
• AspNetSecuritySchema.jpg: A picture of the schema produced from IBDataWorks in case you don't own it.

AspNetSecurity Schema

1. Build your security database

The first step is to build your security database. FirebirdClient install comes with the following 4 files: FirebirdMemberShipProvider.sql, FbSessionStateStore.sql, FbRoleProvider.sql, FbProfileProvider.sql. These scripts can be used to build your security database or databases. Unfortunately these files have bug in the sql (or at least the version I have does). I'm not sure I can remember what all the problems are since mine is already working. I think the biggest and least obvious problem was the BOOL domain doesn't have a default value and is Not Null. Another problem is with the indexes. The fields being indexed are too big so the index fails to build. I prefer having one database for MemberShip, Session, Roles and Profiles anyway so I merged the script all into one script and fixed the BOOL default value problem. I never got around to building a new index so I still get the index creation error but the database builds anyway. Obviously on a production system with lots of users you will definitely want an index.

Steps: Do 1 of the following to build your database.

• Use an admin tool (IBConsole for example) and restore the AspNetSecurityTest.fdb.Backup into your server.
• or Edit the AspNetSecurity.sql and change the path and name of the database file to whatever fits your server. At a command prompt run "isql -i AspNetSecurity.sql" without the quotes.
• or if you own IBDataWorks, load the AspNetSecurity.IBW file. Go to forward engineering. First forward engineer with only the create database option selected. Then forward engineer again with standard options and Stored Procedure and Grants selected using the connection alias you just created.

If you used the 2nd option (using the AspNetSecurity.sql) you will get the following error:


Use CONNECT or CREATE DATABASE to specify a database Statement failed, SQLCODE = -607 unsuccessful metadata update -key size too big for index SESSIONS_IDX1
   

The database should be created OK - its just missing an index on the sessions table.

2. Populate some roles and user role associations

I used the following script to load some default roles and user-role associations. The roles used in this script match my example code so if you want to run the code "as is" you need to use the same roles.
insert into Roles values('Guest','/');
insert into Roles values('User','/');
insert into Roles values('Manager','/');
insert into Roles values('Admin','/');
insert into UsersInRoles values('ATestGuest','Guest','/');
insert into UsersInRoles values('ATestUser','Guest','/');
insert into UsersInRoles values('ATestUser','User','/');
insert into UsersInRoles values('ATestManager','Guest','/');
insert into UsersInRoles values('ATestManager','User','/');
insert into UsersInRoles values('ATestManager','Manager','/');
insert into UsersInRoles values('ATestAdmin','Guest','/');
insert into UsersInRoles values('ATestAdmin','Guest','/');
insert into UsersInRoles values('ATestAdmin','User','/');
insert into UsersInRoles values('ATestAdmin','Manager','/');
insert into UsersInRoles values('ATestAdmin','Admin','/');
   

3. Setup the providers: Membership/Roles/Profiles (Web.Config)

Start by opening up the primary Web.Config file in the top level directory from the example code. If you've worked with the Web.Config before just a glance at this example should be enough for you to make a few changes and have it working on your system. I'll go through the Web.Config step by step anyway in case your not familiar with how it works.
• <connectionStrings>
<add name="AspNetSecurity"
     connectionString="Server=localhost;
                       User=SYSDBA;
                       Password=masterkey;
                       Database=C:\Databases\FireBird\Office\ASPNETSECURITYTEST.FDB"
     providerName="FirebirdSql.Data.FirebirdClient"/>
     

This section contains your connection information for the security database. You may also have other connection strings in this section for the website database etc.. In fact there is no reason you can't add the security database into you website database but I think it's easier and cleaner to keep it separate. You need to modify your Server, User, Password and Database sections to match your server and database file specifications. The name is just an alias (remember this alias though since we will need to refer to it later) and the providerName is required to identify which client dlls to use for the connection.

• <membership>
<membership defaultProvider="FireBirdMembershipProvider">
  <providers>
     <clear/>
     <add name="FireBirdMembershipProvider"
          connectionStringName="AspNetSecurity"
          applicationName="/"
          type="FirebirdSql.Web.Providers.FbMembershipProvider,
                FirebirdSql.Web.Providers,
                Version=2.0.0.0,
                Culture=neutral,
                PublicKeyToken=3d06a02581b682f8"
          passwordFormat="Clear"/>
  </providers>
</membership>
     

The membership section appears under <system.web> and contains the necessary provider information for connecting to and using the membership database. Specifically this relates to the USERS and possibly the SESSIONS tables in the database.

• defaultProvider="FireBirdMembershipProvider": this tells the application which membership provider to use. In this example it matches the name="FireBirdMembershipProvider" property for the add provider definition.
• <clear/>: This clears all default provider definitions defined in other configuraions (such as the machine.config). Since we don't want our application trying to use default SqlServer provider definitions it's a good idea to clear them out.
• <add ...>: This is the actual provider definition.
• connectionStringName="AspNetSecurity" refers back to the <connectionStrings> <add name="AspNetSecurity"...> alias. In other words it specifies which connection to use for the membership provider.
• applicationName="/" denotes the root application. In general you should use applicationName="/<myapplicationname>" where <myapplicationname>
  refers to the top level directory of your website application.
• type="...": This is the full GAC definition for the FirebirdSql.Web.Providers.dll. If you don't know how to setup your provider related to the GAC see the FirebirdClientDDEX article I wrote. Make sure you have the correct PublicKeyToken from your registry.
• passwordFormat="Clear": This just means don't use any encryption on the password. Eventually you will probably want to change this to use some form of encryption.

Other than the PublicKeyToken there is no real need to change anything in this section for my example to work on another machine.

• <roleManager>
<roleManager defaultProvider="FireBirdRoleProvider" enabled="true"><br>
  <providers>
    <clear/>
    <add name="FireBirdRoleProvider"
         connectionStringName="AspNetSecurity"
         applicationName="/"
         type="FirebirdSql.Web.Providers.FbRoleProvider,
               FirebirdSql.Web.Providers,
               Version=2.0.0.0, Culture=neutral,
               PublicKeyToken=3d06a02581b682f8"/>
  </providers>
</roleManager>
     

The roleManager section works almost exactly the same as the memberShip provider section. The main differences are the removal of the passwordFormat andtype="FirebirdSql.Web.Providers.FbRoleProvider...". Also in the <roleManager> tag you need to specify enabled="true" since it's not enabled by default.

If you decided to keep your databases split up then you would have required more than 1 connection string in the first section. Also here you need to make sure you use the connection string for your roles database if it's different (connectionStringName="AspNetSecurity").

• <profile>
<profile defaultProvider="FireBirdProfileProvider" enabled="true">
  <properties>
    <add name="Age" type="Int32" />
    <add name="Country" type="String"/>
  </properties>
  <providers>
    <clear/>
    <add name="FireBirdProfileProvider"
         connectionStringName="AspNetSecurity"
         applicationName="/"
         type="FirebirdSql.Web.Providers.FbProfileProvider,
               FirebirdSql.Web.Providers,
               Version=2.0.0.0, Culture=neutral,
               PublicKeyToken=3d06a02581b682f8"/>
   </providers>
</profile>
     

The <profile> tag and the <providers> section works exactly the same as the <roleManager> tag and sections work. What's new is this section is the <properties> subsection. This allows the application to assign new custom profile information you want to store related to each user. In this example I added an Age and Country property so that I can ask the user for their Age and Country and store it in the profile database. The profile table in the database use generic property columns to store additional information in a denormalized format. This allows for the addition of any number of extra fields for any type of information you want to store.

4. Setup your authentication configuration

Still working with the same Web.config file look for the <authentication ...> section and find the following configuration.
<system.web>
  <authentication mode="Forms">
    <forms name="MyTestSecurityCookie"
           loginUrl="~/Login.aspx"
           protection="All"
           timeout="10"
           path="/">
    </forms>
  </authentication>
   

For the purposes of this example application we are using "Forms" level authentication. This means that each form can have its own security and in this case the security level is handled through roles. The roles necessary for each form is determined from the Web.config that appears in the same directory as the form - to be discussed further below.

• name="MyTestSecurityCookie": Defines the name of the cookie used to stored the users login session information. Any name will do but if you already have a common cookie for your current application you may want to use it.
• loginUrl="~/Login.aspx": Defines the path (relative to the top level website directory) and name of the web page used for logging into your site. If a user doesn't have sufficient security rights for a given web page and isn't logged in then they are redirected to this page by default.

I'm not going to discuss in detail all the parameters for the <forms ...> tag since all this information is readily available and not specific to making the security work with Firebird.

At this point your Membership, Roles and Profile configuration should be correctly setup to work with Firebird. If you build and run the example application it should work, assuming you made the necessary changes for your environment.

A further discussion of the example code and how the Forms and directory level roles authentication works see this link: Firebird Membership/Role/Profile Example continued.