EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Grant All Privileges

MySQL Grant All Privileges

MySQL Grant All Privileges

Introduction to MySQL Grant All Privileges

MySQL Grant All Privileges are the commands that allow the MySQL user accounts to manage and access the database with appropriate privileges. When we grant privileges to a MySQL User Account, it controls which operations the user can execute.

These privileges may be used differently at various levels of operation:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

  • Administrative Privileges are then applied globally to accomplish the operation of the MySQL server but are not specified to a particular MySQL database.
  • Database Privileges can be implemented either to a specific database or globally to all server databases and associated objects within a database.
  • Privileges can be allowed for MySQL database objects, including indexes, tables, views, and also stored routines, for all MySQL objects of a given type within a database or all MySQL database tables or even for all objects of a given type in all MySQL databases globally.

Syntax of MySQL Grant All Privileges

For using the MySQL Grant All Privileges by a user account for any specific MySQL database and executing the MySQL commands, we need to apply the following elementary syntax structure for granting permissions:

GRANT [ALL | Specific Privilege] [, privilege], ... ON PrivilegeLevel TO AccountName;

The above syntax is illustrated below:

Initially, we must specify single or multiple privileges after the keyword GRANT. Suppose you provide more privileges. Then, we need to distinguish each privilege using commas. For demonstration, let us grant SELECT privilege on a table named ‘Books’ in the demo database in the server to the MySQL user account [email protected]:

GRANT SELECT ON Books TO [email protected];

A MySQL user account includes two fragments: user name & host name, as shown above.

Similarly, the examples illustrated below will show you to grant INSERT, UPDATE, and DELETE like multiple privileges on the Books table to [email protected]:

GRANT INSERT, UPDATE, DELETE ON Books TO [email protected];

Again, secondly, we need to state the PrivilegeLevel that will determine the privilege level to which the grant privileges will be applied.

How to Grant All Privileges in MySQL?

MySQL Grant All Privileges are the administrative statements that grant rights to a user account to regulate and execute MySQL operations. When a new user creates a single or multiple user accounts using CREATE USER statement, the user does not get any privileges. This means that the MySQL user can log in to the server, but the MySQL user cannot perform anything like MySQL operations, including queries such as selecting a MySQL database, inserting data into the database table, and other data retrieval statements from the tables. Therefore, if we want to work with the MySQL database and its consisting objects, a user account will need rights or user account privileges to perform any managing or administrative operations on the server.

To grant all privileges for a user account, you need to use the MySQL “GRANT ALL PRIVILEGES” statements. These statements allow you to work with the database tables and other database objects, providing access at different privilege levels.

1. Global Privilege

This applies global privileges to all MySQL databases in the server denoted by the syntax: *.*

Example:

GRANT SELECT ON *.* TO [email protected];

Here, the user account [email protected] will query data records from all tables in all MySQL databases on the server.

2. Database Privilege

This provides database-level privileges to all the database objects. We need to use the syntax ON databasename.*

Example:

GRANT INSERT ON databasename.* TO [email protected];

3. Table Privilege

It assigns the table level privileges to all the table columns using the syntax ON databasename.tablename. If we do not add a databasename, MySQL implements the default database and may issue an error if it finds no default database.

Example:

GRANT DELETE ON databasename.tablename TO [email protected];

4. Column Privilege

It applies to a table column or columns for every privilege in the server.

Example:

GRANT SELECT (ColumnName1, COlumnName2,..) UPDATE (ColumnName1) ON TableName TO [email protected];

5. Stored Routine Privilege

This privilege level applies to the MySQL stored routine procedures and functions.

Example:

GRANT EXECUTE ON PROCEDURE Procedure_Name TO [email protected];

Here, specify the Procedure_Name present in the present database.

6. Proxy User Privilege

This privilege level for MySQL Grant All Privileges permits a single to be a proxy for other users. Here, the proxy user receives all privileges of the proxy user.

Example:

GRANT PROXY ON root TO [email protected];

Here, the [email protected] adopts all privileges of the MySQL root.

It should be noted that using the GRANT statement, a MySQL user must hold the privilege GRANT OPTION and the privilege to be granted. So, for example, if, in case, the system variable read-only is allowed, then the user require to possess the SUPER privilege to execute the GRANT statement.

Example of MySQL Grant All Privileges

Usually, we need to create a new user account using the statement CREATE USER, and then, we need to proceed further to grant all privileges to the user created using the GRANT statement.

Initially, we will create a user account in the MySQL server called [email protected] by the identical command to the CREATE TABLE statement:

Code:

CREATE USER [email protected] IDENTIFIED BY '[email protected]';

After this, next, we can view the initial privileges assigned to the user-created named [email protected] using the statement SHOW GRANTS:

Code:

SHOW GRANTS FOR [email protected];

Output:

MySQL Grant All Privileges OP 1

Here, the usage term denotes that the user can log in to the database account server but cannot have privileges to perform any operation actions.

Now, we will allow all privileges in all the MySQL databases in the present database server to [email protected] using the following query statements:

Code:

GRANT ALL ON empdb.* TO [email protected];

Again, let us display and view the privileges to the user account called [email protected] using the SHOW GRANT command:

Code:

SHOW GRANTS FOR [email protected];

Output:

MySQL Grant All Privileges 2

Conclusion

MySQL Grant All Privileges allows a MySQL user to give all privileges at a particular access level except GRANT OPTION in the MySQL server database. All privileges existing at a specific privilege level in the MySQL server grant all rights at a global or table level. The privilege specifiers for these privileges are abbreviated. Thus, using the MySQL Grant All Privileges, a user account can perform various MySQL queries at different privilege levels.

Recommended Articles

We hope that this EDUCBA information on “MySQL Grant All Privileges” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL COALESCE
  2. MySQL User Permissions
  3. mysql_real_escape_string
  4. MySQL SHOW
C++ PROGRAMMING Certification Course
38+ Hours of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ASP.NET Certification Course
149+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SQL - Everything in SQL
253+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SOFTWARE TESTING Certification Course
74+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Certification Course
 115+ Hours of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - 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

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
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*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?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more