How to design database for authorization and authentication

Question

I normally in my projects use such these code:

If user.IsInRole("Admin") Then 
  deleteButton.Visible = True 
else 
  deleteButton.Visible = False

But I want to control roles, which can see this button in database.

For this purpose how should database design be?

Thanks.

Answer 1

Make the design whatever you want to be, but in ASP.NET side implement your own MembershipProvider. This will translate your DB design into users/roles that .NET can use. After that you can use it as usually - with user.isInRole("Admin") :)

Answer 2

Well, one design is to have tables such as:

User(UserID, ...) PK = UserID

Role(RoleID, RoleName, ...) PK = RoleID

UserHasRole(UserHasRoleID, UserID, RoleID) PK=UserHasRoleID ; Unique= (UserID, RoleID)

That's one method. This is a role based system rather than a discretionary, object based authorization system (In a discretionary system, you'd set permissions on each object, say this User x has the DELETE permission for Customers, or something like that).

Answer 3

May be I should be more clear, but I don't know how :). I will try again.

For example I use for my deletebutton this code:

if user.isInRole("Admin") then 
  deleteButton.visible = true 
else 
  deleteButton.visible = false

Afte a whole, make a decision that, user have role "moderator" should also see delete button. So I should change my code like this:

if user.isInRole("Admin","Moderator") then 
  deleteButton.visible = true 
else 
  deleteButton.visible = false

If I have a database design to take control this, I didn't need to change my code for it.

Well, How should it be?

Answer 4

Assuming you're using .NET, one way to do this is to implement your own Role and Membership Providers. Then, you could add functionality by implementing an interface that contained the items you wanted (I've just knocked this sample up off the top of my head, so I apologize if it seems a bit rough):

public interface ICustomRole
{
  bool IsInRole(string userName, object[] params roles);
}

public class MyCustomRole : RoleProvider, ICustomRole
{
  public IsInRole(MembershipUser user, object[] params roles)
  {
    if (roles == null || roles.Length == 0)
      throw new ArgumentException("roles");
    // Put your logic here for accessing the roles
  }
}

Then, in your code you would do this:

bool isValid = ((ICustomRole)Roles.Provider).IsInRole(
  User, new[] { "Admin", "Moderator", "Validator" });

Answer 5

LDAP is the best option for For Authorization and Authentication. you can use openLDAP API for same purpose.

Answer 6

Code:

public class YourSqlRoleProvider : System.Web.Security.RoleProvider
{
    private string ConnectionString { get; set; }

    public override void AddUsersToRoles(string[] userNames, string[] roleNames)
    {
        // logic here
    }

    public override string ApplicationName
    {
        get
        {
            throw new NotSupportedException();
        }
        set
        {
            throw new NotSupportedException();
        }
    }

    public override void CreateRole(string roleName)
    {
        throw new NotSupportedException();
    }

    public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
    {
        throw new NotSupportedException();
    }

    public override string[] FindUsersInRole(string roleName, string userNameToMatch)
    {
        throw new NotSupportedException();
    }

    public override string[] GetAllRoles()
    {
        // logic here
    }

    public override string[] GetRolesForUser(string userName)
    {
        // logic here
    }

    public override string[] GetUsersInRole(string roleName)
    {
        throw new NotSupportedException();
    }

    public override bool IsUserInRole(string userName, string roleName)
    {
        return GetRolesForUser(userName).Contains(roleName);
    }

    public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
    {
        this.ConnectionString = ConfigurationManager.ConnectionStrings[config["connectionStringName"]].ConnectionString;

        base.Initialize(name, config);
    }

    public override void RemoveUsersFromRoles(string[] userNames, string[] roleNames)
    {
        throw new NotSupportedException();
    }

    public override bool RoleExists(string roleName)
    {
        throw new NotSupportedException();
    }
}

Web.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <connectionStrings>
        <clear />
        <add name="YourConnectionString" providerName="System.Data.SqlClient" connectionString="connection string here" />
    </connectionStrings>
    <system.web>
        <roleManager defaultProvider="YourSqlRoleProvider" enabled="true">
            <providers>
                <clear />
                <add name="YourSqlRoleProvider" type="YourSqlRoleProvider" connectionStringName="YourConnectionString" />
            </providers>
        </roleManager>
    </system.web>
</configuration>