mySQL (mariaDb) Useful User Management Via Command Line

Here is a collection of useful and common mysql commands for managing database users using the command line, perhaps whilst logged in to the server via ssh. The following commands work for MySql and mariaDb. Don’t forget to include the semi-colon at the ends of each command.

View Existing MySql Users
SELECT User,Host FROM mysql.user;

Create a New MySql User
CREATE USER ‘username’@’hostname’ IDENTIFIED BY ‘password’;

Remove Existing MySql User
DROP USER ‘username’@’hostname’;

Privileges

Set/Grant Privileges To Existing MySql User
GRANT [permission] ON databaseName . * TO ‘username’@’hostname’;

As we are using the wildcard selector *, that will apply to all tables. You could replace the * with a specific table name.

Remove Specific Permission/Privilege From Existing User
REVOKE [permission] ON databaseName.tableName FROM ‘username’@’hostname’;

Where [permission] could be one of:

  • ALL PRIVILEGES
  • CREATE
  • DELETE
  • DROP
  • GRANT OPTION
  • INSERT
  • SELECT
  • UPDATE
  • ALTER

(See this full list of privileges)

You can comma-separate the privileges if you wish to set more than one, e.g.

GRANT SELECT,INSERT ON databaseName.tableName TO ‘username’@’hostname’;

Reload the privileges:

FLUSH PRIVILEGES;

Change Password for Existing User

SET PASSWORD FOR ‘username’@’hostname’ = PASSWORD(‘new-password’);
FLUSH PRIVILEGES;

Facebook Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Apply your human brain cells and complete this highly complicated maths problem *