Hey folks --

At my workplace, we are designing a (hopefully) secure website/database for
our suppliers, customers, salespeople, and office to communicate indirectly.
It will be based on PHP/MySQL.

We aren't worried so much about outside hackers as we are about legit users
trying to gain access to information they shouldn't. Our suppliers and
salespeople compete against each other, and I'm certain they would take any
opportunity to gain an advantage.

So, here's my question. Good database design dictates that I normalize my
tables. So, in this simplified example, we have a table of supplier quotes:

supplier_id
part_id
quote_price
quote_date

All of our suppliers would be drawing from the same table, via php. I'm
worried that good database design might be more susceptible to information
'spilling over' -- what if I make a simple mistake and put the wrong
supplier_id with a new user's logon? That new user would see all the parts
that belong to whatever company I mistakenly associate them with.

I'm not so worried about, say, suppliers seeing sales data. All the php
pages will be protected by Unix filesystem permissions, so I can be
reasonably certain that only those belonging to the suppliers group will be
able to execute supplier_*.php. Even if they do load some sales_*.php page,
then the MySQL user permissions will stop them from actually seeing any data
on the page. So there are two layers of security between sales and
suppliers, for example. I would have to make two mistakes (one in the php
file permissions, and then again on the MySQL tabke permissions) for them to
have access to sales data.

But, when all suppliers are accessing the same pages, it's up to my careful
hands to make sure they are pulling only their records out of the table. If
I make a mistake in a query, it might pull up other records, or even all
records!

Of course if I design it completely perfectly the first time, I don't have
to worry about anything. But I'm not perfect and I don't make perfect
things.

So, I'm thinking I should violate good design principles, and setup
identical tables for each supplier, salesperson, customer, etc. That way,
since they share the same PHP pages, they aren't all pulling data from the
same table. If there is any mixup in the query, the user doesn't have the
MySQL permission to pull data from another suppliers table.

Does this make sense?

Steve Lefevre