On Friday, it was my turn as speaker at the Community Bootcamp 2005 aka CBC05. One of the topics at hand was "The Provider Model", with a focus on Membership providers. Those are the ones most likely being extended / written from scratch, and we did an exercise in that very area: Extend the SqlMembershipProvider to audit successful and failed logins similar to *nix. The solution I present today is a streamlined solution programmed by Alexander Schoeppl, one of the attendees.
Step 1: Create the table
CREATE TABLE [dbo].[myLoginAuditing]( [username] [varchar](255) NOT NULL, [numberofSuccessfulLogins] [int] NOT NULL, [numberofFailedLogins] [int] NOT NULL, [lastFailedLogin] [datetime] NOT NULL, [lastFailedLoginIP] [varchar](15) NOT NULL)
Step 2: Create the stored procedure
create procedure myLogUserVisit( @username as Varchar(255), @success as int, @lastfailedLoginIP as varchar(15))as IF ( EXISTS ( SELECT username FROM dbo.myLoginAuditing WHERE username = @username ) ) BEGIN if (@success = 1) Begin update myLoginAuditing set numberofSuccessfulLogins = numberofSuccessfulLogins + 1 where username = @username End else begin update myLoginAuditing set numberofFailedLogins = numberofFailedLogins + 1, lastFailedLogin = GetDate(), lastfailedLoginIP = @lastFailedLoginIP where username = @username end END ELSE BEGIN if (@success = 1) Begin insert into myLoginAuditing (username, numberofSuccessfulLogins, numberoffailedlogins, lastfailedlogin, lastfailedloginip) values (@username, 1, 0, '01.01.1900', '') End else begin insert into myLoginAuditing (username, numberofSuccessfulLogins, numberoffailedlogins, lastfailedlogin, lastfailedloginip) values (@username, 0, 1, GetDate(), @lastfailedLoginIP) end END
Alexander did a smart thing - he looked at the various aspnet* sp's.
Step 3: Write the Membership provider
The class skeleton looks like this:
public class MyMembershipProvider : SqlMembershipProvider {public override bool ValidateUser(string username, string password){}public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config){}public override MembershipUser GetUser(string username, bool userIsOnline){}}
Initialize is the easy but essential part - we need the connection string name for later:
private string connectionStringName;public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config){connectionStringName = config["connectionStringName"];base.Initialize(name, config);}
Now we can validate the user - well, the base class does that. We only do the auditing part:
public override bool ValidateUser(string username, string password){HttpContext.Current.Trace.Write("ValidateUser:entry");bool bSuccess = base.ValidateUser(username, password);string connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;SqlConnection conn = new SqlConnection(connectionString);SqlCommand cmd = new SqlCommand("exec myLogUserVisit @username, @success, @IP", conn);cmd.Parameters.AddWithValue("@username", username);if (bSuccess) cmd.Parameters.AddWithValue("@success", 1);else cmd.Parameters.AddWithValue("@success", 0);cmd.Parameters.AddWithValue("@IP", HttpContext.Current.Request.UserHostAddress);conn.Open();cmd.ExecuteNonQuery();conn.Close();HttpContext.Current.Trace.Write("ValidateUser:exit");return bSuccess;}
Step 4: Set it up - web.config
<appSettings/> <connectionStrings> <add name="MyNWind" connectionString="Data Source=cbc05vpc\cbc05;Initial Catalog=Northwind;User=sa;Password=P@ssw0rd"/> </connectionStrings> <system.web> <membership defaultProvider="SuperDuperMSProv"> <providers> <clear/> <add name="SuperDuperMSProv" connectionStringName="MyNWind" type="MyMembershipProvider"/> </providers> </membership>
Step 5: View the auditing information - default.aspx.cs
The final "UI" looks like this:
The source code is rather simple:
protected void Page_Load(object sender, EventArgs e){MyMembershipUser currentUser = (MyMembershipUser)Membership.GetUser();Label1.Text = currentUser.FullName;string lcConnection = ConfigurationManager.ConnectionStrings["MyNWind"].ConnectionString;SqlConnection conn = new SqlConnection(lcConnection);SqlCommand cmd = new SqlCommand("select * from myLoginAuditing where username=@username", conn);cmd.Parameters.AddWithValue("@Username", currentUser.UserName);conn.Open();SqlDataReader reader = cmd.ExecuteReader();GridView1.DataSource = reader;GridView1.DataBind();reader.Close();conn.Close();}
Done. By the way, did you notice something? Right! Alexander never fell into the trap of SQL Injection.
ExtendingMembershipProviderDemo.zip (5.64 KB)