How to Grant and Revoke

Privileges in MySQL
When it comes to keeping databases and data secure, it is recommended to use privileges. They
can help you control who can access database data and do not allow users without specified
permissions to view, modify, or delete database objects or data.

In the article, we'll explore how to grant and revoke privileges on any level using
the GRANT and REVOKE commands and dbForge Studio for MySQL, a powerful GUI tool for
database development, management, testing, and deployment.

Role of privileges in MySQL database

security
To get started, let us identify what a privilege is. In MySQL, privileges are rights or permissions
assigned to users, which decide whether they are allowed to perform different operations within
the MySQL database. These operations may include accessing databases, executing queries,
modifying data, creating or deleting tables, and managing other users' privileges. Therefore, if a
user has the privilege to execute a specific command, MySQL executes the statement. Otherwise,
an error will occur.

You can assign a privilege to the user account using the GRANT statement.

GRANT privilege

ON privilege_level TO account_name;

where:

 privilege is an operation that the user account will be allowed to perform, such as SELECT,
INSERT, DROP, etc. If you want to grant multiple privileges, separate them by commas.
 privilege_level is the level of access, such as global, database, or database object, to which
the privileges can be applied.
 account_name is the username to which the privileges will be granted.

MySQL supports the following types of privileges to access and control the database:

Global
They can be used to manage all databases located on the current MySQL server and control
administrative tasks, such as creating or deleting databases, managing users, and setting server
parameters. To assign the global privilege, use the following syntax:

GRANT privilege

ON *.* TO username;

Database

They give permissions to manage all objects from the specified database and determine what
operations users can perform within those databases. To assign the database-level privilege, use
the following syntax:

GRANT privilege

ON database_name.* TO username;

Table

They provide users with the right to manage rows from the specified database table and
determine what operations users can perform on those tables. To assign the table-level privilege,
use the following syntax:

GRANT privilege

ON database_name.table_name TO username;

Column

They allow users to perform the defined operations, for example, SELECT or DELETE, for the
specified columns of the database table. Note that the column must be specified for each
privilege. To assign the column-level privilege, use the following syntax:
GRANT privilege (column1), privilege (column2)

ON table_name TO username;

Stored routine

They control access to stored procedures and functions within the database, including the ability
to execute, alter, or drop them. To assign the stored routine privilege, use the following syntax:

GRANT privilege

ON PROCEDURE procedure_name TO username;

Proxy user

They allow one user to be a proxy for another. The proxy user receives all the privileges of the
proxied user. To assign the proxy user privilege, use the following syntax:

GRANT PROXY

ON proxied_user TO proxy_user;

So, managing privileges ensures that data access is restricted to authorized users, helps
organizations protect their sensitive information, and maintains compliance with regulatory
requirements.

Show user privileges in MySQL

In MySQL, you can view user privileges using the SHOW GRANTS command. The command
displays the privileges given to the user currently connected to the database.

SHOW GRANTS;
For example, you can execute the SHOW GRANTS command to list all global privileges for the
user - root on the dbfmylast server.

You can also see the privileges granted to the specific user account from which the user can be
connected. In such cases, if the current server and the hostname of the user whose permissions
you want to check differ, it is recommended to specify the hostname or IP address in the
command. To show the privileges, execute the following command:

SHOW GRANTS FOR 'username'@'hostname';

Let us consider the following example. The command SHOW GRANTS FOR
'root'@'localhost'; displays the privileges granted to the user account root when connecting from
the hostname 'localhost'. These privileges may include permissions to perform different actions,
such as accessing specific databases, executing certain SQL queries (for example, SELECT,
INSERT, UPDATE, DELETE, etc.), creating or dropping databases and tables, etc.
In the command, replace the following parameters with your actual data:

 username is the username of the user whose privileges you want to view.
 hostname is the hostname or IP address from which the user is connecting.

Show privileges for all users on a MySQL

database
Now, we can view the permissions granted to all users connected to a MySQL database. One of
the ways to do this is to query the following SELECT statement:

SELECT * FROM mysql.user;

This query retrieves information about all user accounts stored in the mysql.user system table,
including their privileges. The result will include columns, such as Host, User, and various
privileges columns like Select_priv, Insert_priv, Update_priv, etc.

However, it should be noted that you need to have administrative privileges or at least the
SELECT privilege on the mysql system database.

Another way to show privileges granted to all users that exist in a MySQL database is to
query user_privileges in information_schema.
SELECT * FROM information_schema.user_privileges;

where information_schema.user_privileges is a system view that provides information about user

privileges. It contains details about the privileges granted to users, including the user account and
the host from which the user is connecting.

