# Don't list the databases which don't belong to the user

Using MySQL and PHPMyAdmin I want to give some users all the permissions to create databases and delete their own. Basically a user should only see his created databases and could do everything he wants on them, but he shouldn't interact nor see the databases of the other users.

I've seen a solution with the GRANT ALL ON prefix_. but it implies that the user knows the prefix and use it to create all it's databases.

Any less restrictive alternative?

-

## migrated from stackoverflow.comSep 8 '11 at 14:44

This question came from our site for professional and enthusiast programmers.

I use this PHP script to to add a user with the permissions you specified. this script also randomize a password and dump it into a file in the user home dir. you will need to fix it a bit because it depends on some other files, but it should be easy enough to just take the Sql statements and use them.

#! /usr/bin/php
<?php
error_reporting(E_ALL);
require_once 'utils/utils.php';

if (count($argv) < 2) { echo "usage : php -f ".$argv[0] . " user\n";
return;
}
$user =$argv[1];
$pass = createRandomPassword(10); global$conn;
if(false === $conn->query("CREATE USER '$user'@'localhost' IDENTIFIED BY '$pass'")) die("mysql error\n"); if(false ===$conn->query("GRANT USAGE ON * . * TO '$user'@'localhost' IDENTIFIED BY '$pass'")) die("mysql error\n");
if(false === $conn->query("GRANT ALL PRIVILEGES ON $user\_% . * TO '$user'@'localhost'")) die("mysql error\n"); echo "Created mysql user$user/$pass\n"; savePasswordFile($user,$pass); function savePasswordFile($user,$pass) {$passfile = "/home/$user/passwords.txt";$f = fopen($passfile,"a"); if (!$f) die("Error opening passwords file");
fputs($f, "Mysql login:\nuser :$user\npass : $pass\n\nYou may create databases named ".$user."_*, for example ".$user."_blog\n"); chown($passfile,"$user"); chgrp($passfile,"$user"); chmod($passfile,0700);
fclose($f); } function createRandomPassword($length)
{
$chars = "abcdefghijkmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ023456789-!@#$%^&*";
srand((double)microtime()*1000000);
$i = 0;$pass = '' ;
while ($i <=$length) {
$num = rand() % strlen($chars);
$tmp = substr($chars, $num, 1);$pass = $pass .$tmp;
$i++; } return$pass;
}

?>
-
You use exactly what I try to avoid: prefix the databases by the user name. I don't want to have any prefix if possible. –  koskoz Sep 8 '11 at 16:58
I don't think mysql supports what you need then. you can run multiple instances of mysql - but this is probably an overkill. i`ll stay with database prefix - users find it intuitive enough. –  Omry Sep 8 '11 at 17:01
Indeed, after a lot of researching it seems that I can't do what I want. I'll stick with the prefix solution then. –  koskoz Sep 9 '11 at 11:33

you can use webmin and create individual users and they can create databases.. http://www.webmin.com/

-