How can we grant privileges to a MySQL user?

MySQLMySQLi Database

We need to grant privileges to the new user which we have created because the new user would not have privileges. We can use GRANT statement to grant privileges to the user account. Its syntax would be as follows −

Syntax

GRANT privilege,[privilege],.. ON privilege_level
TO user [IDENTIFIED BY password]
[REQUIRE tsl_option]
[WITH [GRANT_OPTION | resource_option]];

Explanation of the above syntax is as follows −

First, specify one or more privileges after the GRANT keyword. If we grant the user multiple privileges, each privilege is separated by a comma.

Next, specify the privilege_level that determines the level at which the privileges apply. MySQL supports global ( *.*), database ( database.*), table ( database.table) and column levels. If we use column privilege level, we must specify one or a list of the comma-separated column after each privilege.

Then, place the user that we want to grant privileges. If the user already exists, the GRANTstatement modifies its privilege. Otherwise, the GRANT statement creates a new user. The optional clause IDENTIFIED BY allows us to set a new password for the user.

After that, we specify whether the user has to connect to the database server over a secure connection such as SSL, X059, etc.

Finally, the optional WITH GRANT OPTION clause allows us to grant other users or remove from other users the privileges that we possess. In addition, we can use the WITH clause to allocate MySQL database server’s resource e.g., to set how many connections or statements that the user can use per hour. This is very helpful in the shared environments such as MySQL shared hosting.

Example

In the example below we are creating a user and granting privileges to it −

mysql> use mysql
Database changed
mysql> create user abcd@localhost identified by 'password123';
Query OK, 0 rows affected (0.04 sec)

The query below will give us the privileges for the new user account abcd@localhost.

mysql> SHOW GRANTS FOR abcd@localhost;
+------------------------------------------+
| Grants for abcd@localhost                |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'abcd'@'localhost' |
+------------------------------------------+
1 row in set (0.01 sec)

Now, to grant all the privileges to the abcd@localhost user account, we can use the following statement −

mysql> GRANT ALL ON *.* TO 'abcd'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.05 sec)

Here, the ON *.* clause means all databases and all objects in the databases. The WITH GRANT OPTION allows abcd@localhost to grant privileges to other users.

Now if we will use the SHOW GRANTS statement again, we will see that the privileges of abcd@localhost have been updated.

mysql> SHOW GRANTS FOR abcd@localhost;
+---------------------------------------------------------------------+
| Grants for abcd@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'abcd'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
raja
Published on 20-Feb-2018 17:55:52
Advertisements