1 Star2 Stars3 Stars4 Stars5 Stars (6 votes, average: 5.00 out of 5)

Grant privileges to a Database in MySqli

In order to Grant privileges to a Database in MySqli you will need to have your hosting login information. If you have not already done so please see the post Create a MySql Database in PHP with MySqli. In order to use this method you will be lost if you do not understand the basics that is written in that post.

Code to Create a User for a Database *

You will set that php variables to whatever the name of your database is to create the user. The first part of the script below will create a user for the database that you created. The second part of this script will grant all privileges to that user.

// 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();
}

Remember that you are creating a user for a script like WordPress, Joomla or some other script that you want to be able to access the database.  You usually want to give them all privileges so the script can do its work. If you have special requirements or want to set up the permissions differently then you will have to adjust the script to your needs.

Grant privileges to a Database in MySqli was last modified: May 5th, 2015 by Maximus Mccullough

Subscribe To My YouTube Channel:
Advanced Programming Made Easy

I hope you enjoyed the article. If I was able to help you please consider a tip for the content.

One Time Tip

more tips

Reoccuring Tips

Tips

Grant privileges to a Database in MySqli

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.