SQL statements in PHP for CREATE USER and GRANT privileges

PHP
1

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…


		// 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

it’s explained in da mysql manual under the GRANT statement

3

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:


$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

echo $sql just before you execute it, then copy/paste it directly into mysql and see if you get an error message

5

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

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.