Grant Privileges MySQL

Introduction to Grant Privileges MySQL

Whenever a new user is created in the MySQL database using the CREATE USER statement, the created user doesn’t have any privileges that mean the permissions to access and modify the contents of the database. This means that the user will be able to log in to the database server but cannot retrieve the data from the database tables neither use the database and CREATE, ALTER or MODIFY the tables.

The newly created user cannot use SELECT, INSERT, UPDATE, or DELETE statements to fetch and modify the contents of the table. For this, the privileges must be granted to the user by some other user, such as root or another user that has the privilege to grant these privileges and is already granted with the privileges that it wants to grant to another user. In this article, we will learn who can grant the privileges to other users, how this can be done, syntax, and examples of granting the privileges in MySQL.

Working of Granting and Revoking Privileges

When MySQL is installed on a machine, by default, a user named root is created that has all the privileges of the database server. We can check the privileges granted to any user in MySQL by using the SHOW GRANTS command. For example, if we want to check the privileges that are granted to the root user, then we can make the use of the following query statement to fetch the information –

Query:

SHOW GRANTS FOR root@localhost;

Output:

Explanation: The root user has all the privileges on all the databases, its tables and columns of the tables, can perform any of the operations on them, and can grant the privileges to other users. It is actually a proxy user of the @ user.

Syntax:

The syntax of the GRANT command that is used for assigning the privileges to the user to allow the access and operate on the database and its entities is as follows –

Query:

GRANT specified_priv [,specified_priv],..
ON specified_priv_level
TO user_name;

where,

• specified_priv: It is the name of the privilege that is an operation that you want to permit and grant to the user named user_name. We can grant multiple privileges to the single user on a particular entity by using the single grant command by specifying the privileges in a comma-separated manner after the GRANT keyword.
• specified_priv_level: The privilege level is the entity on which you want to assign the privilege to the user. There can be six different privilege levels on which we can grant the permission or privilege to the user.
• user_name: It is the name of the user to which you want to grant the privileges using the GRANT command.

Privilege levels  in MySQL

There are six privilege levels used for granting privileges to the user: global, database, table, column, stored procedure or function, and proxy, as shown in the below image.

Examples of Grant Privileges MySQL

Let us create a new user named grant demo using the following create user statement, which we can use to grant privileges to-

Query:

CREATE USER 'grantdemo'@'localhost' IDENTIFIED BY '123';

Output:

Let us check all the granted privileges for this user using the following query statement –

Query:

SHOW GRANTS FOR grantdemo@localhost;

Output:

We can see from the output that the newly created user does not have any grants assigned to it. It can just log in to the database but not access the contents and can neither modify them. Using the GRANT statement, let us grant the select privilege on all the educba_writers tables of the educba database. This can be done by using the following query statement –

Query:

GRANT SELECT
ON educba.educba_writers
TO grantdemo@localhost;

Output:

We can even assign multiple privileges to the grantdemo user using the GRANT statement. For example, let us assign UPDATE, DELETE and INSERT privileges to the grantdemo user on the table educba_writers located in the educba database. For this, we will make the use of the following query statement where the privileges to be granted are mentioned in comm-separated format –

Query:

GRANT UPDATE, DELETE, INSERT
ON educba.educba_writers
TO grantdemo@localhost;

Output:

How Privileges can be Assigned on Different Levels?

Let us now see, one by one, how privileges on different levels can be assigned.

1. Global Level

We can grant certain privileges to the user that has the privilege level that is scope globally on all the tables of all the databases. This is called granting privileges at the global level. Suppose we want to grant SELECT privilege on all the tables of all the databases to the grantdemo user. This can be done by using the following query –

Query:

GRANT  SELECT
ON *.*
TO grantdemo@localhost;

Output:

*.* is used to specify the global privilege level.

2. Database Level

When we want to grant the privileges to the user on all the tables of the ceratin database, then we can use the database privilege level. We need to specify name_of_database.* to mention the database level of privilege. We can grant INSERT privilege on all the tables of the educba database to grantdemo user by using the following query –

Query:

GRANT INSERT
ON educba.*
TO grantdemo@localhost;

Output:

3. Table Privilege Level

When we want to restrict the granting of privilege on a particular table of the database, then table-level privileges are granted. For example, if we want to give DELETE privilege to grantdemo user on just educba_writers table, then we can use the following query –

Query:

GRANT DELETE
ON educba.educba_writers
TO grantdemo@localhost;

Output:

4. Column, Stored Routine and Proxy Level Privilege Granting

If we want ceratin privilege to be restricted for granted to a certain user, then we can make the use of column privilege level in the GRANT statement by mentioning the list of columns in the comma-separated format. Besides this, if we want to grant privileges on stored procedures, routines, and functions, then the same can be done by using ON PROCEDURE or ON FUNCTION statements in GRANT queries.

We can even grant all the privileges assigned to a particular user to some other user. This is called the proxy level privilege granting, and the user to which the privileges are being granted is called the proxy of the original user whose privileges are being granted.

Conclusion

We can grant the privileges to the user once it is created on various levels of privileges and assign different privilege operations that we want to allow for that user using the GRANT statement in MySQL.

Recommended Articles

This is a guide to Grant Privileges MySQL. Here we discuss the Introduction of Grant Privileges MySQL and the practical examples and different subquery expressions. You can also go through our suggested articles to learn more –

1. Introduction to MySQL Operators
2. Top 23 MySQL String functions
3. MySQL vs SQLite | Top 14 Comparisons
4. Guide to MySQL Timestamp