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)