Multiple Authentification methods, or implement role based Authentification?

Question

I have a problem I am trying to solve as smooth as possible, and also keep it as safe and flexible as possible!

I have a web app where I have 2 kinds of users; Back end users (trusted users; Admins!), and Customers (Front end users; not trusted in the same way)! They do work on some of the same data in the database, but the Customers can only do a subset of what the Back end users can do (like Salesforce users and customers "Self Service Portal")!

I also want to have 2 separate tables in the database for the back end users and the customers (tables are named User and Customer, and they both have a username and password row)...

Should I implement this with 2 different authentification methods, or should I use one authentification method and use roles instead (Then I would skip having 2 tables i the database with username and password, and let all use the User table)?

Answer 1

I would suggest you use one table and just setup roles. If properly done this will be easier to maintain. Of course this is the case if they have common fields such as name, surname.

Answer 2

The following database structure would allow for scaling if you find that you need to eventually maintain multiple roles for users (which happens alot from my experience).

CREATE TABLE user (
   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(120),
   created_ts DATETIME DEFAULT '0000-00-00 00:00:00'
);

CREATE TABLE user_role (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id INT(11) UNSIGNED NOT NULL,
  role_id INT(11) UNSIGNED NOT NULL 
);

CREATE TABLE role (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(125)
);