[RESOLVED] Best practices segregating user data

phpSimon

Hi everyone

I'm building an online web reporting site that connects to a remote system with a MSSQL backend.

There are multiple users and multiple remote backends.

Users only have access to one backend. The server is out in the cloud and makes a remote connection to the MSSQL data; there may be up to 50 users per backend.

In addition, there's a local MySQL system database which contains data and settings relevant to the way each user gets reports from their remote system, and 'company wide' settings for the remote backends too.

I'd really appreciate some feedback on the approach I use to ensure a user connects to the correct backend databases, and in particular on the best approach to segregate user data within the local database.

When users log in, a sessvar is set which contains a companyID.

Every page in the system uses this sessvar to get the remote MSSQL address and login credentials from a 'companies' table, and connects to the remote database. Every page disconnects from the remote database when it's finished gathering data.

User data segregation within the local database is also handled by the companyID.

For example, company 1 and company 2 might make 10 types if widget, but the users for both companies only want to report on 3 of the widgets.

My local widgets table will contain all 6 widget ID's plus a companyID. When I query the widgets database, I only return the widgets for the company this user belongs to.

This doesn't really feel strong enough. A simple bug like a missing "...AND WHERE companyID='1234'" in a query will result in a user seeing ALL the widgets for all the companies (although they wont get any data from the wrong remote company databases because they can only connect to the right remote database). 2 companies could well use the same widget ID at best, this would cause confusion.

My other options seem to be:

1) Have one local MySQL database per company that uses the system. I can use the same companyID sessvar to get the table name, and connect to that.

2) Above, plus a whole new /company folder on the reporting server, so the code is unique to each company too. This makes updates difficult though, and is bound to result in more company specific workarounds so the code will gradually evolve into distinct systems. Seems overkill anyway.

3) Perform some sort of double-check every time I read the results of a query, but again I think this is overkill.

Any advice, suggestions or approaches used would be very much appreciated.

Thanks

johanafm

phpSimon;10994328 wrote:
I'd really appreciate some feedback on the approach I use to ensure a user connects to the correct backend databases, and in particular on the best approach to segregate user data within the local database.

Use one database which everyone connects to. Tables database(id, url/ip, schema_name) company(id, name, db_id), user(id, password, name, company_id)

User logs in and credentials are checked against user table. Joins on company and database will instantly also give you the ip to the db and name of the schema to use.

phpSimon;10994328 wrote:
When users log in, a sessvar is set which contains a companyID.

The only thing you need to store is the user's id. You can of course opt to store company_id as well as database ip and schema name.

phpSimon;10994328 wrote:
Every page disconnects from the remote database when it's finished gathering data.

Not really. The web server sending the page disconnects from the database when it's told to do so or when the http connection to the user is closed.

phpSimon;10994328 wrote:
User data segregation within the local database is also handled by the companyID.

How can you separate Joe's and Jane's data from one another if they both work for company 1? If each company has its own schema (or dbms), all you need is the user's id. If more than one company (can) share the same schema, you'd need both company and user id for this.

phpSimon;10994328 wrote:
For example, company 1 and company 2 might make 10 types if widget, but the users for both companies only want to report on 3 of the widgets.

I really don't understand what you're talking about here. I still have some questions, but I might be shooting wide since I don't get what you want to achieve.

Want to or can? If a user only want to report on 3 widgets, why should you put any kind of restriction on how many they can report on? What if a new user wants to report on the other 7? Or on another subset of 3?

phpSimon;10994328 wrote:
My local widgets table will contain all 6 widget ID's plus a companyID. When I query the widgets database, I only return the widgets for the company this user belongs to.

Before you had 10 and 3, now you have 6. I assume they're all examples, albeit a bit confusing.
This is what I meant above: Return all widgets for said company and let the user choose which they want to use.

phpSimon;10994328 wrote:
This doesn't really feel strong enough. A simple bug like a missing "...AND WHERE companyID='1234'" in a query

Well, nothing is strong enough if you have "bugs" like that. I'm hesitant to even call this a bug - I'd call it "if you can't trust your devs with things like this, hire new people. Anyway, with this kind of reasoning, you can't store user credentials in a database and use that with a login system. What if you're missing WHERE user.password='$pwd' AND user.name='$name', i.e. your login query consists only of "SELECT * FROM user".

phpSimon;10994328 wrote:
will result in a user seeing ALL the widgets for all the companies (although they wont get any data from the wrong remote company databases because they can only connect to the right remote database).

But why would you store names of all widgets in every database but only store the actual widget (code?) in one database? And why would the user not be talking to the correct database in the first place? Once again, at login everyone uses the same database. After that, every single user only uses the one database they are supposed to use (unless the user or you for some reason want to reauthenticate, change or delete information in the "login database").

