Security vs. Database design?

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 Received on Mon Sep 08 2003 - 23:05:27 CEST