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

From Brian Nelson Ramblings
Jump to: navigation, search
(Created page with "==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 d...")
 
(How to Create/Add a User)
 
Line 11: Line 11:
 
  CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'theirpassword';
 
  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===
 
===GRANT permissions to your New User===

Latest revision as of 18:51, 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 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