Creating MySQL users with GRANT

Last update:

To create users in MySQL 5 without a helper such as phpMyAdmin, you need to know the GRANT statement—but a few details can still get in the way.

This article covers good practices for creating database users, GRANT syntax, and ready-to-use commands for several kinds of users.

What kinds of users? ​

Many blogs, articles, examples, and administration panels use one username and password for an entire application. That is a serious mistake.

MySQL users include both a username and a source address. They appear in the form 'username'@'source-address'. The same user can have different privileges when connecting from localhost or from an external machine.

In practice, these are different users. When creating one, you must specify both the username and source address.

The % character is a wildcard that can match part of a source address or any source. For example, 'someone'@'192.168.0.%' can connect from any machine on 192.168.0.0/24; the same works for hostnames if the server can resolve the source host. 'someone'@'%' can connect from any address, including the same machine.

If your application and database share a server, create a user with localhost as the source host. This prevents someone with the username and password from connecting remotely.

Another important practice is to grant users only the privileges they need. Do not create superusers with permission to alter the database or create users unless your application requires it. Applications usually read, insert, update, and delete records; some avoid deletion with a soft-delete technique. Keep a highly privileged user for maintenance.

When writing a username, quote the username and source address separately. The @ must not be inside the quotes.

GRANT syntax ​

The full GRANT syntax is documented here. The simplified syntax is:

sql
GRANT <privilégios> ON <banco de dados>.<tabela> TO '<usuario>'@'<origem>' IDENTIFIED BY '<senha>';

Setting a password with IDENTIFIED BY '<password>' is optional if the user already exists.

The privileges, separated by commas, include:

  • ALL: equivalent to all privileges below, except GRANT;
  • ALTER: alter tables and create indexes;
  • ALTER ROUTINE: alter stored procedures;
  • CREATE ROUTINE: create stored procedures;
  • EXECUTE: execute stored procedures;
  • CREATE: create tables and databases;
  • CREATE TEMPORARY TABLES: create temporary tables;
  • CREATE USER: create, remove, and alter users, including their privileges (but not grant new privileges);
  • CREATE VIEW: create and alter views;
  • DELETE: delete rows;
  • DROP: delete views, tables, and databases;
  • EVENT: manage events;
  • FILE: create and read files stored on the database server;
  • GRANT OPTION: revoke and grant privileges to users;
  • INDEX: create or delete indexes;
  • INSERT: insert rows;
  • LOCK TABLES: lock tables the user can read;
  • PROCESS: read the list of processes running on the server;
  • RELOAD: run FLUSH commands;
  • SELECT: read databases or tables;
  • SHOW DATABASES: read the list of databases on the server;
  • SHOW VIEW: read the structure (generating query) of views;
  • SUPER: grant advanced administrative privileges;
  • TRIGGER: create, alter, and delete triggers;
  • UPDATE: update rows;
  • USAGE: equivalent to no privileges.

Privilege availability and behaviour can vary by MySQL version. Events are available only in version 5.1 and later; in versions earlier than 5.0, only superusers can edit triggers.

Creating a few users ​

Now that we have the basic syntax, let's create some users. The example uses CREATE USER in addition to GRANT to keep the creation of users with different permissions on different tables organized.

Suppose we need a DBA user with access to every table, a FULANO user for a simple application on the same machine, and a CICLANO user for maintaining database BD01, which FULANO reads. To make the example more specific, CICLANO can access the database only through an application at 192.168.0.45.

sql
/* Criando o usuário DBA */
CREATE USER 'dba'@'%' IDENTIFIED BY 'senhaMuitoDificil';
GRANT ALL ON *.* TO 'dba'@'%' WITH GRANT OPTION;

/* Criando o usuário FULANO */
CREATE USER 'fulano'@'localhost' IDENTIFIED BY 'outraSenha';
GRANT SELECT,UPDATE,DELETE,INSERT ON BD01.* TO 'fulano'@'localhost';

/* Criando o usuário CICLANO */
CREATE USER 'ciclano'@'192.168.0.45' IDENTIFIED BY 'ehOutraSenha';
GRANT ALL ON BD01.* TO 'ciclano'@'192.168.0.45';

/* Recarregando as permissões na memória do servidor */
FLUSH PRIVILEGES;

If you change privileges for every user, including ROOT, and can no longer access the database, you can start the service with --skip-grant-tables, connect with any username and password, and run a password-reset UPDATE for root.

With this option, MySQL ignores privileges and gives full access to anyone who connects. After changing the password, restart the service without the option as soon as possible.

Final notes ​

MySQL lets you grant privileges on individual columns. However, the more rules you add, the more work it takes to verify whether a user reading a table has permission.

References ​