CODEWITHSUNDEEP
mysqlsql
roeygol
roeygol

Reputation: 5048

Create new Mysql user which can be accessed from any end point

I need to create new user on my Mysql database. The user should be a accessed from any end point and have all privileges.

Actually, I can't understand what is the meaning of the host column in my mysql.user table.

Which sql statement I need to write for this?

Thanks!!

Upvotes: 2

Views: 326

Answers (1)

gipinani
gipinani

Reputation: 14418

The first thing you have to check is that your mysql server can receive connection from the outside. So check in my.ini config file the value of bind-address property.

Tipically you have to change it from 

bind-address=127.0.0.1

to

bind-address=0.0.0.0

Then restart your server.

Now you can add your user specifying the wildcard character % for host column. This allow your specified user from any host to your setted privileges.

Then you can create your user: 

CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';
CREATE USER 'newuser'@'192.168.%.%' IDENTIFIED BY 'password';

and grand privileges on schemas/tables:

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

Instead of grant ALL privileges, you can grant:

CREATE - allows them to create new tables or databases

DROP - allows them to them to delete tables or databases

DELETE - allows them to delete rows from tables

INSERT - allows them to insert rows into tables

SELECT - allows them to use the Select command to read through databases

UPDATE - allow them to update table rows

GRANT OPTION - allows them to grant or remove other users' privileges

At the end flush your privileges: FLUSH PRIVILEGES;

The general syntax of GRANT is:

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

You can use a tool like MySql Workbench to simplify this task.

Hope this helps.

Upvotes: 1

Related Questions