MySQL: Adding a user

6/1/2017 - 3:57 PM

Adding a user to a MySQL Database

Create user

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

The 'password' is the identifier that a user needs to type to get into the mysql shell.

Type the following commands if you have MySQL 5.7.6 and later or MariaDB 10.1.20 and later:

ALTER USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Type the following commands if you have MySQL 5.7.5 and earlier or MariaDB 10.1.20 and earlier:

SET PASSWORD FOR 'newuser'@'%' = PASSWORD('password');

Afterwards, run Flush Privileges.

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';

First asterisk refers to a database, the second asterisk refers to tables.

Privileges can include: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP

In order to give a user permission to grant other permissions (they can only grant permissions that they have):

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' WITH GRANT OPTION;

To see the grants for a user:

#for any user
SHOW GRANTS FOR 'root'@'localhost';

#for current user
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER()

select user, host, password from mysql.user;

The following command reloads the privileges:

FLUSH PRIVILEGES;