When you execute this command, it will return a result set containing the user account to whom
the permissions were granted, a host, a privilege type, and information about whether the user
has the privilege to grant the same privilege to other users. In the latter, if
the IS_GRANTABLE column has the YES value, the user has the privilege to assign the same
privilege to other users. In other words, the user has the right to manage access permissions for
other users. If the value is NO, the user does not have permission to assign the same privilege to
others.
Grant all privileges in MySQL
Now, we'll go through some examples of how to grant privileges to users in MySQL. For
instance, you need to provide a specific user with full access and all permissions over all
databases, so only this person can have complete ownership to manage databases, tables, access,
functions, etc. You can achieve this using the GRANT ALL PRIVILEGES statement:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'hostname';

Executing this statement will grant all privileges on all databases and tables to the specified user
at the destination host. However, it does not automatically grant the WITH GRANT OPTION or
PROXY privileges.

 WITH GRANT OPTION allows a user to share or remove permissions from other users.
When you give someone permission using the WITH GRANT OPTION clause, they can
share those privileges with other users. But if you don't include WITH GRANT OPTION,
users can't transfer those permissions further, even if they have been given all permissions.
 PROXY allows a user to connect as another user without providing the password for that
user. This privilege is not granted by default with ALL PRIVILEGES. It should be assigned
explicitly if required.

So, to assign the WITH GRANT OPTION or PROXY privileges along with ALL PRIVILEGES,
you would need to include them explicitly in the GRANT statement as follows:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'hostname' WITH GRANT OPTION;

If you want to give all privileges over the specified database, modify the statement as follows:

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'hostname';

You can also provide full ownership over one table from the database using the following
statement:

GRANT ALL PRIVILEGES ON database_name.table_name TO 'username'@'hostname';

For example, assign all privileges over the sakila database to the user - jordansanders:

GRANT ALL PRIVILEGES ON sakila.* TO 'jordansanders';

Then, verify that the permissions were granted running the SHOW GRANTS FOR statement:
SHOW GRANTS FOR jordansanders;

The results grid displays all the permissions available for the user jordansanders on
the sakila database:

How to grant read-only privileges

There might be cases when you want users only to view data from the tables without the ability
to modify or delete it. You can do this by giving the SELECT privilege. So, to grant read-only
access, execute the following statement:

GRANT SELECT ON database_name.* TO 'username'@'hostname';

Don't forget to replace the following parameters with your actual data:
  database_name is the name of the database to which you want to grant read-only access. If
you want to do this for all databases, use *.*.
 username is the username of the account.
 hostname is the hostname or IP address from which the user will connect.

For example, provide the user jordansanders with read-only access to

the adventureworks database:

GRANT SELECT ON adventureworks.* TO 'jordansanders';

Then, check whether the privileges have been assigned:

SHOW GRANTS FOR jordansanders;

In the output, you'll see the permissions the user has, including the read-only access privilege.
Grant privileges using a GUI tool
We have demonstrated how you can give permissions using the GRANT statement. Now, we
would like to show the easiest method to assign permissions using Security Manager available
in dbForge Studio For MySQL.

Security Manager is an advanced administration tool that allows you to create, update, or delete
logins, users, and roles, manipulate user accounts, assign or modify roles visually, grant object
permissions, and revoke user privileges and access rights.

Security Manager allows global and object privileges to be granted. Let us now see how to grant
permissions on the database object. Begin by opening the Studio.

1. On the ribbon, select Database > Security Manager. This will open the Security Manager.
2. Select the user you want to grant permissions to and go to the Object Privileges tab.
3. In the Objects tree, select the database object for which you want to set permissions.
4. In the Available Privileges pane, a list of privileges you can assign is displayed - select the
checkboxes next to the required privileges.

Select the checkboxes next to the required privileges.

 5. On the toolbar, click Save to apply the changes.

Note that the grid under the Objects tree displays the current privileges of the selected user
(account).

How to revoke all privileges in MySQL

We have already looked at how to grant privileges to the user, and now it is time to review how
to revoke them.

In MySQL, the REVOKE command can be used to remove privileges from a user account. The
syntax of the command is as follows:

REVOKE ALL PRIVILEGES ON *.* FROM 'user_name'@'host_name';

where:

 *.* means that the privileges will be removed from any database and any database object.
 user_name is the user account from which you want to remove privileges.
 hostname is the hostname or IP address from which the specified user is connecting.

After executing this command, the user will no longer have any privileges within the MySQL
server. However, it is important to note that this command does not remove the user account
itself; it only deletes the privileges associated with the user account.

Let us revoke all privileges for the user jordansanders we granted before, namely, the read-only
access to the adventureworks database and all privileges to the sakila database. To do this,
execute the following query:

REVOKE ALL PRIVILEGES ON *.* FROM 'jordansanders';

Then, run the SHOW GRANTS FOR query to verify that the privileges have been removed:
As you can see, the previously assigned privileges have been revoked. Although the GRANT
USAGE ON *.* TO 'jordansanders'@'%' is displayed, it only grants the user
account jordansanders the permission to connect to the MySQL server from any host ('%'); it
does not give any specific privileges on any databases or tables. In other words, it allows the user
to establish a connection to the MySQL server without any additional permissions.

Revoke database-level privileges

