How to grant remote access permissions to mysql server for user?

Question

If I do SHOW GRANTS in my mysql database I get

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' 
    IDENTIFIED BY PASSWORD 'some_characters' 
    WITH GRANT OPTION

If I am not mistaken, root@localhost means that user root can access the server only from localhost. How do I tell MySQL to grant root the permission to access this mysql server from every other machine (in the same network), too?

Simple change `localhost` to `%` will grant root access to public network. Don't forget to `flush privileges;` — , Aug 01 '13 at 01:39
Adding root@% exposes you to a lot of risk and should not be done. I know this is an old post but that warning should be here. The root account is the first account targeted by attackers, and root@'%' means the root user can connect to your MySQL account from anywhere it likes. There is too much to cover in comments, but you should try to remove as many users@'%' as possible, and any that are declared at that should have very limited privileges. No matter what, you should definitely not add root@'%', and your applications should not expect that account to be present either. — Damon, Feb 04 '14 at 23:08
See also [Enable remote MySQL connection](http://stackoverflow.com/questions/8380797/enable-remote-mysql-connection) — user, Mar 10 '14 at 03:53
Hey folks, should remove the PASSWORD string to this command ... it is causing an error ... just finish with IDENTIFIED BY and then the password itself. — Mário de Sá Vera, May 24 '17 at 17:36

Michael Berkowski · Answer 1 · 2014-09-08T19:35:44.807

153

This grants root access with the same password from any machine in *.example.com:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%.example.com' 
    IDENTIFIED BY 'some_characters' 
    WITH GRANT OPTION;
FLUSH PRIVILEGES;

If name resolution is not going to work, you may also grant access by IP or subnet:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.%'
    IDENTIFIED BY 'some_characters'  
    WITH GRANT OPTION;
FLUSH PRIVILEGES;

MySQL GRANT syntax docs.

edited Sep 08 '14 at 19:35

answered Jun 04 '11 at 20:17

Michael Berkowski

267,341
46
444
390

17

I assume `...TO 'root'@'%' IDENTIFIED...` would grant access from everywhere? – Aufwind Jun 04 '11 at 20:21
4

I executed the command in mysql, but `SHOW GRANTS` still shows the same (as in my question). I even did `FLUSH PRIVILGES`. Is there something I miss? – Aufwind Jun 04 '11 at 20:33
@Aufwind I wouldn't exepct you to need anything else besides `FLUSH PRIVILEGES`. Logout and back in, restart the mysqld service if you have access. Also, be sure that the `skip networking` line is not enabled in your `my.cnf` though that would not account for your `SHOW GRANTS` still not being the same. – Michael Berkowski Jun 04 '11 at 22:57
@Aufwind Failing that, you may need to ask over at ServerFault.com instead. – Michael Berkowski Jun 04 '11 at 22:58
10

Note that `IDENTIFIED BY PASSWORD` is accompanied by a hashed 41-digit hexadecimal number. Use `IDENTIFIED BY 'password'` if you want to include the password directly. – Rainulf May 28 '13 at 23:44
@Aufwind @MichaelBerkowski - `SHOW GRANTS;` only shows the grants for the logged in user (eg: root@localhost). If you login as root@example.com and execute `SHOW GRANTS;` from there, it won't show you root@localhost, but it will show you root@%.example.com To list users you want something like `select user,host,grant_priv from mysql.user;` – bobpaul Oct 31 '13 at 20:28
Please also **make sure** that `bind-address` is properly set to a network address (*my.conf*). Mysql defaults to 127.0.0.1 which is not accessible to remotes. – Thiago Macedo May 01 '14 at 14:07
I got the error: `ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number`, so I had to first run this command: `select password('some_characters');` then copy/paste the outputted hexadecimal number into the `IDENTIFIED BY PASSWORD` line for it to work. Everything worked great, thanks!! – prograhammer Jul 08 '14 at 15:39
I preferred @moshe-beeri's answer below for quick-and-dirty, but this answer is obviously better for use in production. – einnocent Aug 21 '14 at 17:46

score 100 · Answer 2 · edited Dec 11 '14 at 10:58

100

Try:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'Pa55w0rd' WITH GRANT OPTION;

edited Dec 11 '14 at 10:58

George G

7,443
12
45
59

answered Apr 04 '13 at 20:41

moshe beeri

2,007
1
17
25

1

umm what is `Pa55w0rd` seems stupid, don't tell me thats the password you want to make. For everybody who tries to login your mysql? – SSpoke Apr 06 '14 at 04:33
26

It just commonly used to give a place holder to password, saying "put your read good password in this place". – moshe beeri Apr 10 '14 at 12:45
3

This command with the usual caveat that you'd only want to use it for applications where security is not of concern, e.g. dev or QA. – einnocent Aug 21 '14 at 17:44
if its not working on mac try to change 'root'@'%' with 'root'@'localhost' or /etc/hosts name of the computer you are granting privileges to. – moshe beeri Sep 10 '15 at 11:28
2

Do not forget to run this after: ```FLUSH PRIVILEGES;``` – dane Nov 27 '15 at 19:25

score 49 · Answer 3 · edited Mar 31 '21 at 13:58

49

You need to take some steps to make sure first mysql and then root user is accessible from outside:

Disable skip-networking in my.cnf (i.e: /etc/mysql/my.cnf)
Check value of bind-address in my.cnf, if it's set to 127.0.0.1, you can change it to 0.0.0.0 to allow access from all IPs or whatever ip that you want to connect from.

Grant remote access the root user from any ip (or specify your ip instead of %)

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'
    IDENTIFIED BY 'your_root_password'
    WITH GRANT OPTION;
FLUSH PRIVILEGES;

Restart mysql service:
```
sudo service mysql restart
```

edited Mar 31 '21 at 13:58

Wang Liang

4,244
6
22
45

answered Nov 27 '14 at 10:47

Khashayar

1,321
10
9

You also need to open mysql port 3306 from the firewall otherwise remote clients won't connect – David Okwii Jun 15 '17 at 07:57
Just a quick notice on point 2 from @Khashayar: 0.0.0.0 means any IPv4 address. * means ANY address, be it IPv4 or IPv6. – Gustavo Pinsard Jun 12 '19 at 02:08
the location is now `/etc/mysql/mysql.conf.d/mysql.conf`. – Eoop Xoeno May 05 '22 at 08:55

Wasim A. · Answer 4 · 2020-01-02T12:36:27.343

38

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' 
    IDENTIFIED BY 'YOUR_PASS' 
    WITH GRANT OPTION;
FLUSH PRIVILEGES;

*.* = DB.TABLE you can restrict user to specific database and specific table.

'root'@'%' you can change root with any user you created and % is to allow all IP. You can restrict it by changing %.168.1.1 etc too.

If that doesn't resolve, then also modify my.cnf or my.ini and comment these lines

bind-address = 127.0.0.1 to #bind-address = 127.0.0.1
and
skip-networking to #skip-networking

Restart MySQL and repeat above steps again.

Raspberry Pi, I found bind-address configuration under \etc\mysql\mariadb.conf.d\50-server.cnf

edited Jan 02 '20 at 12:36

answered Dec 25 '14 at 07:04

Wasim A.

9,660
22
90
120

2

Only answer that explains the `*.*` and `'user'@'address'` part. Thanks! :) – Philipp Aug 28 '17 at 19:19
Granting ALL on *.* invites hackers in. – Rick James Nov 13 '17 at 18:35
that's why I explained \*.\* – Wasim A. Dec 14 '19 at 18:42

