Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Sep 8 '11 at 14:44

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

add comment

2 Answers 2

up vote 0 down vote accepted

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);
define('ADMIN','true');
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;
}

?>
share|improve this answer
    
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
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.