MySQL supports revoking privileges at the database level using the REVOKE command. So, to
remove privileges from a specific database, specify the database name in the REVOKE
command as follows:

REVOKE privileges ON database_name.* FROM 'user_name'@'host_name';

where:
  privileges specifies the privileges you want to revoke from the user. It could be a single
privilege or a comma-separated list of multiple privileges - for example, SELECT,
INSERT, UPDATE, DELETE, etc.
 database_name specifies the database from which you want to revoke privileges.
 user_name is the user account from which you want to remove privileges.
 hostname is the hostname or IP address from which the specified user is connecting.

Let us take the user manager as an example and view the privileges it has been assigned.

As you can see, the user has been granted all privileges on the adventureworks database and the
SELECT, INSERT, and UPDATE privileges on the customer table of the sakila database.

Now, execute the command to remove a privilege from the adventureworks database:

REVOKE ALL PRIVILEGES ON adventureworks.* FROM 'manager';

Then, check the user privileges after revoking to ensure that they do not have access to the
specified database:

Revoke object-level privileges

Now, we'll remove the privilege from a database object, such as a table. To do this, specify the
database and table names in the REVOKE command:

REVOKE privileges ON database_name.object_name FROM 'user_name'@'host_name';

Finally, remove the SELECT, INSERT, and UPDATE privileges for the customer table in
the sakila database for the user manager.

REVOKE SELECT, INSERT, UPDATE ON sakila.customer FROM 'manager';

Upon executing the SHOW GRANTS FOR query, we can verify that the mentioned privileges
have been removed.

Revoke privileges using a GUI tool

It is worth noting that revoking all privileges in dbForge Studio is much easier than using the
commands mentioned above. All you need to do is to open Security Manager, perform several
clicks, and save the changes. For example, remove all privileges from the user manager. First,
execute the SHOW GRANTS FOR query to see the privileges assigned to the user.
To proceed, navigate to Security Manager and select manager from the user list. Then, switch
to the Global Privileges tab and clear the checkboxes next to the previously granted privileges.
Click Save to apply the changes.
Advantages of granting and revoking
privileges using dbForge Studio for MySQL
We would like to note that the Security Manager tool available in dbForge Studio for MySQL
can be more beneficial to use over manually writing the GRANT and REVOKE commands:

 Take advantage of the user-friendly GUI for managing privileges, which can be helpful for
those users who are not familiar with SQL syntax or who prefer a visual approach to
privilege management.
 Minimize the number of errors when granting and revoking privileges in Security Manager
instead of typing SQL commands manually.
 Improve accuracy and efficiency in privilege management tasks by visually selecting
specific databases, tables, or columns when granting or revoking privileges.
 Manage privileges using role-based access control, which allows administrators to define
roles with specific sets of privileges and assign users to these roles.
 Automate routine MySQL and MariaDB admin tasks.

Conclusion
In this guide, we examined how to regulate the level of access granted to user accounts across
databases or database objects. These privileges can be assigned globally, to databases, or to
specific database objects. We have also discussed the GRANT command as a way to assign user
account privileges, thus enhancing their access levels, and the REVOKE command, which
illustrates how to revoke privileges granted to accounts. In addition to using these commands, we
demonstrated the easiest way to grant and revoke permissions - dbForge Studio for MySQL, an
ultimate IDE for MySQL and MariaDB database administration and management. This GUI tool
can offer a more user-friendly, efficient, and integrated approach to privilege management. So,
try the Studio and evaluate its cutting-edge features and capabilities within a free 30-day trial
period.

How to grant and revoke rights to tables

using phpMyAdmin
Ask Question

Asked 11 years, 4 months ago

Modified 6 years, 6 months ago
Viewed 50k times
6
Is phpMyAdmin capable of granting rights (permissions) on certain tables to
users or roles?

When I look at the page of a database table there is no "rights" tab.

I have only found a place where I can give rights to single users.

What I need is granting some rights to several users at the same time like

grant insert, update, delete on customers to john, max, annie;

 mysql
 permissions

Share
Edit
Follow
Get updates on questions and answers
edited May 4, 2015 at 20:40

3 Answers
Sorted by:
Highest score (default)
9
You can do that. Follow the pictures: In the "Privileges" tab which list your users
click the "Edit Privileges" of the

user.

Then select the

database:
After that specific the privileges by checking them and then select your
table as shown in the
picture:
 After you select your table, you can define the operations in
detailed:

Share
Edit
Follow
answered Jan 6, 2014 at 16:29

shgnInc
1,99644 gold badges2222 silver badges2929 bronze badges
 Yes, this gives rights to a single user, but not to several users as mentioned
in my question. I don't want to do this over and over again for every single
user I want to give rights to. It would be ok if MySQL understood the concept
of roles, then I could give a role to a bunch of users and then grant the rights
to that role. Unfortunately, MySQL lacks this important feature. So I will to it
rather using the command line.
– Holger Jakobs
CommentedJan 7, 2014 at 10:30
 1
