MySQL accounts from a file

[bionik]

Is there a way to create MySQL accounts, set the rights, and create the password by reading the usernames from a file? I use the following script to create accounts from a file.

#!/bin/sh
#
#
# Creates users from a file
#
FILE=/tmp/scripts/pouser.txt

if [ ! -f $FILE ]
then
echo "ERROR $FILE doesnt exist.."
exit 1
else
echo "Creating user accounts from $FILE"
fi

USERS=`cat $FILE`

for i in $USERS
do
echo "Creating user:$i"
/usr/sbin/useradd -m -d /home/$i -s `which bash` $i
echo "Account $i Created!!"
done

Can I use a similar script to create MySQL accounts from a file? Thanks

[jasonlambert]

How much PHP do you know?

A simple script could open the file, read a username, then execute the necessary mysql command to create the user as per http://dev.mysql.com/doc/mysql/en/Adding_users.html

Jason

[bionik]

Not too good at PHP, do you have a sample script that I can modify? Thanks

[jasonlambert]

take a look at http://www.hotscripts.com/ you may find one on there.

Jason

[jasonlambert]

<?php
// get contents of a file into a string
$filename = "/usr/local/something.txt";
$handle = fopen&#40;$filename, "r"&#41;;
$contents = fread&#40;$handle, filesize&#40;$filename&#41;&#41;;
fclose&#40;$handle&#41;;

$conn = mysql_connect&#40;"localhost", "root", "password"&#41; or die&#40;mysql_error&#40;$conn&#41;&#41;;

$lines = explode&#40;"\n", $contents&#41;;

foreach&#40; $lines as $username &#41;
&#123;
     $query = "GRANT ALL PRIVILEGES ON *.* TO '&#123;$username&#125;'@'localhost' IDENTIFIED BY 'some_pass'";

      mysql_query&#40;$query, $conn&#41; or die&#40;mysql_error&#40;$conn&#41;&#41;;
&#125;

mysql_close&#40;$conn&#41;;
?>

I've just wrote that, with the file opening/closing code direct from php.net, its not tested, so test if before running on a live server! The script assumes that the file contains ONLY a list of usernames, one username per line.

Good luck!

Jason

[sdousley]

u could try Webmin I have installed that, and there's a good MySQL web based interface on there, or alternatively phpMyAdmin is good for MySQL databases

[bionik]

Thanks, that script worked great......I also used it to create databases with the same username file. The only thing I cant get to work is im trying to give the user full rights on that specific database. I tried the following but its not working? =( Thanks for your help you guys are the best.

<?php
// get contents of a file into a string
$filename = "/usr/local/something.txt";
$handle = fopen($filename, "r");
$contents = fread($handle, filesize($filename));
fclose($handle);

$conn = mysql_connect("localhost", "root", "password") or die(mysql_error($conn));

$lines = explode("\n", $contents);

foreach( $lines as $username )
{
$query = "GRANT ALL PRIVILEGES ON DATABASE.'{$username}' TO '{$username}'@'localhost' IDENTIFIED BY 'some_pass'";

mysql_query($query, $conn) or die(mysql_error($conn));
}

mysql_close($conn);
?>

[jasonlambert]

<?php
// get contents of a file into a string
$filename = "/usr/local/something.txt";
$handle = fopen($filename, "r");
$contents = fread($handle, filesize($filename));
fclose($handle);

$conn = mysql_connect("localhost", "root", "password") or die(mysql_error($conn));

$lines = explode("\n", $contents);

foreach( $lines as $username )
{
$query = "GRANT ALL PRIVILEGES ON DATABASE.'{$username}' TO '{$username}'@'localhost' IDENTIFIED BY 'some_pass'";

mysql_query($query, $conn) or die(mysql_error($conn));
}

mysql_close($conn);
?>

That is only giving them access to one table (called their username) within a database called DATABASE. You would proberly need to change that to:

$query = "GRANT ALL PRIVILEGES ON '{$username}'.* TO '{$username}'@'localhost' IDENTIFIED BY 'some_pass'";

which would give them all permissions on a database with their username.

the "ON ..." is in "DATABASE.TABLENAME".
*.* - gives access to all databases and all tables
bob.test - gives access to the "test" table in the "bob" database
bob.* - gives access to all tables in the "bob" database.
etc

Jason

[bionik]

Thanks for the clarification I totally understand the syntax now. =) The only wierd thing is that it works with the *.* but when I use the new line you posted with '{username}'.* it gives me the following error...

You have an error in your SQL syntax near ''testaccount1'.* TO 'testaccount1'@'localhost' IDENTIFIED BY 'password'' at line 1

My textfile has the following usernames

testaccount1
testaccount2
testaccount3
testaccount4
testaccount5

[jasonlambert]

Ah, my mistake

The correct query is:

$query = "GRANT ALL PRIVILEGES ON {$username}.* TO '{$username}'@'localhost' IDENTIFIED BY 'some_pass'";

Jsaon