MySQL Create User and Grant Privileges For PHP Code

Introduction

Many companies uses various types of databases. One primary thing common in the context of databases is security. Since when we talk about a company's databases, it often has many confidential records and those records are not accessible for all employees. So in this article I will explain how user accounts work for MySQL databases. One fundamental thing that comes to mind is how to manage the database security, but before you learn the details of managing database security, you should have a general idea of how user accounts work.

In this article I am taking an example to more briefly describe my point of view. In this example I write a simple script to create two users and grant them privileges.

Create a user that have all privileges 

You can simply write a query as in the following:

create user sharad@localhost identified by 'password@123'; for creating user.

The following query image also shows how to write the simple create user query in a MySQL cmd and in this query image the script starts with a "CREATE USER" statement, that creates the user with the name of "sharad" and that user only connects from localhost with password of "password@123".



Now I am going to write the grant privileges script for this user.

For granting all privileges for this (sharad) user

After the user has been created, the "GRANT" statement sets up the privileges for each user. Here the user "sharad" is granted all privileges on the MySQL database. To do that, you can simply write the following query:

grant all on mysql.* to sharad@localhost;

Now let's try to create another user with limited privileges.

Create an User

 

Grant limited privileges to this( mohan) user

The user "mohan" can only use "SELECT" and "INSERT" statements in the MySQL database.

For seeing the privileges of a user, you can use the "SHOW GRANTS" statement.

sharad user privileges

mohan user privileges

Now let's see what will be happen if we try to use an "update" command with either the mohan or sharad account.

When using the "sharad" account

Output before the following update query is applied:



<?php
$con = mysql_connect("localhost","sharad","password@123");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("mysql", $con);
mysql_query("UPDATE employee SET id=108 WHERE name='ravi'");
mysql_close($con);
?>

Output after the following execution:

The code below is executed succefully, because we use  $con = mysql_connect("localhost","sharad","password@123");  and this user has all privileges.

When working with the "mohan" account

Now if you try the code above with $con = mysql_connect("localhost","mohan","password@123"); then this code will not work, because the mohan user only has "SELECT" and "INSERT" statement authority.