Depending on your host, this might not be an option in your phpMyAdmin
interface either.
– artomason
CommentedMar 28, 2019 at 21:20
 I don't see an option for table.
– khatchad
CommentedFeb 14, 2021 at 0:12
Add a comment
1
phpMyAdmin can't do such a granular rights setup, but you can do this with pure
SQL. See this reference for details. It's basically just writing "GRANT" statements
yourself which you can pass to phpMyAdmin.
The GRANT syntax allows you to specify multiple users in a single query.:
GRANT SELECT,INSERT,UPDATE,DELETE ON customers.* TO 'john', 'annie', 'max';
With PHP my admin, anything you do shows you the query that it ran to do it at
the top of the page. Copy these querys. Switch out the username and and host
and rerun them.

Not GUI but if doing several users then you just do it through the GUI once and a
quick copy paste for each user.

follow @shgnInc answer for the GUI part.

8.2.8 Adding Accounts, Assigning Privileges, and Dropping

Accounts
To manage MySQL accounts, use the SQL statements intended for that purpose:

 CREATE USER and DROP USER create and remove accounts.

 GRANT and REVOKE assign privileges to and revoke privileges from accounts.
 SHOW GRANTS displays account privilege assignments.
Account-management statements cause the server to make appropriate modifications to the
underlying grant tables, which are discussed in Section 8.2.3, “Grant Tables”.
Note
Direct modification of grant tables using statements such as INSERT, UPDATE, or DELETE is
discouraged and done at your own risk. The server is free to ignore rows that become malformed as
a result of such modifications.
For any operation that modifies a grant table, the server checks whether the table has the expected
structure and produces an error if not. To update the tables to the expected structure, perform the
MySQL upgrade procedure. See Chapter 3, Upgrading MySQL.

Another option for creating accounts is to use the GUI tool MySQL Workbench. Also, several third-
party programs offer capabilities for MySQL account administration. phpMyAdmin is one such
program.
This section discusses the following topics:

 Creating Accounts and Granting Privileges

 Checking Account Privileges and Properties
 Revoking Account Privileges
 Dropping Accounts
For additional information about the statements discussed here, see Section 15.7.1, “Account
Management Statements”.
Creating Accounts and Granting Privileges
The following examples show how to use the mysql client program to set up new accounts. These
examples assume that the MySQL root account has the CREATE USER privilege and all privileges
that it grants to other accounts.
At the command line, connect to the server as the MySQL root user, supplying the appropriate
password at the password prompt:
$> mysql -u root -p
Enter password: (enter root password here)
After connecting to the server, you can add new accounts. The following example uses CREATE
USER and GRANT statements to set up four accounts (where you see 'password', substitute an
appropriate password):
CREATE USER 'finley'@'localhost'
IDENTIFIED BY 'password';
GRANT ALL
ON *.*
TO 'finley'@'localhost'
WITH GRANT OPTION;

CREATE USER 'finley'@'%.example.com'

IDENTIFIED BY 'password';
GRANT ALL
ON *.*
TO 'finley'@'%.example.com'
WITH GRANT OPTION;

CREATE USER 'admin'@'localhost'

IDENTIFIED BY 'password';
GRANT RELOAD,PROCESS
ON *.*
TO 'admin'@'localhost';

CREATE USER 'dummy'@'localhost';

The accounts created by those statements have the following properties:

 Two accounts have a user name of finley. Both are superuser accounts with full global
privileges to do anything. The 'finley'@'localhost' account can be used only when
connecting from the local host. The 'finley'@'%.example.com' account uses
the '%' wildcard in the host part, so it can be used to connect from any host in
the example.com domain.
The 'finley'@'localhost' account is necessary if there is an anonymous-user account
for localhost. Without the 'finley'@'localhost' account, that anonymous-user account
takes precedence when finley connects from the local host and finley is treated as an
anonymous user. The reason for this is that the anonymous-user account has a more
specific Host column value than the 'finley'@'%' account and thus comes earlier in
the user table sort order. (For information about user table sorting, see Section 8.2.6, “Access
Control, Stage 1: Connection Verification”.)
 The 'admin'@'localhost' account can be used only by admin to connect from the local
host. It is granted the global RELOAD and PROCESS administrative privileges. These privileges
enable the admin user to execute the mysqladmin reload, mysqladmin refresh,
and mysqladmin flush-xxx commands, as well as mysqladmin processlist . No privileges
are granted for accessing any databases. You could add such privileges
using GRANT statements.
 The 'dummy'@'localhost' account has no password (which is insecure and not
recommended). This account can be used only to connect from the local host. No privileges are
granted. It is assumed that you grant specific privileges to the account using GRANT statements.
The previous example grants privileges at the global level. The next example creates three accounts
and grants them access at lower levels; that is, to specific databases or objects within databases.
Each account has a user name of custom, but the host name parts differ:
CREATE USER 'custom'@'localhost'
IDENTIFIED BY 'password';
GRANT ALL
ON bankaccount.*
TO 'custom'@'localhost';

CREATE USER 'custom'@'host47.example.com'

