Assigning Roles to Users

Question

I'm having trouble figuring out how to assign roles to users. I've got the log in mechanism working, but need to figure out how to use roles defined to give users certain access.

I have a database with these tables:

                     Tables
                     ------
   UserTbl          RolesTbl           UserInRoleTbl
   -------         ----------          -------------
 UserID  (PK)      RoleId  (PK)        ID       (PK)
 Name              RoleName            UserId
 UserName          Description         RoleId
 Password
 Email

With this small database I'm testing the ability to assign roles to the user.

I'm using LINQtoSQL to act as the Data Access Layer. I've created the login mechanism to login to the software, and then I get stuck on what to do next.

For Example:

username = admin,password= admin, RoleId = 1 ;Rolename =Administrator;

And then I use the following code after the login to get the role:n

public partial class Window3 : Window
{  

    public Window3()
    {
        InitializeComponent();

        GenericIdentity My2 = new GenericIdentity("admin");

        string[] roles1 = { "Administrator" };

        GenericPrincipal principal1 = new GenericPrincipal(My2, roles1);
        Thread.CurrentPrincipal = principal1;
     }

private void LoadWindow(object sender, RoutedEventArgs e)
    {   if (Thread.CurrentPrincipal.IsInRole("Administrator"))
        {
             exercise.Visibility = Visibility;
              tot.IsEnabled = false;
        }

}

I implement the code role but there is no connection with database; instead I want to store the role from the database,create a method in C# and write down a code that after user's login to get access to the application.

To explain further: I gave Rolename = Administrator so if the user is admin he'll get this role, but I'm unsure how to retreive this information from the database and tie it to the user. Can anyone assist me?

Related (or possible duplicate) questions by Same User:

http://stackoverflow.com/questions/550667/username-and-role
http://stackoverflow.com/questions/544517/forms-authenticate
http://stackoverflow.com/questions/557791/how-create-role-to-put-in-the-database

Answer 1

Sorry, I find your question a little confusing.

You seem to want to have two tables, User and Role, with a many-to-many join table, UserRole, in between. User and Role must both have primary keys, both of which appear in the join table.

create table user
(
    user_id int not null, 
    -- other fields here, like username, password, etc.
    primary key(user_id)
);

create table role
(
    role_id int not null,
    -- other fields here, like role name, etc.
    primary key(role_id)
);

create table user_role
(
    user_id int not null,
    role_id int not null,
    primary key(user_id, role_id),
    foreign key(user_id) references(user),
    foreign key(role_id) references(role)
);

When you query for a user to see if they're authorized, you're joining the many-to-many table to bring back all the potential roles at the same time. If the credential you supply includes a role, your authorization code should check to make sure that it's a member of the set of potential roles at that time.

One minor bit of advice: Leave the "Tbl" out of your table names. They're redundant, in my opinion.

Answer 2

If you have the "UserTbl" and "RolesTbl" in your database, you should also have two classes called "UserTbl" and "RolesTbl" in your LINQ-to-SQL model.

To store a role in the database, instantiate one of thouse "RolesTbl" objects, set it's properties, and then add it to the LINQ data context. Same with the "UserTbl" object.

EDIT: here's the code sample requested - assuming you have set up your database, your LINQ-to-SQL model and used all the default names:

// create the LINQ-to-SQL Data context
UsersAndRolesDataContext dc = new UsersAndRolesDataContext();

// create new instace of "UserTbl" object
UserTbl newUser = new UserTbl();
newUser.UserID = "newuser";
newUser.Name = "Some NewUser";
newUser.EMail = "[email protected]";
newUser.Password = "TopSecret";

// add new user to the table of users in the data context
dc.UserTbls.InsertOnSubmit(newUser);

// create new instance of a role
RolesTbl newRole = new RolesTbl();
newRole.RoleId = "ADMIN";
newRole.RoleName = "Administrators";
newRole.Description = "User with administrative rights";

// add new role into LINQ-to-SQL Data context
dc.RolesTbls.InsertOnSubmit(newRole);

// write out all changes back to database
dc.SubmitChanges();

Does that help at all?

Marc