Mehdi Golzadeh · Answer 5 · 2020-03-02T12:35:46.653

By mysql 8 and later version, you cannot add a user by granting privileges. it means with this query:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' 
    IDENTIFIED BY 'type-root-password-here' 
    WITH GRANT OPTION;
FLUSH PRIVILEGES;

mysql will return this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'written password' at line 1

this means you don't have a root user for % domain. so you need to first insert the user and then grant privileges like this:

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'your password';
Query OK, 0 rows affected (0.11 sec)

mysql> GRANT ALL ON *.* TO 'root'@'%';
Query OK, 0 rows affected (0.15 sec)

mysql> FLUSH PRIVILEGES;

Dont forget to replace passwords with your specific passwords.

It throws an error: ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'%' — Package.JSON, Dec 20 '20 at 18:22
can we do this fo already created user ? for that it gives access deined error — Heemanshu Bhalla, Jun 21 '23 at 12:22

score 8 · Answer 6 · answered Jun 26 '13 at 20:23

Those SQL grants the others are sharing do work. If you're still unable to access the database, it's possible that you just have a firewall restriction for the port. It depends on your server type (and any routers in between) as to how to open up the connection. Open TCP port 3306 inbound, and give it a similar access rule for external machines (all/subnet/single IP/etc.).

score 5 · Answer 7 · edited May 11 '23 at 10:17

