SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2010
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL statements in PHP for CREATE USER and GRANT privileges

    I copied sql code from phpmyadmin for successfully creating a user and granting privileges and have been trying to adapt it in php. I'm automating database creation for new clients and would like to incorporate new user and privileges at the same time. I keep getting errors with the changes I've made so far. I'm using variables too. And I'm not quite certain what the (.) that appear near (*) mean in SQL. So it's a bit tricky for me. Here's what I'm working with...

    PHP Code:
            // create user
                
    $sql "CREATE USER " $dbName "'_user'@'localhost' IDENTIFIED BY " $privilege_passwd;
                
    $result mysqli_query($link$sql);        
                if (!
    $result)
                {
                    
    $error 'Error in creating new user.';
                    
    error_log($error);
                    exit();
                }
            
    // grant privileges
                
    $sql "GRANT SELECT , INSERT , UPDATE ,
                DELETE ON * . * TO 'generic_user'@'localhost' IDENTIFIED BY '" 
    $privilege_passwd "' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0";
                
    $result mysqli_query($link$sql);        
                if (!
    $result)
                {
                    
    $error 'Error in granting privileges to new user.';
                    
    error_log($error);
                    exit();
                }
            
    // grant additional
                
    $sql "GRANT ALL PRIVILEGES ON '" $dbName "' . * TO '" $dbName "_user'@'localhost'";
                
    $result mysqli_query($link$sql);        
                if (!
    $result)
                {
                    
    $error 'Error in granting privileges to new user.';
                    
    error_log($error);
                    exit();
                } 
    Thoughts? If there's a less verbose way of doing this as well, that would be fine too. Like I said, I just copied the successful code snippet that phpmyadmin produces after it's done executing.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ryan77 View Post
    And I'm not quite certain what the (.) that appear near (*) mean in SQL.
    it's explained in da mysql manual under the GRANT statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2010
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, *.* references the positions of database.table and (*) are for all. Thanks.

    Running the code in php, I can get the CREATE USER statement to work but not GRANT. I've run them as separate queries and combined. These are snippets of the separate example:

    PHP Code:
    $sql "CREATE USER '" $dbName "'@'localhost' IDENTIFIED BY '" $privilege_passwd "'";
    $result mysqli_query($link$sql);
    $sql "GRANT SELECT , INSERT , UPDATE , DELETE ON " $dbName " . * TO '" $dbName "'@'localhost' IDENTIFIED BY '" $privilege_passwd "'";
    $result mysqli_query($link$sql); 
    Is there anything wrong with second statement? I can't get the privileges to take effect.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ryan77 View Post
    Is there anything wrong with second statement?
    echo $sql just before you execute it, then copy/paste it directly into mysql and see if you get an error message
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jun 2010
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I did what you said. The echoed values run fine in mysql and set the privileges. Now I have to figure out what's going on between php and mysql that's making my query $result null (or whatever the proper term is). Thanks for your help. I'm getting closer.

  6. #6
    SitePoint Enthusiast
    Join Date
    Jun 2010
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937...I got it working. The addClient_user I reference in the php code to set the new client's privileges had only the privileges necessary to get the job done...so I thought; for example, CREATE, INSERT and GRANT at the global level. I was missing the CREATE USER admin privilege. But the code creating the user would work though.

    So I changed the privileges of the addClient_user to all and the php code now executes beautifully. Thoughts? Just glad it's working. Thanks for the troubleshooting help.


Tags for this Thread

Bookmarks

Posting Permissions

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