MySQL User Management and Access Control

Introduction

User management and access control is at the heart of any database server. This page explains how to create and maintain user accounts, and how to grant and revoke privileges to users.

This page is part of the MySQL administration series:

User Accounts Names

A MySQL user account name consists of a username and hostname. In MySQL, the username is not a unique identifier. Two users can have the same username as long as they connect from different hosts.

The user account name syntax is: ‘username’@’hostname’. A user account name consisting only of a username is equivalent to ‘username’@’%’. The username and hostname parts only need to be quoted when using special characters such as %, . or -.

The table below provides some example account names and their permissible connections.

AccountPermissible connections
user@’%’A username “user” connecting from any hostname
user@localhostA username “user” connecting from the localhost only
user@’127.0.0.1’A username “user” connecting from the IPv4 loopback interface
user@’::1’A username “user” connecting from the IPv6 loopback interface
user@’actimem.com’A username “user” connecting from the actimem.com domain
@’actimem.com’Any user connecting from the actimem.com domain
user@’196.168.1.%’A username “user” connecting from any IP address between 196.168.1.0 and 196.168.1.255
user@’%.com’A username “user” connecting from any .com domain

Creating User Accounts

To create a new user account you use the CREATE USER command:

If you do not specify a password with IDENTIFIED BY, then you are creating an unsecured account.

You can change the password with the SET PASSWORD command:

MySQL stores passwords as an encrypted hash value. You need to use the password() function with SET PASSWORD to encrypt the password. Note this is somewhat inconsistent, as the IDENTIFIED BY part of the CREATE USER encrypts the password for you.

If you forget to use the password() function then you will save the clear text password. You will not be able to log in into your account, as MySQL will attempt to match the hashed login password with the clear text version.

You could also change the password by updating the mysql.user table directly. You will need to flush privileges to update the caches.

You can use the ALTER USER command to expire a password:

Finally, to delete a user account you use the DROP USER command:

User accounts and passwords are stored in the user table of the MySQL database. You can query this table to retrieve the list of all user accounts.

Current user vs Login user

MySQL differentiates between the logged in user and the current user.

For example, you could create a user account for user@’%’:

If you now log in to MySQL from the command line, you will be logging in as user user@localhost

However, the user account for user@localhost has not been created yet. Instead, MySQL will use the account for user@’%’.  The user() function can be used to show the user used to log in. The current_user() function can be used to return the user MySQL is using.

Access Control with Privileges

Show User Privileges

To show the privileges granted to a user, you use the SHOW GRANTS command:

To show the privileges granted to the current user, you can use either:

Grant Privileges

The GRANT statement gives privileges to users. To grant a privilege you need to have the privilege and the GRANT OPTION privilege. This is the syntax:

The following GRANT statement assigns all privileges to a use (except the GRANT OPTION privilege):

To create a super-user account, you would also need to assign the GRANT OPTION privilege:

 The following GRANT statement provides basic SQL usage to all tables in a database:

Finally, this GRANT statement provides read access to only few columns in a table:

You can assign privileges at several levels with the ON [database.]object part:

SpecifierDescription
ON *.*All objects in all databases
ON db.*All objects within a database
ON db.table_nameA given table name
ON table_nameA given table name in the current database

Revoke Privileges

The REVOKE statement takes away privileges. To revoke privileges, you need to have the privileges you are revoking and the GRANT OPTION privilege. This is the syntax:

To revoke all privileges you can use this statement:

You can find below few REVOKE statement examples:

MySQL Privileges

The MySQL privileges can be categorised in these groups:

  • Administrative privileges – used to manage the MySQL server
  • Basic SQL privileges – used to access and update table information
  • DDL manipulation privileges – used to create and delete objects in the database, such as tables, views, indexes, triggers and stored procedures.

Administrative privileges

PrivilegeDescription
ALLAll privileges except the GRANT OPTION privilege.
CREATE USEREnables CREATE USER, DROP USER, ALTER USER, RENAME USER and REVOKE ALL PRIVILEGES
GRANT OPTIONAllows a user to give or remove privileges to others
SHOW DATABASESShow all database names with SHOW DATABASES. If this privilege is not available, then only shows the databases you are entitled to.
SHUTDOWNShut down the server
SUPERKill threads and other supervisory operations
FILEAllows to read files from the server using LOAD DATA INFILE, and write files to the server using SELECT … INTO OUTFILE. This is a dangerous permission, as the user can write to any directory the MySQL has access to.

Basic SQL privileges

PrivilegeDescription
SELECTSelect rows from a table
UPDATEUpdates rows from a table
INSERTInsert rows into a table
DELETEDelete rows from a table
EXECUTEExecute stored routines

DDL manipulation privileges

PrivilegeDescription
CREATECreate databases and tables
DROPDrop existing databases, tables and views. Also needed for TRUNCATE TABLE.
LOCK TABLESLocks tables using LOCK TABLES statement
ALTERAlter tables
INDEXCreate and drop indexes
CREATE TEMPORARY TABLESCreates temporary tables
TRIGGERCreates a Trigger
CREATE VIEWCreates a View
SHOW VIEWEnables SHOW CREATE VIEW
ALTER ROUTINEAlter a stored routine
CREATE ROUTINECreates a stored routine

Bibliography

The following two tabs change content below.

Eduard Manas

Eduard is a senior IT consultant with over 15 years in the financial sector. He is an experienced developer in Java, C#, Python, Wordpress, Tibco EMS/RV, Oracle, Sybase and MySQL.Outside of work, he likes spending time with family, friends, and watching football.

Latest posts by Eduard Manas (see all)

Leave a Reply