Find the answer to your Linux question:
Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 21
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 ...
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1
    Just Joined!
    Join Date
    Feb 2003
    Posts
    56

    MySQL accounts from a file


    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

  2. #2
    Linux Guru
    Join Date
    Apr 2003
    Location
    London, UK
    Posts
    3,284
    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

  3. #3
    Just Joined!
    Join Date
    Feb 2003
    Posts
    56

    PHP

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

  4. #4
    Linux Guru
    Join Date
    Apr 2003
    Location
    London, UK
    Posts
    3,284
    take a look at http://www.hotscripts.com/ you may find one on there.

    Jason

  5. #5
    Linux Guru
    Join Date
    Apr 2003
    Location
    London, UK
    Posts
    3,284
    Code:
    <?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

  6. #6
    Linux Guru sdousley's Avatar
    Join Date
    Feb 2004
    Posts
    1,790
    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
    "I am not an alcoholic, alcoholics go to meetings"
    Registered Linux user = #372327

  7. #7
    Just Joined!
    Join Date
    Feb 2003
    Posts
    56

    Script

    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);
    ?>

  8. #8
    Linux Guru
    Join Date
    Apr 2003
    Location
    London, UK
    Posts
    3,284

    Re: Script

    Quote Originally Posted by bionik
    <?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:
    Code:
    $query = "GRANT ALL PRIVILEGES ON '&#123;$username&#125;'.* TO '&#123;$username&#125;'@'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

  9. #9
    Just Joined!
    Join Date
    Feb 2003
    Posts
    56

    Variable

    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

  10. #10
    Linux Guru
    Join Date
    Apr 2003
    Location
    London, UK
    Posts
    3,284
    Ah, my mistake

    The correct query is:
    Code:
    $query = "GRANT ALL PRIVILEGES ON &#123;$username&#125;.* TO '&#123;$username&#125;'@'localhost' IDENTIFIED BY 'some_pass'";
    Jsaon

Page 1 of 3 1 2 3 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •