0

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?

2 Answers 2

0

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;
}

?>
3
  • 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, 2011 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, 2011 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, 2011 at 11:33
0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .