March 21

MYSQL: Basic user setup

Make a new user:
CREATE USER ‘newuser’@’localhost’ IDENTIFIED BY ‘password’;

Grant privileges:
GRANT ALL PRIVILEGES ON * . * TO ‘newuser’@’localhost’;

Flush privileges:

How To Grant Different User Permissions
Here is a short list of other common possible permissions that users can enjoy.

ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)

CREATE- allows them to create new tables or databases

DROP- allows them to them to delete tables or databases

DELETE- allows them to delete rows from tables

INSERT- allows them to insert rows into tables

SELECT- allows them to use the Select command to read through databases

UPDATE- allow them to update table rows

GRANT OPTION- allows them to grant or remove other users’ privileges

Specific user permissions:
GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@’localhost’;

Revoke a user’s permissions
REVOKE [type of permission] ON [database name].[table name] TO ‘[username]’@‘localhost’;

Drop a user
DROP USER ‘username’@‘localhost’;

Test your user by exiting mysql
mysql -u username -p

By: digitalocean

Copyright 2021. All rights reserved.

Posted March 21, 2013 by Timothy Conrad in category "Databases

About the Author

If I were to describe myself with one word it would be, creative. I am interested in almost everything which keeps me rather busy. Here you will find some of my technical musings. Securely email me using - PGP: 4CB8 91EB 0C0A A530 3BE9 6D76 B076 96F1 6135 0A1B