MySQL How to Add a User and Grant Permissions

Adding a user is easy with MySQL. We are going to cover this as well as granting permissions.

Create a user like this:

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

Grant all permissions to all tables on all databases like this:

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

Flush the privileges to make them active:

FLUSH PRIVILEGES;

Privileges need to be flushed every time a permissions change is made in order to put the changes into effect.

Note that in production you probably wouldn’t want to just grant all permissions to everything for a new user.

You can see what permissions a particular user has like this:

SHOW GRANTS FOR 'user2'@'localhost';

You can grant and revoke permissions with the following syntax:

GRANT permission_type ON database.table TO user@host;
REVOKE permission_type ON database.table FROM user@host;

For example you could use either of these commands:

GRANT SELECT ON supplies.food TO user2@localhost;
REVOKE SELECT ON supplies.food FROM user2@localhost;

These are the types of privileges that can be granted or revoked:

ALL PRIVILEGES	all access to everything
CREATE	create new databases and tables
DROP	delete databases and tables
SELECT	query a database with select
INSERT	insert rows into a table
DELETE	delete rows from tables
UPDATE	update rows in a table
GRANT OPTION	add or remove permissions for other users

You can remove a user like this:

DROP USER 'user2'@'host';

To test out a newly added user you can exit and log back in as that user like this:

quit
mysql -u user2 -p