Here's an interesting problem that you people might have a nice answer
to:
Imagine my database holds a small amount of personal data relating to
the customers of ten different companies. The data is sensitive. Each
of these companies is totally paranoid about another company seeing
their data. (Apart from my company which hosts the database, which can
see all the records). If one company ever EVER sees another company's
data then my company will be sued out of existence.
Keeping the data in one table with a field identifying to which
company the data belongs is not good enough - a missed WHERE clause in
an SQL statement means the company goes down in flames.
Perhaps a solution is to have a separate table for each company. Then
each company can be given a user name and password, the same as their
application login. The application could then connect to the database
using these credentials, and so could not possibly see the other data.
(My company would have permissions granted to see all the data.)
What do people think is the best way to solve this problem?
(Using Oracle 9i Standard Edition)
