Include SELECT statement result in IF statement

Question

I am working on a simple ASP.NET Intranet with Windows Authentication. I am authenticating users with Active Directory to allow them access to the site, but would like to implement site-specific user roles to manage page & content access without disturbing or adding to the Windows User Groups already in place.

My thought on how to accomplish this was to create a UserRoles table, and place a specific RoleId in the Users table for each user. Then I would compare the Currently Logged In username with the Users table to find out which role the user is in.

Question 1:

Is this an OK way to go about this?

Question 2 (If the answer to question 1 is yes):

How can I select the correct RoleId for the current user, and use in an IF statement?

This is what I was thinking:

var db = Database.Open("Database") ;

// Find out what my Windows Username is
var currentUser = Request.LogonUserIdentity.Name.Substring(Request.LogonUserIdentity.Name.LastIndexOf(@"\") + 1);

// Find out what Role I'm in    
var getUser = "SELECT RoleId FROM Users WHERE UserID = @0";

// Get the result
var selectedUser = db.Query(getUser, currentUser);

var requiredRole = "1";

// Choose where I should go
if (SelectedRoleId != requiredRole)
{
    Response.Redirect("~/AreaRestricted");
}

Any help would be great.

Answer 1

ASP.NET includes a very adaptive RoleManager. A while back, I created a custom method like the one you are using here and then I discovered the SqlRoleProvider and it was incredible. Does everything you need it to and you can implement it with or without SqlMembershipProvider.

Add this to system.web section of your web.config:

<!-- Configure the Sql Role Provider -->
<roleManager enabled="true" defaultProvider="SqlRoleProvider">
  <providers>
    <add name="SqlRoleProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="QCmembership" applicationName="Quality_Training_2" />
  </providers>
</roleManager>

Then you can just add a new custom roles using the built-in commands, and add users to those roles in the same way. It just needs a string value of the username or some kind of identifier. Looks something like this:

Roles.AddUserToRole("anyUserName", "myCustomRole")

More information (including a list of the role provider's methods) can be found HERE.