Managing users and privileges in mysql

How to search for active users, add new ones and set their passwords or how to create a login without password. Set an expiration date for the password or to expire on first access. See how to remove users, change the password of the logged in user or any other registered user. Change current access and set specific permissions for each user with each database and table. Remove permissions and see the table of available privileges.

MySQL commands taken from version 8.0 of the manual.

Create user

Adding a user can be done through the create user command or directly with the command grant, which will be seen later.

Mysql

# Create user jessica with 'my_password'
CREATE USER 'jessica'@'localhost'
  IDENTIFIED BY 'my_password';

# Create user without password
CREATE USER 'jessica'@'localhost';

# Password expires on first access requiring a new one
CREATE USER 'jessica'@'localhost'
  IDENTIFIED BY 'new_password' PASSWORD EXPIRE;

# Uses the caching_sha2_password authentication plugin,
# defines that the password expires every 180 days,
# enable account lock for 2 days upon reaching
# 3 login attempts with incorrect password
CREATE USER 'jessica'@'localhost'
  IDENTIFIED WITH caching_sha2_password BY 'my_password'
  PASSWORD EXPIRE INTERVAL 180 DAY
  FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2; 

Never use a user without a password in a production environment! Only for ease of access in controlled environments.

In the examples in this article, whenever manipulating users, the local machine context will be considered, this is what @localhost does. To guarantee remote access to the user, replace localhost with the desired IP (jessica@192.168.0.55).

Remove user

Mysql

DROP USER 'jessica'@'localhost';

List users

How to display user information, see below about permissions.

Mysql

# All users
SELECT user FROM mysql.user;

# All distinct users
SELECT DISTINCT User FROM mysql.user;

# Users and the hosts they can connect from
SELECT user,host FROM mysql.user;

# Users, hosts and encrypted passwords
SELECT user,host,password FROM mysql.user;

Change the password

Mysql

# Change the password for the logged in user
SET PASSWORD = PASSWORD('new_password');

# Change password for a specific user
SET PASSWORD FOR 'jessica'@'localhost' = PASSWORD('new_password');

List privileges

Use show grants to check permissions.

Title

# Display a user's privileges
SHOW GRANTS FOR 'jessica'@'localhost';

# Display the privileges of the logged in user
SHOW GRANTS;

Grant privileges

The grant command is responsible  for administering the privileges given to users.

Mysql

# Create the user jessica with 'my_massword' and
# grants full access to the db_store database
GRANT ALL ON db_store.*
  TO 'jessica'@'localhost'
  IDENTIFIED BY 'my_password';

# Grants full privilege to jessica on all tables in
# the db_store database using his current password
GRANT ALL ON db_store.*
  TO 'jessica'@'localhost';

# Grants only SELECT to jessica in the
# customers table in the db_store database
GRANT SELECT
  ON db_store.customers
  TO 'jessica'@'localhost';

Remove privilege

The revoke command remove user's permissions.

Mysql

# Removes INSERT privilege on all
# databases and tables for user jessica
REVOKE INSERT
  ON *.*
  FROM 'jessica'@'localhost';

# Removes DELETE privilege on db_store database
# and its tables for user jessica
REVOKE DELETE
  ON db_store.*
  FROM 'jessica'@'localhost';

# Removes all privileges from user jessica
REVOKE ALL PRIVILEGES, GRANT OPTION
  FROM 'jessica'@'localhost';

Privilege list

List of some of the privileges that can be used with the grant and revoke commands.

See all available privileges here.

Sources

• https://dev.mysql.com/doc/refman/8.0/en/
• https://phoenixnap.com/kb/mysql-show-user-privileges

This is not my original language and I don't speak it very well. I used my little knowledge and translators tools to compose the text of this article. Sorry for possible spelling or grammatical errors, suggestions for corrections are appreciated and can be sent to the contact email in the footer of the site. My intention is to share some knowledge and I hope this translation is good enough.