MySQL accounts from a file

[bionik]

I actually tried that before too but am getting the following error...

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

been searching around to try to get passed this error but no luck

[jasonlambert]

Run this script, and post the output you get please.

Also, which version of MySQL are you using?

<?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 &#123;$username&#125;.* TO '&#123;$username&#125;'@'localhost' IDENTIFIED BY 'some_pass'";

echo $query . "\n";

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

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

Ta,

Jason

[bionik]

GRANT ALL PRIVILEGES ON testaccount1.* TO 'testaccount1'@'localhost' IDENTIFIED BY 'some_pass' GRANT ALL PRIVILEGES ON testaccount2.* TO 'testaccount2'@'localhost' IDENTIFIED BY 'some_pass' GRANT ALL PRIVILEGES ON testaccount3.* TO 'testaccount3'@'localhost' IDENTIFIED BY 'some_pass' GRANT ALL PRIVILEGES ON testaccount4.* TO 'testaccount4'@'localhost' IDENTIFIED BY 'some_pass' GRANT ALL PRIVILEGES ON testaccount5.* TO 'testaccount5'@'localhost' IDENTIFIED BY 'some_pass' GRANT ALL PRIVILEGES ON .* TO ''@'localhost' IDENTIFIED BY 'some_pass' You have an error in your SQL syntax near '* TO ''@'localhost' IDENTIFIED BY 'some_pass'' at line 1

Running version 3.23.58-1.0

[jasonlambert]

I would guess you have a blank line at the end of your file that is causing you this problem.

This version of the script should take care of it for you:

<?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;
    //skip username if it is less that 3 chars long...
    if&#40; strlen&#40;$username&#41; < 3 &#41;
           continue;

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

     echo $query . "\n";

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

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

Jason

[bionik]

Worked perfectly thanks a lot you guys rock!!

[bionik]

I checked the permissions with that last script that runs without errors but the permissions say None. but with the *.* the permissions get assigned ok. Thanks

[jasonlambert]

Are you connecting to the database as the right user, and from localhost?

eg:

# mysql -h localhost -u username1 -psome_pass username1

Jason

[bionik]

No im not logging in as the user, im using Webmin to check the rights that are assigned to the user. Thanks

[jasonlambert]

unless you log in as that user, or you log in as root, you wont be able to access the database.

[bionik]

I logged in as root using Webmin to see what permissions are assigned to the accounts that the script created but under the description it says none. But when I run the original script that had *.* the permissions show under the description in webmin?