5

Two steps:

set up user with wildcard:

create user 'root'@'%' identified by 'some_characters'; 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' 
    IDENTIFIED BY PASSWORD 'some_characters' 
    WITH GRANT OPTION

vim /etc/my.cnf

add the following:

bind-address=0.0.0.0

restart server, you should not have any problem connecting to it.

edited May 11 '23 at 10:17

RiggsFolly

93,638
21
103
149

answered Nov 29 '17 at 17:12

studio1057

147
2
5

Following along, getting "ERROR 1827 (HY000): The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function." Could you explain what I'm getting wrong here? – lampShadesDrifter Jul 24 '19 at 00:41

score 4 · Answer 8 · edited Dec 10 '18 at 18:56

4

Open the /etc/mysql/mysql.conf.d/mysqld.cnf file and comment the following line:

#bind-address = 127.0.0.1

edited Dec 10 '18 at 18:56

Shubham

2,847
4
24
37

answered Feb 21 '18 at 13:25

Vik2696

347
2
7

Working on Ubuntu 19.4 – Ngô Văn Thao Apr 17 '20 at 13:05

score 3 · Answer 9 · edited Oct 26 '15 at 01:12

In my case I was trying to connect to a remote mysql server on cent OS. After going through a lot of solutions (granting all privileges, removing ip bindings,enabling networking) problem was still not getting solved.

As it turned out, while looking into various solutions,I came across iptables, which made me realize mysql port 3306 was not accepting connections.

Here is a small note on how I checked and resolved this issue.

Checking if port is accepting connections:

telnet (mysql server ip) [portNo]

-Adding ip table rule to allow connections on the port:

iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT

-Would not recommend this for production environment, but if your iptables are not configured properly, adding the rules might not still solve the issue. In that case following should be done:

service iptables stop

Hope this helps.

score 1 · Answer 10 · answered Feb 08 '20 at 14:48

Ubuntu 18.04

Install and ensure mysqld us running..

Go into database and setup root user:

sudo mysql -u root
SELECT User,Host FROM mysql.user;
DROP USER 'root'@'localhost';
CREATE USER 'root'@'%' IDENTIFIED BY 'obamathelongleggedmacdaddy';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
exit;

Edit mysqld permissions and restart:

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

# edit the line to be this:
bind-address=0.0.0.0

sudo systemctl stop mysql
sudo systemctl start mysql

From another machine, test.. Obvs port (3306) on mysqld machine must allow connection from test machine.

mysql -u root -p -h 123.456.789.666

All the additional "security" of MySql doesn't help security at all, it just complicates and obfuscates, it is now actually easier to screw it up than in the old days, where you just used a really long password.

score 0 · Answer 11 · answered Mar 07 '16 at 11:20

This worked for me. But there was a strange problem that even I tryed first those it didnt affect. I updated phpmyadmin page and got it somehow working.

If you need access to local-xampp-mysql. You can go to xampp-shell -> opening command prompt.

Then mysql -uroot -p --port=3306 or mysql -uroot -p (if there is password set). After that you can grant those acces from mysql shell page (also can work from localhost/phpmyadmin).

Just adding these if somebody find this topic and having beginner problems.

How to grant remote access permissions to mysql server for user?

11 Answers11

Linked

Related