469,646 Members | 1,100 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,646 developers. It's quick & easy.

Secure Database Design Part II


Hey folks -

Thanks to everyone who gave input to my concerns. Of course, we don't intend
to have the mysql port open to the world. We will have Apache/PHP connect on
a unix socket, or to another machine with a cross-cable on non-routeable
IPs.

But now I have another question. We are working on a web database to allow
our suppliers to log on and submit information that they would otherwise
fax, email, or phone to us. It would reduce work in our office, and reduce
errors in duplication of our information. But, we are very concerned about
security!

We aren't worried so much about outside hackers as we are about legit users
trying to gain access to information they shouldn't. Some of our suppliers
are overseas and we think they have no qualms about trying to hack the
system, knowing the stunts they have pulled in the past. It would be
extremely difficult to pursue any problems legally, since it would be
international, and the damage would be already done.

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 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
Network Administrator
IMI International, Inc.
614.839.2500
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw

Jul 19 '05 #1
0 1313

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Sarah Tanembaum | last post: by
1 post views Thread by opt_inf_env | last post: by
reply views Thread by Lefevre, Steven | last post: by
3 posts views Thread by Annette Massie | last post: by
2 posts views Thread by Jason Smith | last post: by
3 posts views Thread by Pachydermitis | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.