How to Add a User in MySQL

From Brian Nelson Ramblings
Jump to: navigation, search

How to Add a User in MySQL Database

To help keep your applications from accessing every database you will want to create separate users with various permissions to your databases.

How to Create/Add a User

For all of the following commands you will want to be logged into mysql with the root user.

Creating the user is pretty straight forward...

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

Now you can also create it so that they access it from 127.0.0.1

CREATE USER 'newuser'@'127.0.0.1' IDENTIFIED BY 'theirpassword';

GRANT permissions to your New User

Give them the entire farm

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

You will want to use the same TO that you used above

Then activate the new privileges

FLUSH PRIVILEGES;

Now I like to let them only have specific actions on the farm

example:

GRANT INSERT,SELECT,UPDATE on database . * to 'newuser'@'localhost';
FLUSH PRIVILEGES;

Basic Syntax for GRANT

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

Each time you update or change a permission be sure to use the Flush Privileges command