IDENTIFIED BY 'password';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON expenses.*
TO 'custom'@'host47.example.com';

CREATE USER 'custom'@'%.example.com'

IDENTIFIED BY 'password';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON customer.addresses
TO 'custom'@'%.example.com';
The three accounts can be used as follows:

 The 'custom'@'localhost' account has all database-level privileges to access

the bankaccount database. The account can be used to connect to the server only from the
local host.
 The 'custom'@'host47.example.com' account has specific database-level privileges to
access the expenses database. The account can be used to connect to the server only from
the host host47.example.com.
 The 'custom'@'%.example.com' account has specific table-level privileges to access
the addresses table in the customer database, from any host in the example.com domain.
The account can be used to connect to the server from all machines in the domain due to use of
the % wildcard character in the host part of the account name.
Checking Account Privileges and Properties
To see the privileges for an account, use SHOW GRANTS:
mysql> SHOW GRANTS FOR 'admin'@'localhost';
+-----------------------------------------------------+
| Grants for admin@localhost |
+-----------------------------------------------------+
| GRANT RELOAD, PROCESS ON *.* TO `admin`@`localhost` |
+-----------------------------------------------------+
To see nonprivilege properties for an account, use SHOW CREATE USER:
mysql> SET print_identified_with_as_hex = ON;
mysql> SHOW CREATE USER 'admin'@'localhost'\G
*************************** 1. row ***************************
CREATE USER for admin@localhost: CREATE USER `admin`@`localhost`
IDENTIFIED WITH 'caching_sha2_password'
AS
0x24412430303524301D0E17054E2241362B1419313C3E44326F294133734B30792F436E7776
4270373039612E32445250786D43594F45354532324B6169794F47457852796E32
REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
PASSWORD HISTORY DEFAULT
PASSWORD REUSE INTERVAL DEFAULT
 PASSWORD REQUIRE CURRENT DEFAULT
Enabling the print_identified_with_as_hex system variable causes SHOW CREATE USER to
display hash values that contain unprintable characters as hexadecimal strings rather than as
regular string literals.
Revoking Account Privileges
To revoke account privileges, use the REVOKE statement. Privileges can be revoked at different
levels, just as they can be granted at different levels.
Revoke global privileges:

REVOKE ALL
ON *.*
FROM 'finley'@'%.example.com';

REVOKE RELOAD
ON *.*
FROM 'admin'@'localhost';
Revoke database-level privileges:

REVOKE CREATE,DROP
ON expenses.*
FROM 'custom'@'host47.example.com';
Revoke table-level privileges:

REVOKE INSERT,UPDATE,DELETE
ON customer.addresses
FROM 'custom'@'%.example.com';
To check the effect of privilege revocation, use SHOW GRANTS:
mysql> SHOW GRANTS FOR 'admin'@'localhost';
+---------------------------------------------+
| Grants for admin@localhost |
+---------------------------------------------+
| GRANT PROCESS ON *.* TO `admin`@`localhost` |
+---------------------------------------------+
Dropping Accounts
To remove an account, use the DROP USER statement. For example, to drop some of the accounts
created previously:
DROP USER 'finley'@'localhost';
DROP USER 'finley'@'%.example.com';
DROP USER 'admin'@'localhost';
DROP USER 'dummy'@'localhost';

MySQL / Authentication and authorization

How to use `GRANT` and `REVOKE` to
manage privileges in MySQL
CONTENT

 Introduction

 Prerequisites
  How do privileges work in MySQL?

 How do you see what privileges an account has?

 How do you use the GRANT command?

 Granting common privileges to user accounts

 How do you use the REVOKE command?

 What is the SUPER privilege?

 Conclusion

SHARE ON
Introduction
Privilege management is an important part of system and database
administration. Deciding who should have what access to which components
and powers and then designing an implementation that enables those
policies requires a good deal of thought and care.

MySQL has a robust privilege assignment system that allows you to

implement access policies throughout your database system. In this guide,
we will talk about how to use the GRANT and REVOKE commands to add and
remove privileges from MySQL user accounts and implement access policies
that match your requirements.

Prerequisites
To follow along with this guide, you'll need an account on a MySQL server
with the appropriate privileges.

Commands we will use

The most important commands we'll be using in this guide are

the GRANT and REVOKE commands:

 GRANT: use to assign new privileges to a user account

  REVOKE: use to remove existing privileges from a user account

Required privileges

To manage privileges for MySQL users, you need to have the following
privileges:

 GRANT OPTION: the GRANT OPTION privilege allows you to grant or revoke
any privilege that you have been granted

 whatever privileges you wish to assign to other users

 SELECT on mysql.*: used to execute SHOW GRANTS for other accounts

To follow along with this guide, we will assume that you are using an account
with full administrative privileges (including the GRANT OPTION privilege). This
could be the common 'root'@'localhost' user that is configured during
installation, or any other user with full privileges.

How do privileges work in MySQL?

