Link to home
Start Free TrialLog in
Avatar of dcb2007
dcb2007

asked on

How to setup MySQL for a virtual site

Hello,

my problem is as follows, the server admin is on holiday and I havn't got a clue on how to setup up permission for a customer with a virtual site on our server to use MySQL (which is already installed on the server) ...the guy who normally does it said something about writing it into a table????? the system configuration is

Cobalt RaQ 3i
Redhat 5.2?
MySQL newest one?

now this question is fairly straight forward for somebody who does that sort of stuff every day, the reason I class it as hard is that you have to make it understandable to me (with something like no linux knowledge)i.e. please explain it clearly...

many thanks
Avatar of superschlonz
superschlonz

Before you start you need some informations:
 - name of the database(s) they may use
 - host(s) from where they may connect
 - username(s) and password(s)
 - which privileges should be granted

If you know that it's easy:

GRANT <privileges> ON <database>.* TO <user>@<host>
  [IDENTIFIED BY 'some_pass'] [WITH GRANT OPTION];

Have also a look at the documentation at http://www.mysql.com/documentation/mysql/bychapter/ (chapter 3).

Give us some more information what exactly you have to do (create database, create user or just change priviledges).
Avatar of dcb2007

ASKER

well, the customer who got a virtual host on our server wants to be able to use MySQL for their website...the host they can connect from? shouldn't that be in this case local only i.e. he can only use the SQL with the webspace he got from us? or is it standard to allow them to connect from anywhere? do I need to create a database for each virtual site? because we got the mysql running for a couple of virtual hosts? username and password? I can just use the same as for the webspace they got right?....hmmm which privileges should be granted...no idea...basically that the customer can use mysql on his/her site in together with PHP (which is running for all sites)

if I know that its easy...well as I said, I dont know anything about linux or mysql i.e. what do I do? do I just go login as root and type in that "grant command"...by the way the admin gave me a password for mysql, where does that come in...?
> well, the customer who got a virtual host on our server
> wants to be able to use MySQL for their website...the
> host they can connect from? shouldn't that be in this
> case local only i.e. he can only use the SQL with
> the webspace he got from us?
OK, then the host is localhost.

> or is it standard to allow them to connect from anywhere?
That could be a security problem. There are two possibilities:
1. You allow it, the customer will then be able to create the needed tables and insert data which is needed.
2. you don't allow it, but then you will have to create tables etc. or the customer can login via ssh.
You could try to find out how it is configured for other customers.

> do I need to create a database for each virtual site? because we got
> the mysql running for a couple of virtual hosts?
I think normally every customer has it's own database. It's  easyer for administration.

> username and password? I can just use the same as for the  webspace they got right?
Yes.

> ....hmmm which privileges should be granted...no idea...basically that the customer
> can use mysql on his/her site in together with PHP (which is running for all sites)
If the customer has its own database you can give him all privileges for his database.
Now the commands you need:

Login to the machine and start mysql (you need the root password of mysql):

mysql -p -u root

Select the mysql database (the one with the user table):

use mysql;

Look which databases exist:

select * from db;

Look which users exist and which privileges they have:

select * from user;

Create a database (replace db_name by customername, have also a look at the documentation 4.1.5  'Database, Table, Index, Column, and Alias Names'):

CREATE DATABASE db_name;

Now create a user and give him access to the database:

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON db_name.* TO username@localhost IDENTIFIED BY 'password';

You have to look if the other users also have CREATE and DROP privileges and perhaps have remote administration privileges (an entry where the host is not localhost).
Avatar of dcb2007

ASKER

many thanks for your answer, I solved the problem in another way i.e. I cheated a bit - I installed webmin and after I set the sql dir. it was all quite easy from there...but now I got another problem the customer can see the other databases on the server (from other customers) he has of course not the right to do anything to them but he can see them using phpmyadmin? is that always like that? for the hosts from which it should be accessable I put localhost?
ASKER CERTIFIED SOLUTION
Avatar of superschlonz
superschlonz

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial