MySQL Administration: Grant & Revoke privileges to a user account

Summary: in this tutorial, you will learn how to use the  GRANT, REVOKE statement to grant privileges to user accounts.

Introduction to the MySQL GRANT statement

The CREATE USER statement creates one or more user accounts with no privileges. It means that the user accounts can log in to the MySQL Server, but cannot do anything such as selecting a database and querying data from .

To allow user accounts to work with database objects, you need to grant the user accounts privileges. And the GRANT statement grants a user account one or more privileges.

The following illustrates the basic syntax of the GRANT statement:

GRANT privilege [,privilege],..
ON privilege_level
TO account_name;

In this syntax:

First, specify one or more privileges after the GRANT keyword. If you grant multiple privileges, you need to separate privileges by commas.

This example grants the SELECT privilege on the table employees  in the sample database to the user acount bob@localhost:

ON employees
TO bob@localhost;

The following example grants UPDATEDELETE, and INSERT privileges on the table employees to bob@localhost:

ON employees
TO bob@localhost;

Second, specify the privilege_level that determines the level to which the privileges apply.

MySQL supports the following main privilege levels:

MySQL Grant - Privilege Level

Global privileges apply to all databases in a MySQL Server. To assign global privileges, you use the *.* syntax, for example:

ON *.*
TO bob@localhost;

The account user bob@localhost can query data from all tables in all database of the current MySQL Server.

Database privileges apply to all objects in a database. To assign database-level privileges, you use the ON database_name.* syntax, for example:

ON classicmodels.*
TO bob@localhost;

In this example, bob@localhost can insert data into all tables in the classicmodels database.

Table privileges apply to all columns in a table. To assign table-level privileges, you use the ON database_name.table_name syntax, for example:

ON classicmodels.employees
TO bob@localhsot;

In this example, bob@localhost can delete rows from the table employees in the database classicmodels.

If you skip the database name, MySQL uses the default database or issues an error if there is no default database.

Column privileges apply to single columns in a table.  You must specify the column or columns for each privilege, for example:

   SELECT (employeeNumner,lastName, firstName,email),
ON employees
TO bob@localhost;

In this example, bob@localhost can select data from four columns employeeNumberlastNamefirstName, and email and update only the lastName column in the employees table.

Stored routine privileges apply to stored procedures and stored functions, for example:

TO bob@localhost;

In this example, bob@localhost can execute the stored procedure CheckCredit in the current database.

user privileges allow one user to be a proxy for another. The proxy user gets all privileges of the proxied user. For example:

TO alice@localhost;

In this example, alice@localhost assumes all privileges of root.

Finally, specify the account name of the user that you want to grant privileges after the TO keyword.

Notice that in order to use the GRANT statement, you must have the GRANT OPTION privilege and the privileges that you are granting. If the read_only system variable is enabled, you need to have the SUPER privilege to execute the GRANT statement.

MySQL GRANT statement examples

Typically, you use the CREATE USER statement to create a new user account first and then use the GRANT statement to grant privileges to the user.

First, create a new user called super@localhost by using the following CREATE USER statement:

CREATE USER super@localhost
IDENTIFIED BY 'Secure1Pass!';

Second, show the privileges assigned to super@localhost user by using the SHOW GRANTS statement.

SHOW GRANTS FOR super@localhost;
MySQL Grant - No Privilege

The USAGE means that the super@localhost can log in the database but has no privilege.

Third, grant all privileges in all databases in the current database server to super@localhost:

ON classicmodels.*
TO super@localhost;

Fourth, use the SHOW GRANTS statement again:

SHOW GRANTS FOR super@localhost;
MySQL Grant example

Permissible privileges for GRANT statement

The following table illustrates all permissible privileges that you can use for the GRANT and REVOKE statement:

Privilege Meaning Level
Global Database Table Column Stored Routine Proxy
ALL [PRIVILEGES] Grant all privileges at specified access level except GRANT OPTION
ALTER Allow user to use of ALTER TABLEstatement X X X
ALTER ROUTINE Allow user to alter and drop stored procedures or stored functions. X X X
CREATE Allow user to create databases and tables X X X
CREATE ROUTINE Allow user to create stored procedures and stored functions X X
CREATE TABLESPACE Allow user to create, alter or drop tablespaces and log file groups X
CREATE TEMPORARY TABLES Allow user to create a temporary table by using CREATE TEMPORARY TABLE statement X X
CREATE VIEW Allow user to create or modify the view. X X X
DELETE Allow user to use DELETE statement X X X
DROP Allow user to drop database, table and view X X X
EVENT Enable use of events for the Event Scheduler. X X
EXECUTE Allow user to execute stored routines X X X
FILE Allow user to read any file in the database directory. X
GRANT OPTION Allow user to have privileges to grant or revoke privileges from other accounts. X X X X X
INDEX Allow user to create or drop indexes. X X X
INSERT Allow user to use the INSERT statement X X X X
LOCK TABLES Allow user to use LOCK TABLES on tables for which you have the SELECT privilege X X
PROCESS Allow user to see all processes with SHOW PROCESSLIST statement. X
PROXY Enable user proxying.
REFERENCES Allow user to create a foreign key X X X X
RELOAD Allow user to use FLUSH statement X
REPLICATION CLIENT Allow user to query to see where master or slave servers are X
REPLICATION SLAVE Allow the user to use replicate slaves to read binary log events from the master. X
SELECT Allow user to use SELECT statement X X X X
SHOW DATABASES Allow user to show all databases X
SHOW VIEW Allow user to use SHOW CREATE VIEW statement X X X
SHUTDOWN Allow user to use mysqladmin shutdown command X
SUPER Allow user to use other administrative operations such as CHANGE MASTER TOKILLPURGE BINARY LOGSSET GLOBAL,

