Updated June 1, 2023
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:
- 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 admin@localhost:
GRANT SELECT ON Books TO admin@localhost;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 admin@localhost:
GRANT INSERT, UPDATE, DELETE ON Books TO admin@localhost;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 myadmin@localhost;Here, the user account myadmin@localhost 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 myadmin@localhost;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 myadmin@locolhost;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 myadmin@localhost;5. Stored Routine Privilege
This privilege level applies to the MySQL stored routine procedures and functions.
Example:
GRANT EXECUTE ON PROCEDURE Procedure_Name TO myadmin@localhost;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 myadmin@localhost;Here, the myadmin@localhost 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 myadmin@localhost by the identical command to the CREATE TABLE statement:
Code:
CREATE USER myadmin@localhost IDENTIFIED BY 'Adminpass@123';After this, next, we can view the initial privileges assigned to the user-created named myadmin@localhost using the statement SHOW GRANTS:
Code:
SHOW GRANTS FOR myadmin@localhost;Output:
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 myadmin@localhost using the following query statements:
Code:
GRANT ALL ON empdb.* TO myadmin@localhost;Again, let us display and view the privileges to the user account called myadmin@localhost using the SHOW GRANT command:
Code:
SHOW GRANTS FOR myadmin@localhost;Output:
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.
