Mark Phillips wrote:
On Sat, Oct 3, 2009 at 3:06 PM, Martin Gainty <mgai...@hotmail.com> wrote:
depends on the relationship of the Data Tables and the Users that use them
for instance if I was to setup a table of outgoing calls from 2 distinct
individuals :
Me> calls to HarvardMedicalSchool, MassGeneral,
SomervilleHospital and AMA
VereinDesKrankRufs>calls to Biff,Tony,EdSoprano and Destiny
so as you can see the difference between my calls and Vereins calls should
never be joined
as Vereins customers are distinctly not mine and mine are not his
Moreover my contact table would contain Degrees and titles where Vereins
customers
have no need for that
So in this case it would make perfect sense for my Database to be separate
and distinct from Vereins database..if for no other reason than the schemas
are completely difference
With an emphasis on security once Verein initiates populating his records
on your DB by populating the same tables and using the same join
relationships it will be impossible to force him to not use those tables
or even to restrich his access to the slave server while you're updating
the master
You can restrict access by GRANT SELECT on the tables to Verein but that
would last only a week or 2 until Verein requests update and insert access
to the DB. Once the INSERT and UPDATE grants are made you wont be able to
separate his records from yours
Keep the 2 separate is my suggestion..MySQL is inexpensive and HW is cheap
so this should be a low cost solution for you
Keep us apprised and any feel free to inquire on any operational details
you may require.
Thanks! To make sure I understand. Even if the schemas are the same, if the
data is not related, nor is meant to be combined in some way (eg rolled up
or summed in some way), then creating a separate database for each user is a
better way to go; or at least a meaningful way to go. A side benefit is
greater security from the stand point that user a cannot get to user b's
data.
Can't I achieve the same level of security if each row has a userID, and all
queries use a "where userID=xxxxxxx" clause?
Mark
no, don't confuse that with database security. There are too many ways
to get around that sort of trick through SQL injection attacks. Read
http://dev.mysql.com/doc/refman/5.4/en/privilege-system.html for a
starter on privileges and security.
But as long as you're not needing to regularly combine and aggregate the
data then creating separate databases is a reasonable option.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