and mysqladmin command

TRIGGER Allow user to use TRIGGER operations. X X X
UPDATE Allow user to use the UPDATE statement X X X X
USAGE Equivalent to “no privileges”

Introduction to the MySQL REVOKE statement

The REVOKE statement revokes one or more privileges from a user account.

The REVOKE statement has several forms.

Revoke one or more privileges

The following illustrates the basic syntax of the REVOKE statement that revokes one or more privileges from user accounts:

      privilegee [,privilege]..
ON [object_type] privilege_level
FROM user1 [, user2] ..;

In this syntax:

  • First, specify a list of comma-separated privileges that you want to revoke from a user account after the REVOKE keyword.
  • Second, specify the type and privilege level of the privileges after the ON keyword; check it out the GRANT statement for more information on privilege level.
  • Third, specify one or more user accounts from which you want to revoke the privileges in the FROM clause.

Note that to execute this form of REVOKE statement, you must have GRANT OPTION privilege or you must have the privileges that you are revoking.

Revoke all privileges

To revoke all privileges from a user, you use the following form of the REVOKE ALL statement:

FROM user1 [, user2];

To execute the REVOKE ALL statement, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql system database.

Revoke Proxy

To revoke a proxy user, you use the REVOKE PROXY command:

ON proxied_user
FROM proxy_user1[,proxy_user1]...;

A proxy user is a valid user in MySQL who can impersonate another user, therefore, the proxy user has all privileges of the user that it impersonates.

It is a good practice to show privileges of the user accounts using the SHOW GRANTS statement before you revoke the privileges from the user:


MySQL REVOKE examples

Let’s take some examples of revoking privileges.

A) Using MySQL REVOKE to revoke some privileges from a user account example

First, create a user account named rfc@localhost:

CREATE USER rfc@localhost
IDENTIFIED BY 'Secret1Pass!';

Second, grant rfc@localhost the SELECTUPDATE, and INSERT privileges on the classicmodels database:

ON classicmodels.*
TO rfc@localhost;

Third, display the granted privileges of the rfc@localhost user account:

SHOW GRANTS FOR rfc@localhost;

Fourth, revoke the UPDATE and INSERT privileges from rfc@localhost:

ON classicmodels.*
FROM rfc@localhost;

Fifth, display the privileges of rfc@localhost:

SHOW GRANTS FOR rfc@localhost;
MySQL Revoke privileges example

B) Using MySQL REVOKE to revoke all privileges from a user account example

First, grant the EXECUTE privilege to the rfc@localhost:

ON classicmodels.*
TO rfc@localhost;

Second, show the currently granted privileges of rfc@localhost:

Third, revoke all privileges of the rfc@localhost user account by using the REVOKE ALL statement:

FROM rfc@localhost;

Finally, show the privileges of the rfc@localhost to verify the revoke:

SHOW GRANTS FOR rfc@localhost;
MySQL Revoke All Example

The rfc@localhost has no privileges. Note that USAGE privilege means no privileges in MySQL.

C) Using MySQL REVOKE to revoke PROXY privilege example

First, grant the PROXY privilege to rfc@localhost user account:

ON root
TO rfc@localhost;

Second, show the granted privileges of rfc@localhost:

SHOW GRANTS FOR rfc@localhost;
MySQL Revoke Proxy

Third, revoke the PROXY privilege from the rfc@localhost:

ON root
FROM rfc@localhost;

Finally, show the granted privileges of rfc@lcoalhost to verify the revoke:

SHOW GRANTS FOR rfc@localhost;

When the MySQL REVOKE command takes effect

The effect of REVOKE statement depends on the privilege level:

Global level

The changes take effect when the user account connects to the MySQL Server in the subsequent sessions. The changes are not applied to all currently connected users.

Database level

The changes take effect after the next USE statement.

Table and column levels

The changes take effect on all subsequent queries.

In this tutorial, you have learned how to use the MySQL GRANT, REVOKE statement to grant privileges to a user.

Print Friendly, PDF & Email

Related posts:

Random posts:

%d bloggers like this: