Language
Category
Search

Managing users and privileges in mysql

How to add, remove, search for active users and their privileges, how to create passwords with expiring date or login with no password. See how to set permissions with databases and tables

Managing users and privileges in mysql
At Database By Rudi Drusian Lange
Published on
Last updated

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.

Privilégio

Coluna

Contexto

Create_priv
databases, tables, or indexes
Drop_priv
databases, tables, or views
Grant_priv
databases, tables, or stored routines
Lock_tables_priv
databases
References_priv
databases or tables
Event_priv
databases
Alter_priv
tables
Delete_priv
tables
Index_priv
tables
Insert_priv
tables or columns
Select_priv
tables or columns
Update_priv
tables or columns
Create_tmp_table_priv
tables
Trigger_priv
tables
Create_view_priv
views
Show_view_priv
views
Alter_routine_priv
stored routines
Create_routine_priv
stored routines
Execute_priv
stored routines
File_priv
file access on server host
Create_tablespace_priv
server administration
Create_user_priv
server administration
Process_priv
server administration
see proxies_priv table
server administration
Reload_priv
server administration
Repl_client_priv
server administration
Repl_slave_priv
server administration
Show_db_priv
server administration
Shutdown_priv
server administration
Super_priv
server administration
 
server administration
 
server administration

See all available privileges here.

Sources

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.