In MySQL, the privilege system determines whether a user can execute a
given command or not.

Each time a client attempts to perform an action, MySQL consults its

information on the user's privileges to determine whether it should be
allowed or not. If the user has been granted all of the privileges required to
perform the action, MySQL executes the statements. If the user is missing
any of the required privileges, an error will occur.

MySQL stores the information about which users have what privileges in a
number of different tables in the mysql system database. Here is a review the
where MySQL keeps different types of privilege information as was covered
in the introduction to MySQL authentication and authorization article:

 user: The user table defines each user's static global privileges. These
privileges apply to the whole MySQL server and are not affected by the
availability of any plugins or components.
  global_grants: The global_grants table defines each user's dynamic global
privileges. Any privileges defined by a plugin or component are registered
in this table.

 db: The db table defines database-level privileges. The db table matches

the user's User and Host values just like the user table but also has a
column called Db that defines the database scope for the row.

 tables_priv: The tables_priv table defines table-level privileges in a similar

way that the db table does for databases. To enable table-level scope, a
column called Table_name is available in addition to the User, Host, and Db.

 columns_priv: A step further than the tables_priv table, the columns_priv table
determines access at the column level. To add this additional granularity,
a column called Column_name is included in addition to the columns
available within the tables_priv table.

 procs_priv: The procs_priv table defines privileges for executing procedures

and functions. It uses the User, Host, Db, Routine_name,
and Routine_type columns to scope the user's privileges for different types
of processes.

 proxies_priv: The proxies_priv table defines a user's proxying privileges.

Proxying allows one user to act as another user, inheriting their privileges.
The proxies_priv table uses the User and Host columns to match a user and
then uses separate columns called Proxied_host and Proxied_user to define
who the matched user can act as.

What privileges are available in MySQL?

MySQL defines many privileges appropriate for various system scopes. Some
of these are useful for everyday use and management of databases, tables,
and functions, while others are designed for administrative tasks
like replication, backups, and connection management.

You can find a comprehensive list of static privileges (core privileges built
into MySQL itself) and their respective scopes in the Permissible Static
Privileges for GRANT and REVOKE table in the MySQL documentation. The
related Static Privilege Descriptions section of the MySQL documentation
provides a detailed overview of what each privilege allows and in many
cases, guidance on what scenarios they would be most useful.

Dynamic privileges are the other type of privilege. Dynamic privileges are
defined in plugins or components and are registered with MySQL to enable
them. They are always global in scope and provide additional capabilities or
features. The Permissible Dynamic Privileges for GRANT and REVOKE table in
the MySQL documentation lists each dynamic privilege and its context. You
can find full descriptions of what each is used for in the associated Dynamic
Privilege Descriptions section of the MySQL documentation.

To find out which privileges are enabled and available on your MySQL server,
as well as the context in which they're relevant, you can use the following
command:

SHOW PRIVILEGES

This can help you understand what privileges are best suited for your users'
responsibilities.

How do you see what privileges an account

has?
Now that we've reviewed how privileges in MySQL work and what privileges
are available, how do you figure out which privileges have been granted to
each account?

You can always view the privileges granted to your own user by typing:

SHOW GRANTS;
+--------------------------------------------------------------------+
Grants for exampleuser@localhost |
+--------------------------------------------------------------------+
GRANT USAGE ON *.* TO `exampleuser`@`localhost` |
GRANT ALL PRIVILEGES ON `exampledb`.* TO `exampleuser`@`localhost` |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)
 Here, we see that 'exampleuser'@'localhost' has two sets of privileges defined.
The first entry shows that it has been granted USAGE globally (indicated by
the wildcard <database>.<table> scope of *.*). Despite its name, USAGE in this
context actually means "no privileges are granted". So, by default, this user
hasn't been given any privileges. The second record shows that they have
been granted ALL PRIVILEGES, or complete access, to the exampledb database.

If the user account you are logged in as has SELECT privileges on the
internal mysql database, you can see the privileges granted to other user
accounts. To show the privileges of other accounts, use the following format:

SHOW GRANTS FOR '<user>'@'<host>';

The output will display the privileges of the provided account.

How do you use the GRANT command?

To GRANT command is used to assign new privileges to an account. It is the
primary way of adding access to a user account to databases, objects, or
actions that they previously did not have. Whenever you wish to provide
additional access to a user account, the GRANT command can help.

Basic syntax

The basic syntax of the GRANT command to assign privileges is fairly

straightforward. It follows this format:

GRANT <privileges> ON <database>.<object> TO '<user>'@'<host>';

Multiple privileges can be provided, separated by commas.

Targeting databases, tables, columns, etc.

The <database>.<object> part of the syntax above dictates the scope where
the privileges will be granted. This will determine which objects the
privileges will be granted for and the specific table in the mysql database
where the new privileges will be recorded.
 To grant a privilege globally, allowing a user account to use the privilege
throughout the entire system, use wildcards for both the database and
database object part of the scope component:

For example, to grant SELECT privileges globally for 'sally'@'localhost', you

