Sharing users between 2 databases

Question

I am developing this application in PHP. The application will consist of an adminstration area which will also contain employee functions. The other part of the application is the customer facing website.

The administration area has its own database. The customer facing website also has its own database.

In the administration database, I have a table with users and I also plan to implement RBAC so that users can have roles, permissions and so on.

The customer facing website also allows customers to register and that's stored in a user table in customer website database.

What I need is to be able to have employees logging on at the customer website. They also need to have permissions which controls which parts of the customer website they can modify and which rows in the customer website database they can change.

What's the best way to implement this?

Cheers :)

Answer 1

I would recommend to always merge, when possible, tables handling the same concept (Users here).

The problem here is the data access security, no matter how your data is stored (one or two tables, one or two databases).

With MySQL (and many other common DBMS), you can :

• Create a MySQL account which will have only access to some tables, do only some operations on it (e.g. only SELECT), access only some columns etc.

• Hide a table from a MySQL account and give only access to a view on this table (filtering columns/table results).

Whatever is your data structure, you will need to check some employee data (login, password, permissions etc.) and your code will have to use a MySQL account with enough privileges for that.

So I would advise you to keep your users in one table, add tables to describe roles (employee, customer, etc.), and code wisely.

Keep in mind to address separately database design and data access security issues. Data access security should not lead you to illogical choices in database design.

Answer 2

You can do something like this:

$sql = "SELECT * FROM employeedb.employee_user_table WHERE employee_id = '???'";

As comment problem from thread starter. For two user tables, you can use solution like this:

• create new table. eg: global_user_table with field:

  • user_id
  • pass
  • table_source (real table source)
  • is_special_user (or something like that)
  • additional_field1
  • additional_field2
  • etc, etc, etc

• everytime user login, have it check on this table and store table_source on session, for later usages.