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

Latest revision as of 18:51, 16 May 2014

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

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';

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;

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

@@ Line 11: / Line 11: @@
   CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'theirpassword';
-Now you can also create it so that they access it from any host
+Now you can also create it so that they access it from 127.0.0.1
-  CREATE USER 'newuser'@'%' IDENTIFIED BY 'theirpassword';
+  CREATE USER 'newuser'@'127.0.0.1' 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===