How to create a new MySQL user and grant privileges to that user

In the article below you will be creating a user and giving them full permissions to a specific database. This should only be done for users that you trust as they would have full access to the content of that database. Typically the ability of creating users and granting privileges can only be done by the root/admin user and is done via SSH or Remote Desktop. 

Keep in mind any underlined words may need to be changed to match your specific requirements as shown below in the full example. All other items are necessary.

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON database.table TO 'user'@'localhost';

Now with a full example below.

In this example my user is george and my password is abcd1234

Georges public IP that he connects from is

I’m granting him access to the jungle database and all it's tables.

CREATE USER 'george'@'' IDENTIFIED BY 'abcd1234';

GRANT ALL PRIVILEGES ON jungle.* TO 'george'@'';

You can also grant a user permissions to all databases by putting a *.* where "jungle.*" would be, or give them access to a specific table within a database by specifying it like so: jungle.specificDB

If you wish for him to only connect to this from within you may put this instead:

GRANT ALL PRIVILEGES ON jungle.* TO 'george'@'localhost';

If you wanted to only give the george user read only access to all of the tables within the jungle database, this would be done as shown below:

GRANT SELECT ON jungle.* TO 'george'@'' IDENTIFIED BY 'abcd1234';

Further information on granting privileges and what certain privileges can do can be found at MySQL's Documentation page.

Posted in
Last update:
2016-05-27 17:50
Average rating:0 (0 Votes)