role based authorization with window authentication in asp.net

Question

I have to create a role based application in which i have three roles admin,manager and user. In my application i have three different folder in which i have to check those role. What i want is when i enter credential in my login page first it will check whether user is authorized or not and if not then redirect to the error page. If authorized then check its roles and give access to the specified folder.

this is my login page code:

protected void btnSubmit_Click(object sender, EventArgs e)
{
    String queryread = @"Select * from Login where UserName = '" + tbUserName.Text.ToLower() + "' and Password='"+ tbPassword.Text.ToLower() +"'";
    SqlConnection con = new SqlConnection();
    SqlDataReader read;
    con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["BartConnectionString"].ConnectionString;
    SqlCommand readdata = new SqlCommand(queryread, con);
    try
    {
        con.Open();
        read = readdata.ExecuteReader();
        Boolean flag = false;
        while (read.Read())
        {
            String UserName = read["UserName"].ToString().ToLower();
            String password = read["Password"].ToString();
            if (tbUserName.Text.ToLower() == UserName)
            {
                if (tbPassword.Text.Trim() == password)
                {
                        Session["UserID"] = UserName.ToString();                            
                        flag = true;   
                }
            }
        }
        if (flag)
        {
            Response.Redirect("~/Supervisor/Form.aspx", false);
        }
        else
        {
            Response.Redirect("~/Error.aspx",false);
        }
    }
    catch (Exception ex2)
        {
            Response.Write("Error");
        }
    finally
        {
        if (con.State == System.Data.ConnectionState.Open)
            con.Close();
        }
    }

in this i check the authorization for the user now i also want to check which role this user have what changes i have to make in this code to check roles also

In my sql i have table login which have

UserID
UserName
Password
RoleID

what changes i have to make in my web.config file. what change i have to make in my login page to check role. I guess my question is clear to you guys.

Thanks in advance

There are so many problems with this code I don't know where to start. I'd sort this out before you move on to checking roles to be honest. Do you REALLY want to be building SqlConnections in page code-behind ... the answer is definitely no BTW, you also need to read up on Sql Injection attacks in the first instance. — Jammer
N-Tier architecture, Repository Pattern, EntityFramework, SQL Role Provider. The list is endless. If I was reviewing this code in my team it would be completely rejected as not fit for purpose. Have you done much development before? — Jammer

Jammer Jammer · Accepted Answer · 2013-05-23T08:59:45

As I said in the comment above I wouldn't accept this code into a project I was managing and you should be very careful about how you implement user authentication in an application. I would start by having a good read of this document on MSDN:

Security Basics and ASP.NET Support

Overview of Forms Authentication

Forms Authentication Configuration and Advanced Topics

Once you have that in place you can have a look at Roles in this article:

Authenticating Users with Forms Authentication

There is a lot to take in here but reading through these will give you a good base to start from.

Even though you want to use Windows Authentication I would still read the above linked articles as they will give you a lot of useful information about how authentication work in general. To use Windows Authentication with ASP.NET read this:

Windows Authentication in ASP.NET

role based authorization with window authentication in asp.net

1 Answers