LinuxQuestions.org
Latest LQ Deal: Latest LQ Deals
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices


Reply
  Search this Thread
Old 08-01-2011, 06:16 PM   #1
secretlydead
Member
 
Registered: Sep 2003
Location: Qingdao, China
Distribution: mandriva, slack, red flag
Posts: 249

Rep: Reputation: 31
mysql for multiple websites


Hi,

I currently have a single mysql instance for a couple of websites. Both websites require that the user provide a username.

The thing I just noticed is that two people might choose the same username for both applications. Both of them would have grants to each others' tables, although neither would ever be able to access them, since they can only access through php scripts.

But, I'm now guessing this is not best practice. What does one do? Run multiple instances of mysql for several websites?
 
Old 08-01-2011, 06:48 PM   #2
shambler
Member
 
Registered: Sep 2006
Location: Canada
Distribution: openSUSE 11.3, Xubuntu 10.10, Ubuntu 11.04
Posts: 53

Rep: Reputation: 23
You could add a column which identifies which site the username belongs to.

The login handler will know what site a login is coming from so you could indeed have the same name - but independantly on site A vs site B.

You can also duplicate the table structure in different schemas - one for site A, one for site B. That is probably to most rational. That way, only the login credentials for the site change - nothing else, and total independence.
 
Old 08-02-2011, 12:05 PM   #3
secretlydead
Member
 
Registered: Sep 2003
Location: Qingdao, China
Distribution: mandriva, slack, red flag
Posts: 249

Original Poster
Rep: Reputation: 31
Quote:
Originally Posted by shambler View Post
You could add a column which identifies which site the username belongs to.

The login handler will know what site a login is coming from so you could indeed have the same name - but independantly on site A vs site B.
Sorry, I don't understand. Where would I add the column so that when a new user uses a name already in the information_schema table, he doesn't update their password with a new one?
 
Old 08-02-2011, 07:27 PM   #4
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Rocky 9.2
Posts: 18,348

Rep: Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749
I think shambler was assuming you store the user's names in the app schema, separate to the actual mysql schema (ie data dictionary).
A separate schema for each App would be the soln & have each App handle access by storing usernames/passwds in the relevant schema, probably in a dedicated table.
Only the App itself should actually have a (real) DB login.
This is the general rule.
Otherwise, you will have to enforce a unique username on everybody across all apps. Do not try to mess with the information_schema.
HTH
 
1 members found this post helpful.
Old 08-04-2011, 11:19 AM   #5
TenTenths
Senior Member
 
Registered: Aug 2011
Location: Dublin
Distribution: Centos 5 / 6 / 7
Posts: 3,461

Rep: Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552Reputation: 1552
Quote:
Originally Posted by secretlydead View Post
I currently have a single mysql instance for a couple of websites. Both websites require that the user provide a username.

The thing I just noticed is that two people might choose the same username for both applications. Both of them would have grants to each others' tables, although neither would ever be able to access them, since they can only access through php scripts.

But, I'm now guessing this is not best practice. What does one do? Run multiple instances of mysql for several websites?
Are you getting confused with the concept of a MySQL user which has permissions on a database and an application user which is created inside an application database?

Ok, so I'm assuming you have a single MySQL instance, for each of the website applications you create a database, you then create a MySQL user and permission that MySQL user for the database. Are you then installing an application on each website that access the database you've created for them? I'm guessing that is the case as you say "Both websites require that the user provide a username." Users would then be created in the databases tables and access to the website application controlled from that table.

For example, there's a user Bob and a user Sue who are going to be creating websites that use MySQL databases.

You create a database bob_data and then create a user bob_user, grant bob_user all privs on bob_data.
Do the same for sue_data and sue_user.
Now Bob and Sue install their website applications (let's say it's a forum) and they use their own database, so the tables are the same etc. etc. but they are in separate databases. Sue has no permissions on Bob's database so she can't access his tables and the other way around.
Alan now comes along and registers on Bob's forum and also on Sue's forum.
Alan's user details (and permissions) are held within each application's database. So at no point does Alan actually have a MySQL username. He only exists in two separate applications.

Now, regarding "best practice", it would depend on the environment and resources.

There would be a school of thought that says that running a fully separate MySQL instance for each database would be the way to go, the advantage is that if one database or user screws the MySQL server badly then the other MySQL processes would be unaffected. However this does mean that there is an additional management overhead in setting up and maintaining this kind of infrastructure. You'd need fully separate instances using separate sockets or ports and you'd have to make your website admins aware of which instance to connect to for their application.

Personally I've never had any problems with using a single MySQL server with multiple databases on it. My own web server runs over 50 databases on a single MySQL instance and I've never had any problems with the actual server, it's always been individual applications. I've something like 20 different WordPress blogs and two vBulletin forums (one that does around 1.4M page displays a month) and there's been no issues with cross-database security.

Hope this helps.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
apache and multiple websites jonaskellens Linux - Server 7 06-16-2010 08:58 AM
[SOLVED] multiple websites in LAMP anon091 Linux - Newbie 6 09-23-2009 01:43 PM
One server, multiple domains/websites. jef3189 Linux - Server 4 04-04-2009 11:21 PM
Multiple Websites - 1 server metallica1973 Linux - Server 8 06-16-2008 11:18 AM
How to configure multiple websites on one PC? <Ol>Origy Linux - Networking 8 08-16-2004 07:16 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Server

All times are GMT -5. The time now is 01:49 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration