Updated May 24, 2023
Introduction to Grant Privileges MySQL
When we create a new user in the MySQL database using the CREATE USER statement, the user does not have any privileges initially. This means they do not have permissions to access or 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. To accomplish this, another user, such as root or a user with the privilege to grant these privileges, must grant the privileges to the user. The user granting the privileges must already possess the privileges they wish 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 on Granting and Revoking Privileges
During the installation of MySQL on a machine, the system creates a user named “root” by default with 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 granted to the root user, we can use the following query statement to retrieve 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. You can accomplish this 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
Table-level privileges are granted when we want to restrict the granting of privilege on a particular table of the database.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
To restrict certain privileges for a specific user, we can utilize the column privilege level in the GRANT statement by specifying the list of columns in a 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. We refer to this as proxy-level privilege granting, and we designate the user receiving the privileges as the proxy of the original user whose privileges are being granted.
Conclusion
Once we create a user in MySQL, we can grant privileges to the user at various levels and assign different privilege operations using the GRANT statement.
Recommended Articles
We hope that this EDUCBA information on “Grant Privileges MySQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