phpSimon;10994328 wrote:
2 companies could well use the same widget ID at best, this would cause confusion.

Or are you saying that you provide the widgets and the companies (not end users) are selecting some of them and the end user finally uses them? Either way, then if two companies are using the same widget, they would indeed store the same widget id. And yes, retrieving this widget in the end might be done from one single database which stores all such information, unless you for some particular reason want to duplicate the widget information when a company selects it so that you retrieve it from the "widget database" once and then store it in the company's specific database.

phpSimon;10994328 wrote:
My other options seem to be:

I don't really see what you wrote above as "an option". To me it was not specific enough to qualify for more than "a rough outline", but sure, I might be missing the point.

phpSimon;10994328 wrote:
1) Have one local MySQL database per company that uses the system. I can use the same companyID sessvar to get the table name, and connect to that.

Use the company id to retrieve the TABLE name? If each company has its own DBMS (which is how I understand your use of the word database here, i.e. databae server), which incidentally is how I understood your description above (aka option 1), why on earth would each DBMS have different schemas?

phpSimon;10994328 wrote:
2) Above, plus a whole new /company folder on the reporting server

Reporting server? What, where and how? I've no idea what this is supposed to do. And thus I've obviously no idea what a company folder would be supposed to do.

phpSimon;10994328 wrote:
, so the code is unique to each company too.

Why would anything your wrote make each company have unique code?

As I've understood everything so far is that you want to or have to use several DBMS or schemas, while everyone actually sends requests to the exact same place, i.e. are using the same URIs. This may of course, and is likely to, actually go to several different physical machines in the cloud, but that's not your concern. Those machines will all be using the exact same code.

phpSimon;10994328 wrote:
3) Perform some sort of double-check every time I read the results of a query, but again I think this is overkill.

If you have information, why would you double check it? If it's for the same reason as you stated above that you might miss "WHERE stuff=value", then the double check might also fail, which means you'd have to go from over kill to over over kill.

Make certain that you have a system in place which deals with handling credentials and access rights automatically, such as base classes that deals with db retrieval and updates. Then you extend from these base classes and whenever you want to retrieve something, you also pass either the logged in user_id along or a user class instance along to handle access retrictions. Thus, for example a widget class would have ::getAll(User $user) which will retrieve all widgets that belong to the company that this user belongs to. Thus, you have to get the code right in one single place. Similarily, assuming a user can update a widget, Widget::store($user) would contain something like

UPDATE widget
INNER JOIN company_widget cw ON cw.widget_id = widget.id
SET ...
WHERE widget.id = $id AND cw.company_id = $user->getCompanyId()

or possibly with an addition in the whereclause

... AND $user->canUpdateWidget()

if only some type of admin or other user with specific user rights is allowed to do so.

phpSimon

Thank you, johanafm, for your really comprehensive and helpful reply. I'm sorry it took so long to reply, very rude of me!

My questions were a bit obtuse, as I can see now, I should probably have used a bit less detail.

I found your comments and guidance incredibly valuable, they've added to my perspective on some of the issues. For anyone else looking at this thread and interested in what approach I took:

Trying to keep this to a general description - mine is a PHP application running on my server. I'll call this 'the system'. The system talks to a number of remote DBMS - each one belongs to an individual client company. The client company employees use my system to report on their DBMS.

I'd used fairly simply code to connect a user to their DBMS, and this inherently prevented them from getting connected to the 'wrong' one.

However, I need to store certain 'item' details from the remote DBMS in a table local to my system. This is because each user reports on a specific set of items from their own DBMS that are defined by me. Without this functionality, it's not trival to get the relevant reports.

What I wanted to do was check my methodology of preventing a user from seeing details of another client's items. In reality, because the user logged in will be connected to their own DBMS, they wouldn't get any sensible reporting because the items in question would most likely be alien to the DBMS they're connected to.

johanafm said, quite correctly:

...nothing is strong enough if you have "bugs" like that. I'm hesitant to even call this a bug - I'd call it "if you can't trust your devs with things like this, hire new people. Anyway, with this kind of reasoning, you can't store user credentials in a database and use that with a login system. What if you're missing WHERE user.password='$pwd' AND user.name='$name', i.e. your login query consists only of "SELECT * FROM user".

but I wanted a belt and braces approach. Sometimes, no matter how hard you try, bugs (or poor devs!) creep in and I wanted a fallback.

What I did in the end, after more research and asking around, was create a unique database in my own DBMS for each client company. I have common tables for things like users and other systemwide settings, but when a user logs in they are connected to the remote DBMS for data, and to a local table specific to their company which contains the list of 'items' they are reporting on.

However, I appreciate I could have done it much more simply. If I wrote it all again, I'd probably start from this approach - changing it all was NOT trivial!