{infiniteZest}
// Articles. Tutorials. Utilities.
Home  |   Search  |   Login  
Categories Skip Navigation Links
New / All
AJAX
Apple
ASP.NET
.NET
Git
Google / Android
Python / IronPython
Miscellaneous
SQL Server
Authentication vs. Authorization
Summary
This article discusses the difference between authentication and authorization. It also discusses how the data required to support each of these features is stored in the database in ASP.NET 2.0 sites.
 
Table of Contents

Authentication

Figure 1. The Membership table

Authorization

Figure 2: Database diagram for ASP.NET 2.0 membership and roles

 

Authentication

Logging a user onto a site (or an application, in general) is called authentication. For a user to get authenticated (i.e. to log on to a site), he or she usually provides a username and password. The authentication code will take these two values and see if they are valid. Two primary checks are done here – whether the username exists in the system and if it exists whether the supplied password is associated with the username.

So, the task of authentication is the process of making sure the user is who he or she says he or she is. There could be more restrictions in the authentication processes. For example, you might allow the user to log in only from a certain set of computers. In this case, you will check the IP address in addition to username and password.

Some banking sites first ask for the username and based on that they will display a preselected image (like a tree, a fruit, etc.) and ask for the password. This is a guard against phishing; the idea here is that user will know what image he or she has selected, but a phishing site with a fake login page trying to get the username and password will not know what image to display.

Typically, the username password details are stored in a database. Of course, the authentication can also be done by the operating system and web server and in that case, they will store the information.

In ASP.NET 2.0, this information is stored in a table called aspnet_Membership.

CREATE TABLE [dbo].[aspnet_Membership](
 [ApplicationId] [uniqueidentifier] NOT NULL,
 [UserId] [uniqueidentifier] NOT NULL,
 [Password] [nvarchar](128) NOT NULL,
 [PasswordFormat] [int] NOT NULL DEFAULT ((0)),
 [PasswordSalt] [nvarchar](128) NOT NULL,
 [MobilePIN] [nvarchar](16) NULL,
 [Email] [nvarchar](256) NULL,
 [LoweredEmail] [nvarchar](256) NULL,
 [PasswordQuestion] [nvarchar](256) NULL,
 [PasswordAnswer] [nvarchar](128) NULL,
 [IsApproved] [bit] NOT NULL,
 [IsLockedOut] [bit] NOT NULL,
 [CreateDate] [datetime] NOT NULL,
 [LastLoginDate] [datetime] NOT NULL,
 [LastPasswordChangedDate] [datetime] NOT NULL,
 [LastLockoutDate] [datetime] NOT NULL,
 [FailedPasswordAttemptCount] [int] NOT NULL,
 [FailedPasswordAttemptWindowStart] [datetime] NOT NULL,
 [FailedPasswordAnswerAttemptCount] [int] NOT NULL,
 [FailedPasswordAnswerAttemptWindowStart] [datetime] NOT NULL,
 [Comment] [ntext] NULL,
PRIMARY KEY NONCLUSTERED
(
 [UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

As you can see, userid and password are stored in this table. Another table called aspnet_Users also contains a small set of user related columns and has a 1:1 relationship with this membership table.

Of course, a whole lot of other information is also stored in the membership table. For example, email is stored to send the forgotten password. Password Question and answer are used to make sure the user is who he says he is and that only he has forgotten the password.

This table also contains columns that keep track of how many times a user has entered wrong password and whether the user id is currently locked out.

Figure 1. The Membership table

Figure 1. The Membership table

Authorization

From the authentication process, we know that the user is who he says he is. That doesn’t mean, this particular user has access to everything on the site. For example, an Administrator might have access to all the resources on the site; but a MarketingUser might have access to only the marketing pages on the site. MarketingManager might have access to more pages than a MarketingUser, but can access fewer pages than an administrator.

So, the authorization is which pages (or, resources) that a particular user can access. You can authorize user by user – i.e. which pages can a particular user access. However, since that could turn into a lot of work, you would typically do the authorization by roles. Meaning, you would create a role called MarketingUser and assign users (e.g. alang, kathyb, tomp, etc.) to that role. Now individual roles are either authorized or not authorized to see certain pages/resources.

You can go one more step further and put all your admin pages in the admin directory and then authorize roles against these directories. Creating roles and organizing pages by functionality greatly reduces the management/maintenance involved.

In ASP.NET 2.0, the Roles are stored in a table called aspnet_Roles.

CREATE TABLE [dbo].[aspnet_Roles](
 [ApplicationId] [uniqueidentifier] NOT NULL,
 [RoleId] [uniqueidentifier] NOT NULL DEFAULT (newid()),
 [RoleName] [nvarchar](256) NOT NULL,
 [LoweredRoleName] [nvarchar](256) NOT NULL,
 [Description] [nvarchar](256) NULL,
PRIMARY KEY NONCLUSTERED
(
 [RoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

The table that resolves the many-to-many between Users and Roles is called the aspnet_UsersInRoles. Meaning, a particular user may wear many roles and a particular role may be supported by many users. As you can see, this table holds two foreign keys UserId and RoleId into aspnet_Users and aspnet_Roles tables.

CREATE TABLE [dbo].[aspnet_UsersInRoles](
 [UserId] [uniqueidentifier] NOT NULL,
 [RoleId] [uniqueidentifier] NOT NULL,
PRIMARY KEY CLUSTERED
(
 [UserId] ASC,
 [RoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[aspnet_UsersInRoles] WITH CHECK ADD FOREIGN KEY([RoleId])
REFERENCES [dbo].[aspnet_Roles] ([RoleId])
GO
ALTER TABLE [dbo].[aspnet_UsersInRoles] WITH CHECK ADD FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])

Figure 2: Database diagram for ASP.NET 2.0 membership and roles

Figure 2: Database diagram for ASP.NET 2.0 membership and roles

As you can see in Figure 2, the two user related tables aspnet_Users and aspnet_Membership have a one-to-one relationship. So, they are basically one table (aspnet_Users) with a small set of user data and another table (aspnet_Membership) with all the user related data.

And the table aspnet_UsersInRoles resolves the M:M between aspnet_Users and aspnet_Roles with two 1:M relationships.

Bookmark and Share This

More Articles With Similar Tags
icon-CreateUserWizard-newuser.jpg
You have added the extra controls to the CreateUserWizard to collect additional information from the user at the time of new account creation. This article discusses how to save that extra information to the database.
icon-roles-dbdiag.jpg
This article looks at adding a role to the user that has been newly created via the CreateUserWizard.
icon-CreateUserWizard-extended.jpg
This article discusses the CreateUserWizard and looks at extending the default user interface provided by it.
By default SQLEXPRESS is used as the database that contains the membership information. Some thoughts in this regard.
About  Contact  Privacy Policy  Site Map