EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial Grant Privileges MySQL
 

Grant Privileges MySQL

Payal Udhani
Article byPayal Udhani
EDUCBA
Reviewed byRavi Rathore

Updated May 24, 2023

Grant Privileges MySQL

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

Grant Privileges MySQL Example 1

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.

Privilege levels  in MySQL

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:

Grant Privileges MySQL Example 3

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

Query:

SHOW GRANTS FOR grantdemo@localhost;

Output:

Grant Privileges MySQL Example 4

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:

Grant Privileges MySQL Example 5

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:

Grant Privileges MySQL Example 6

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:

Global Level Example 7

*.* 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:

Database Level Example 8

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:

Table privilege level Example 9

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.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW