Grant privileges to a Database in MySqli

Grant Privileges to a Database in MySqli

MySqli is a PHP extension used to connect and communicate with MySQL databases. One of the key features of MySqli is the ability to grant privileges to users for accessing databases. This feature allows the database administrator to have better control over who can access and modify the data within a database. In this tutorial, we will go through the steps required to grant privileges to a database in MySqli.

Step 1: Log in to the MySQL database

Before we can grant privileges to a database, we need to log in to the MySQL database using MySqli. This is done using the mysqli_connect() function. Here’s an example:

<?php
$host = "localhost";
$user = "username";
$password = "password";
$database = "database_name";

// Create connection
$conn = mysqli_connect($host, $user, $password, $database);

// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

Replace “localhost”, “username”, “password”, and “database_name” with your own values.

Step 2: Grant privileges to a user

To grant privileges to a user, we use the mysqli_query() function to execute the GRANT statement. Here’s an example:

<?php
$host = "localhost";
$user = "username";
$password = "password";
$database = "database_name";

// Create connection
$conn = mysqli_connect($host, $user, $password, $database);

// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

// Grant privileges to user
mysqli_query($conn, "GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'user'@'localhost' IDENTIFIED BY 'password'");
?>

In this example, we grant SELECT, INSERT, UPDATE, and DELETE privileges to the user “user” on the database “database_name”. The user is identified by their password, and we specify that the user can only connect from “localhost”.

Step 3: Test the user’s privileges

To test the user’s privileges, we can use the mysqli_query() function to execute a query on the database. Here’s an example:

<?php
$host = "localhost";
$user = "username";
$password = "password";
$database = "database_name";

// Create connection
$conn = mysqli_connect($host, $user, $password, $database);

// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

// Query the database
$result = mysqli_query($conn, "SELECT * FROM table_name");

// Check if query was successful
if ($result) {
  // Output data
  while ($row = mysqli_fetch_assoc($result)) {
    echo $row["column_name"];
  }
} else {
  echo "Error: " . mysqli_error($conn);
}

// Close connection
mysqli_close($conn);
?>

In this example, we execute a SELECT query on the table “table_name”. If the query is successful, we output the contents of the “column_name” column. If there’s an error, we output the error message.

More on Granting Privileges

Granting privileges to a database in MySQL is a straightforward process. By following the steps outlined in this tutorial, you can give users the appropriate level of access to your database, ensuring that your data remains secure and organized.

In order to Grant privileges to a Database in MySQL 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.