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!!
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.
