1

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 :)

F21
  • 32,163
  • 26
  • 99
  • 170
  • 1
    any reason to have 2 databases? – Your Common Sense Mar 12 '11 at 11:34
  • Main reason is that I would like to fully isolate customer data from employee and internal data. – F21 Mar 12 '11 at 11:39
  • 1
    what does it mean "fully isolate"? you can set permissions right in the same database – Your Common Sense Mar 12 '11 at 11:42
  • "fully isolate" the "data" not the database permissions. When I talk about permissions, I mean application level permissions, not the database permissions. Let's say in the adminstration area, I have its own users table storing just the employees in the company. They also get to modify their personal details that are kept on file. Now, in the website database, let's say there is a forum. I would like to be able to let employees log onto and post without creating a seperate account. As the adminstration database contains more sensitive info, I would like to keep both sides seperate. – F21 Mar 12 '11 at 11:46
  • 1
    how many database users going to be involved? – Your Common Sense Mar 12 '11 at 11:53
  • At the moment 2 databases. Down the track, if more customer websites are added, we would have 1 additional database for every 1 website that is added. In terms of users, I would like to have as many users as I can, as in infinite amount of customers and employees. – F21 Mar 12 '11 at 11:58
  • 1
    how many database **users** going to be involved? user which connects to the db server – Your Common Sense Mar 12 '11 at 12:00
  • There will be 1 database user that will be able to connect and edit the employees and website databases. There will be another database user that can only edit certain tables in the website database. This user will be used in the application for the website. – F21 Mar 12 '11 at 12:02
  • 1
    I see no use for many databases then – Your Common Sense Mar 12 '11 at 12:06
  • 1
    because there will be no "isolation" then – Your Common Sense Mar 12 '11 at 12:08
  • But it is still better than having employee data and data from a public facing website all mixed together is it not? For example, because the customer website will be public facing, if it is attacked, then the the attacker would only be able to delete and write to only some non essential tables, such as website statistics. If it was all in 1 database, then he could modify page contents and even make changes to employee data. – F21 Mar 12 '11 at 12:11
  • 1
    Why he can't delete another database? – Your Common Sense Mar 12 '11 at 12:30
  • Because the database user for the public facing website would only be able to write to a few tables inside the public facing website. He will only be able to read from a few tables in that database. He will have no access to the employee database at all. – F21 Mar 12 '11 at 12:36
  • 1
    So, there will be more than one database user? – Your Common Sense Mar 12 '11 at 12:49

2 Answers2

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.

Maxime Pacary
  • 22,336
  • 11
  • 85
  • 113
0

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.

ariefbayu
  • 21,849
  • 12
  • 71
  • 92
  • That's the solution I am leaning towards at the moment. In the table for the website, we can have normal users, or we can have "special users" which contains the ID of the user in the employee database. With this piece of information, we then first authenticate against the website users to see if theres a match. If we don't we then authenticate against the employees database to get a user id. we then match the user id with the "special users" in the customer db to see if they have access to that site. In this case, permissions on the customer site can be stored against "special users". – F21 Mar 12 '11 at 11:57
  • The above approach seems extremely complicated though. – F21 Mar 12 '11 at 11:57