would type:

GRANT SELECT ON *.* TO 'sally'@'localhost';

To limit the scope of a grant to a single database, replace the wildcard on

the left side of the dot with a database name:

GRANT SELECT ON accounting.* TO 'meredith'@'localhost';

If an account only needs access to a single table within a database, specify

the table name on the right side of the dot:

GRANT UPDATE ON accounting.revenue TO 'frank'@'localhost';

Finally, applying privileges to specific columns follows a slightly different

format. When scoping to the column level, you must provide the columns to
which the privilege should apply in parentheses following the privilege name.

For example, to grant the ability to update the value of the due_by column in
the library.loans table, you can type:

GRANT UPDATE (due_by) ON library.loans TO 'autorenew'@'localhost';

Using the WITH GRANT OPTION clause

An additional clause, called WITH GRANT OPTION, can be appended to grant

statements to allow the user account to manage grants for other users at a
particular scope. Instead of just granting the privilege to the user, you are
also granting the ability for that user to pass on any privileges they have at
the same scope to other users.

For instance, here, we can give

the 'librarymanager'@'localhost' account SELECT, INSERT, UPDATE,
and DELETE privileges, as well as the ability to pass on its privileges at in
the library database to other users:

GRANT SELECT,INSERT,UPDATE,DELETE ON library.* TO 'librarymanager'@'localhost'

WITH GRANT OPTION;

It is important to realize that the WITH GRANT OPTION clause applies to the
account ('librarymanager'@'localhost') and the scope (library.*), not the specific
privileges in the statement. This means that although we've assigned four
new privileges to the 'librarymanager'@'localhost' account in this statement,
the WITH GRANT OPTION allows it to pass on any of its privileges at
the library.* scope. Since the account now has the GRANT OPTION for this
scope, if we give 'librarymanager'@'localhoast' additional privileges in the
future, it'll also be able to pass on those privileges automatically.

Although you can use the WITH GRANT OPTION clause as demonstrated above
to allow an account to pass on its privileges while you are giving them
additional privileges, it's often more clear if you separate these two actions,
like this:

GRANT SELECT,INSERT,UPDATE,DELETE ON library.* TO 'librarymanager'@'localhost';

GRANT GRANT OPTION ON library.* TO 'librarymanager'@'localhost';

When you handle GRANT OPTION as a regular privilege, you can also combine
it in the list of privileges you are assigning:

GRANT SELECT,INSERT,UPDATE,DELETE,GRANT OPTION ON library.* TO

'librarymanager'@'localhost';

In any of these cases, the result is that the 'librarymanager'@'localhost' account

will be able to grant any of the privileges it possesses for
the library database, now and in the future, to other users. This makes
the GRANT OPTION privilege especially dangerous if assigned carelessly, as it
can allow the user to give accounts additional privileges not intended by the
administrator.

Granting common privileges to user accounts

 Now that we've talked about how granting privileges works in general, we
can go through some examples of how to assign various common privileges
to user accounts.

How do you grant users full access?

Often, you want to assign a specific user complete ownership over a

database or database component. For instance, your sales database might
have a specific user designated to manage the tables, functions, and indexes
within.

You can assign full privileges to a user at a specific scope using the ALL or ALL
PRIVILEGES shorthand:

GRANT ALL PRIVILEGES ON sales.* TO 'salesadmin'@'localhost';

This will grant every privilege that your user is capable of assigning on
the sales database to the 'salesadmin'@'localhost' user, with a couple important
exceptions. The ALL PRIVILEGES privilege bundle does not include the GRANT
OPTION or PROXY privileges, which must be assigned separately. This is to
make it easier to assign full privileges without passing on privilege
administration and user substitution privileges.

To assign all privileges except GRANT OPTION and PROXY globally, use
the *.* scope:

GRANT ALL PRIVILEGES ON *.* TO 'systemadmin'@'localhost';

How do you grant users full access including privilege

administration?

To assign full privileges and also give the user the ability to pass on any of its
privileges, include the GRANT OPTION in the statement. For example, to give
the 'salesadmin'@'localhost' account from the last example the ability to control
other users' access to the sales database, you could instead type:

GRANT ALL PRIVILEGES ON sales.* TO 'salesadmin'@'localhost' WITH GRANT OPTION;

 The account will then not only have full access to the sales database, it will
also be able to dictate what other users are able to do on the database.

This same logic can be applied globally using the *.* context. In this cases,
it'll make the given account a full administrative user:

GRANT ALL PRIVILEGES ON *.* TO 'fulladmin'@'localhost' WITH GRANT OPTION;

How do you grant users read-only access?

Often, at the database or table level, you'll have some accounts that need to
be able to access information but should not have the ability to alter the
database or object in any way. These may include reporting tools or any
scenario where data needs to be accessible but not modifiable, like with
many non-interactive webpages.

The SELECT privilege is adequate to give the user read-only privileges on the
database or object. To give the 'salesreport'@'localhost' user read-only access
to the sales database, type:

