Difference between revisions of "How to Add a User in MySQL"

Revision as of 18:28, 16 May 2014

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 any host

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

Or even better from just a specific ipaddress

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

The last one is for remote applications connecting to your database.

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