MySQL Privileges The Ultimate Guide

Privileges in MySQL are an important part of the database.

In this guide, you’ll learn:

• what privileges are and what they do
• the privileges that are available
• how to give and take privileges from individual users and groups of users
• how to see privileges available for a user

Let’s get into it.

What Are Privileges in MySQL?

A privilege in MySQL is something that allows specific actions to be performed on the database. Privileges are given to a user, and that user is allowed to perform the action according to that specific privilege.

For example, there is a privilege called “UPDATE”. If a user has this privilege, they are allowed to update data in a table. If they don’t have this privilege, they are not allowed to update data in a table, and will receive an error when they try to.

Privileges are used to enhance security on the database. You could have all users with the ability to do everything on the database, but this could lead to misuse of the database and other security issues.

Privileges are a common feature in databases, and MySQL has its own implementation of the concept.

Let’s take a look at an example of using privileges.

 

Giving Privileges to Users with GRANT

To give a privilege to a user, you use the GRANT keyword in MySQL.

The keyword has a lot of optional parameters that can be used, but in its simplest form, it looks like this:

GRANT privilege_type ON object TO user;

When you run this statement with your supplied parameters, you get this output if it is successful:

Query OK, 0 rows affected

It says 0 rows affected, which could seem like it’s done nothing, but the statement is successful.

If you want to see all of the parameters, you can check out the MySQL documentation for the GRANT statement.

 

Grant Privilege for All Tables

Let’s say we have a user called “robert”. We want to give them the privilege of selecting from some tables in our database which is called “gravity”.

Our statement would look like this:

GRANT SELECT ON gravity.* TO 'robert';

There are a few things to notice here:

• The privilege to select from tables is called SELECT
• Applying the privilege to all tables in a database is done by specifying the database name, then a period, then a star: gravity.*
• The username is in single quotes: ‘robert’

 

Grant Privilege for a Single Table

We can grant privileges for a single table in a similar method.

Let’s say we want to allow this ‘robert’ user to update a table called cust_order in the gravity database.

Our GRANT statement would look like this:

GRANT UPDATE ON gravity.cust_order TO 'robert';

We specify a single table: gravity.cust_order.

 

Grant Multiple Privileges

You can grant multiple privileges in a single statement by separating the privilege names with a comma.

For example:

GRANT UPDATE, INSERT ON gravity.cust_order TO 'robert';

This will grant both the UPDATE and INSERT privilege on the cust_order table to ‘robert’;

It’s easier than having separate statements for each privilege.

 

Grant All Privileges

You can grant all privileges to a user with a single statement:

GRANT ALL ON gravity.* TO 'robert';

This will give all privileges to the specified user, except for the “Grant Option”. You can find out more about the Grant Option later in this guide.

 

Taking Privileges from Users with REVOKE

If you want to remove a privilege from a user, you can use the REVOKE statement.

Like the Grant statement, it comes with several parameters. In a simple form, the statement looks like this:

REVOKE privilege_type ON object FROM user;

Let’s see some examples.

 

Revoke a Privilege for All Tables

To revoke a privilege (remove it from a user) for all tables in a database, your command would look like this:

REVOKE UPDATE ON gravity.* FROM 'robert';

This will remove the UPDATE privilege on all tables in the gravity database from the user ‘robert’;

It looks similar to the Grant statement.

When you run this, the user ‘robert’ will no longer be able to update data in the gravity database.

 

Revoke a Privilege for a Single Table

If you want to remove a privilege from a single table for a user, you can specify the table as part of the REVOKE statement:

REVOKE UPDATE ON gravity.cust_order FROM 'robert';

This will remove the UPDATE privilege from only the cust_order table, and none of the other tables in the gravity database.

 

Revoke All Privileges

You can also remove all privileges from a user. You can do this using ALL PRIVILEGES:

REVOKE ALL PRIVILEGES FROM 'robert';

This will remove all privileges from the user. The user will still exist but they won’t be able to do much in the database.

 

Viewing a User’s Privileges with SHOW GRANTS

If you’ve been granting and revoking privileges, or want to grant some privileges to users, you may want to know what privileges exist first before you make any changes.

You can do that using the SHOW GRANTS command:

SHOW GRANTS [FOR user_or_role];

The FOR user_or_role is optional: if this is omitted, it shows the privileges for the currently logged-in user.

Here’s an example. If you’re logged in as the “root” account, and run this command:

SHOW GRANTS;

You’ll see these results:

Grants for root@%

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 `root`@`%` WITH GRANT OPTION

GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN, BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN, FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES, GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE, PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN, RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN, SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID, SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN, TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`%` WITH GRANT OPTION

There’s a lot here. This shows all of the privileges for the root user.

What about a regular user that is not root?

Let’s create the ‘robert’ user mentioned above and grant a few privileges:

CREATE USER 'robert' IDENTIFIED BY 'mypassword';
GRANT SELECT ON gravity.* TO 'robert';
GRANT UPDATE ON gravity.cust_order TO 'robert';

The user is created when you run these statements.

You can check their privileges by running this statement, either as ‘robert’ or as root:

SHOW GRANTS FOR 'robert';

This is what is shown:

Grants for robert@%

GRANT USAGE ON *.* TO `robert`@`%`

GRANT SELECT ON `gravity`.* TO `robert`@`%`

GRANT UPDATE ON `gravity`.`cust_order` TO `robert`@`%`

You can see there are several rows, each of which show different privileges.

 

Using Roles to Manage Groups of Users

The concepts and examples mentioned so far are for applying privileges to single users.

If you have one or two users on your database, this is quite manageable.

But if you have a lot of users, such as hundreds, managing the privileges for them can be a lot of effort.

Fortunately, there’s another concept that can help: roles.

Roles in MySQL (and many databases) are a collection of privileges. You can assign a set of privileges to a role. You can then assign a user to that role with one command. The user will then receive all of the privileges for the role.

This makes it easier as instead of assigning privileges to hundreds of users, you could:

• create the roles you need (such as 3-5 roles)
• assign privileges to those roles
• add users to the roles

This will simplify the process of adding and removing privileges and make it easier for everyone involved.

 

Create a Role

To create a role, you can use the CREATE ROLE command:

CREATE ROLE 'team_developer';

This will create a new role called team_developer. It has no privileges by default, but they can be added.

 

Grant Privileges to a Role

To grant privileges to a role that has been created, you can use the Grant statement in the same way as you would for a user.

GRANT UPDATE, INSERT ON gravity.* TO 'team_developer';

This will give the UPDATE and INSERT privilege to the team_developer role.

 

Grant a Role to a User

To grant a role to a user, you use the GRANT statement.

GRANT role TO user;

There is no ON clause needed. We don’t need to specify the database or table, like we would need to with individual privileges.

Here’s an example:

GRANT 'team_developer' TO 'robert';

This will grant the team_developer role to the user of rober. This user will now have the same privileges as the team_developer role, which are Update and Insert.

 

Revoke Privileges from a Role

To remove privileges from a role, and therefore all users with that role, use the REVOKE statement:

REVOKE INSERT ON gravity.* FROM 'team_developer';

This will remove the INSERT privilege on all tables in the gravity database from the team_developer role.

All users with this role will also have this privilege removed.

 

Revoke a Role from a User

To remove a role from a user, you can also use the REVOKE command:

REVOKE 'team_developer' FROM 'robert';

This will revoke the team_developer role from the robert user.

 

Dropping Roles

To remove a role from the database entirely, use the DROP command:

DROP ROLE 'team_developer';

This will remove the team_developer role from the database.

 

Allowing Users to Grant Privileges: With Grant Option

When you grant privileges to a user, they receive the privileges. By default, they cannot then grant those privileges to someone else.

For example, if “admin” gives “robert” the privileges to UPDATE a table, “robert” cannot give that privilege to “mary”. The only one who can give the UPDATE privilege to “mary” is “admin” (or someone who has the ability to grant privileges).

If they want to be able to grant those privileges to another user, the command that gave them the privileges in the first place needs to include the keywords WITH GRANT OPTION.

So, in the example, “admin” would need to include WITH GRANT OPTION when they grant the UPDATE privilege to “robert”.

The command would look like this:

GRANT UPDATE ON gravity.* TO 'robert' WITH GRANT OPTION;

This means that “robert” can now grant this UPDATE privilege to other users, such as “mary”.

The user can grant any privileges that they have to others, not just the one included in the WITH GRANT OPTION command.

This WITH GRANT OPTION keyword is powerful and you should consider when you really need to use it.

 

Privileges Available in MySQL

There are many privileges available in MySQL. Here’s a list of them. They fall into two categories: static privileges (built into the server) and dynamic privileges (defined at runtime).

 

Static Privileges

This is a list of all static privileges in MySQL. These are probably the most common ones you will use when working with users.

• ALL PRIVILEGES: means “all privileges available at a given privilege level”, except for grant option.
• ALTER: allows Alter Table statements.
• ALTER ROUTINE: allows altering or dropping stored procedures or functions.
• CREATE: allows creating new databases and tables.
• CREATE ROLE: allows Create Role statements.
• CREATE ROUTINE: allows for creating stored procedures and functions.
• CREATE TABLESPACE: allows for creating, altering, or dropping tablespaces and log file groups.
• CREATE TEMPORARY TABLES: allows creating temporary tables.
• CREATE USER: allows for Alter User, Create Role, Create User, Drop Role, Drop User, Rename User, and Revoke All Privileges.
• CREATE VIEW: allows Create View statements.
• DELETE: allows the Delete statement.
• DROP: allows for databases, tables, and views to be dropped.
• DROP ROLE: allows the Drop Role statement.
• EVENT: allows creating, dropping, altering, or displaying events for the event scheduler.
• EXECUTE: allows for stored procedures and functions to be run.
• FILE: allows files to be read and written on the server, using Load Data, Select Into Outfile, and Load_File. Also allows creating new files, and the Data Directory or Index Directory table option in the Create Table statement.
• GRANT OPTION: allows granting or revoking privileges from other users that you have.
• INDEX: allows creating and dropping of indexes.
• INSERT: allows for Insert statements.
• LOCK TABLES: allows for Lock Tables statements.
• PROCESS: allows for information about processes to be viewed.
• PROXY: allows users to impersonate other users.
• REFERENCES: allows foreign key constraints to be created.
• RELOAD: allows the user of the Flush statement, Reset Master, and Reset Replica statements.
• REPLICATION CLIENT: allows the use of Show Master Status, Show Replica Status, and Show Binary Logs statements.
• REPLICATION SLAVE: allows for updates to be requested using a replication source server.
• SELECT: allows for Select statements to be run.
• SHOW DATABASES: allows for databases to be viewed using Show Databases.
• SHOW VIEW: allows for the Show Create View statement.
• SHUTDOWN: allows the use of Shutdown and Restart statements.
• SUPER: allows a large number of superuser-style privileges.
• TRIGGER: allows triggers to be created, dropped, executed, or displayed.
• UPDATE: allows the Update statement.
• USAGE: stands for “no privileges”.

 

Dynamic Privileges

There is a range of dynamic privileges in MySQL which apply to certain features. These are mostly for user admin, logging, and changing system settings. Instead of listing them here, you can view them on the MySQL documentation here.

 

Conclusion

Privileges allow users to perform certain tasks on the database. They can be granted and revoked from users by administrators. Roles can be created to group privileges to allow for easier and cleaner privilege management.