Hide mysql database from other users

Hi:

Does anyone know how to make MySQL be like a virtual hosting database? For example, say a user named "John Doe" signed up, and usename is "jdoe". All his databases would be created like "jdoe_databasename". My question is, how can I have it so that he can add as many databases that have jdoe_ prefix, and the ONLY databases he sees is the ones with jdoe_ in front of them? He should see NO OTHER databases. Is this possible? Thanks for your help!!!

Ken Cooper

I'm sorry for asking, but I really searched the internet, and couldn't find ANYTHING of how to do this. Does anyone have a clue of how to do this? I hope so. Thanks a lot!!

I'm somewhat curious as to your need to do this.
Typically if a user needs a database, you(the admin) creates a database, and a user with full access to that, and only that, database. I've never used it, but I believe phpMyAdmin has user account management functionality to make this task fairly quick and easy.
If someone really needed full access to the server, you could consider running multiple instances of mysql.

It's for virtual hosting.

In MySQL you create the database, and then give the user permissions to it using GRANT.

Virtual hosting shouldn't really make a difference. You're the administrator; the user will only have access to the database you assign him.

-Tim

Hi:

Thanks for that information! My question is though, in phpMyAdmin, where it shows the database list on the left, how can I limit those databases to the ones that he creates? I would use the GRANT option. Could I please see an example of that? Ok, so I create a database, and it's the user's, but how does the user go about creating more, with:

username_dbname?

Thanks for your help!!

Ken Cooper

Simply put, the user doesn't create databases; that would be your job as the system administrator. Here's an example:

I've got a web server set for virtual hosting... domainA, domainB and domainC, each domain requires a database:

mysql> CREATE DATABASE domainA;
mysql> CREATE DATABASE domainB;
mysql> CREATE DATABASE domainC;

But one domain shouldn't see the database of another domain.

mysql> GRANT ALL ON domainA.* TO domainAuser@localhost IDENTIFIED BY "passwordA";
mysql> GRANT ALL ON domainB.* TO domainBuser@localhost IDENTIFIED BY "passwordB";
mysql> GRANT ALL ON domainC.* TO domainCuser@localhost IDENTIFIED BY "passwordC";

Then give the user his user id and password that corresponds to the correct database. Each can then create as many tables needed within the database to suit their needs.

mysql> USE domainC;
mysql> CREATE TABLE forum (post_id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, parent_id INTEGER UNSIGNED, time_stamp TIMESTAMP(12), topic VARCHAR(120) NOT NULL, author_id INTEGER UNSIGNED NOT NULL, post TEXT);
mysql> CREATE TABLE users (user_id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(50));
mysql> CREATE TABLE user_info (user_name VARCHAR(50) PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100), location VARCHAR(100), email VARCHAR(225));
mysql> CREATE TABLE user_auth (user_id VARCHAR(50) NOT NULL PRIMARY KEY, passwd VARCHAR(255));
mysql> ...

... and forget about mysqlAdmin for now. Sure a graphical interface is nice, but you'll need to learn the basics first. Then when you know what's going on and how things are done, you can start using admin programs to make things easier for yourself.

-Tim