GRANT SELECT ON sales.* TO 'salesreport'@'localhost';

This user will be able to query and extract any data it requires from
the sales database, but it cannot make any changes.

As usual, the global equivalent uses the *.* scope:

GRANT SELECT ON *.* TO 'globalread'@'localhost';

How do you grant users read and write access?

The typical companion to the read-only use case is the user who needs read
and write access. This type of access is appropriate for any processes that
need to manage the data within the database or the object. For instance, a
process that creates or edits website user profiles would need both read
and write privileges.
 To assign read and write access to a user, grant them SELECT, INSERT, UPDATE,
and DELETE privileges on the object. For example:

GRANT SELECT,INSERT,UPDATE,DELETE ON website.profiles TO

'profilemanager'@'localhost';

How do you grant users append-only access?

Another common scenario is making an account that can only append data
to a table or other object. This way, the process always has additive
permissions to the object, but cannot rewrite or modify entries that are
already present. This can be useful for append-only event logging or
scenarios where updates are actually stored as new records to preserve
history.

To allow an account append-only privileges on a database object, only grant

them SELECT and INSERT privileges:

GRANT SELECT,INSERT ON website.eventlog TO 'weblogger'@'localhost';

If you want the account to selectively be able to update certain parts of the
record, you can additionally grant them UPDATE privileges on the appropriate
columns:

GRANT SELECT,INSERT ON website.eventlog TO 'weblogger'@'localhost';

GRANT UPDATE (comments) ON website.eventlog TO 'weblogger'@'localhost';
How do you use the REVOKE command?
Now that we've taken a look at the GRANT command, we need to introduce
its counterpart, REVOKE. While the GRANT command assigns additional
privileges to a user at a specific scope, the REVOKE command allows you to
remove privileges from an account.

Basic syntax
 The REVOKE command mirrors the GRANT command fairly closely. Aside from
the command name, you revoke privileges from an account rather than
granting them to the account.

The basic syntax looks like this:

REVOKE <privileges> ON <database>.<object> FROM '<user>'@'<host>';

As with GRANT, multiple privileges can be named, separated by commas.

Targeting databases, tables, columns, etc.

Since privileges are tied to a specific scope (global, database, table, etc.),
the REVOKE command must specify the scope from which to remove the
privilege, just as you do when adding privileges.

To remove a privilege at the global level, use the *.* wildcard to match any
database and any database object:

REVOKE SELECT ON *.* FROM 'sally'@'localhost';

To remove a privilege from a specific database, specify the database name

on the left side of the dot:

REVOKE SELECT ON accounting.* FROM 'meredith'@'localhost';

And finally, to remove a privilege from a database object, name the database
and the object name separated by a dot:

REVOKE UPDATE ON accounting.revenue FROM 'frank'@'localhost';

It's a good idea to check the user's available privileges after revoking to
make sure that they do not still have unwanted access granted through any
other means:

SHOW GRANTS FOR 'frank'@'localhost';

Using partial revokes to fine tune privileges

As of MySQL 8.0.16, partial revocation is supported. This means that you can
give an account broad privileges and then selectively remove those
privileges for specific scopes.

For example, you can set up an account that has full privileges over the
database except for on the mysql database, which is used to store system
information like privileges, authentication details, and more for users. A
partial revoke would allow you to grant full privileges and then add a special
exception for that database.

To enable partial revocation in MySQL, you need to enable it. You can turn it
on persistently by typing the following in supported versions (MySQL 8.0.16
or later):

SET PERSIST partial_revokes = ON;

Now, to set up the user account described above, you could type:

CREATE USER 'normaladmin'@'localhost' IDENTIFIED BY '<password>';

GRANT ALL PRIVILEGES ON *.* TO 'normaladmin'@'localhost';
REVOKE ALL PRIVILEGES ON mysql.* FROM 'normaladmin'@'localhost';
GRANT SELECT ON mysql.* TO 'normaladmin'@'localhost';

Here, we've created a user and granted them full privileges for the entire
MySQL server. Afterwards, we revoke those privileges specifically in the
context of the mysql database. We then re-grant the SELECT privilege so that
the account can still read values from the database.

If you look at the privileges for this account, something similar to this will be
displayed:

SHOW GRANTS FOR 'normaladmin'@'localhost'\G

*************************** 1. row ***************************
Grants for normaladmin@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE,
DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW
DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE,
REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE
ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE,
CREATE ROLE, DROP ROLE ON *.* TO `normaladmin`@`localhost`
*************************** 2. row ***************************
Grants for normaladmin@localhost: GRANT
APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG
_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GR
OUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,P
ERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESO
URCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_AD
MIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_
VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO
`normaladmin`@`localhost`
*************************** 3. row ***************************
Grants for normaladmin@localhost: REVOKE INSERT, UPDATE, DELETE, CREATE, DROP,
REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE,
CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON
`mysql`.* FROM `normaladmin`@`localhost`
3 rows in set (0.00 sec)

Th