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.
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;
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
See all available privileges here.