Create Users and Grant Permissions in MySQL

Published about 7 years ago

---

Creating a New User

Lets open the MySQL shell and create a new user.

mysql -u root -p
CREATE USER 'jimmy'@'localhost' IDENTIFIED BY 'password123';

When we first create a user, it will have no permissions, which means it can even login to the MySQL shell, so we need to give it the needed permissions.

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

The first asterisk in this command refers to the database, in this case we are giving it access to all of them. The second asterisk refers to the tables in said databases, this jimmy user will have access to all of those as well.

Once we have finalized the permissions we need, we need to refresh the shell to update our recent changes.

FLUSH PRIVILEGES;

Our changes are now updated

Granting Different Users Permissions

Here is a list of available permissions any given user can have.

ALL PRIVILEGES # Access to everything
CREATE # Create new tables/databases
DROP #Delete tables/databases
DELETE #Delete rows from tables
INSERT # Insert rows into tables
SELECT # Read database rows
UPDATE # Update table rows
GRANT OPTION # Grant or remove other users' privileges

To give a certain user permissions, use the following syntax.

GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;

You can put an asterisk for any of these to give them access to everything. Don't forget also the flush the privileges afterwards for to update recent changes.

To revoke a given user's permission, it is identical to granting them.

REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘localhost’;

If you wish to delete a user, just drop them.

DROP USER ‘jimmy’@‘localhost’;

You can test out whether or not a user was created or dropped successfully by quitting and then logging in as them.

quit
mysql -u [username] -p

Changing Root Password

If you want root to use a blank password or change it completely, on the most recent versions of MySQL, they changed how to alter the password.

mysql -u root

Then change the database and password.

use mysql;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
flush privileges;
quit;

After doing this, stop and start your MySQL server.

Older versions of MySQL may use this syntax to update the password.

First stop your mysql server, then run the following command to start it in safe mode.

sudo mysqld_safe --skip-grant-tables &

Then change the password.

use mysql;
UPDATE USER SET authentication_string=PASSWORD("new_password") WHERE User='root';
flush privileges;
quit;

Now stop and restart your MySQL server and you should be good.