How to grant remote access to MySQL for a whole subnet

Question

I can easily grant access to one IP using this code     mysql -u root -p Enter password      mysql gt  use mysql     mysql gt  GRANT ALL ON     to root  192 168 1 4  IDENTIFIED BY  your-root-password        mysql gt  FLUSH PRIVILEGES    But i need to allow the whole subnet 192 168 1   to access the database remotely   How can i do that

User · Answer

It looks like you can also use a netmask  e g   GRANT     TO  user   192 168 0 0 255 255 255 0  IDENTIFIED BY

User · Answer

MySQL 8 0 23 onwards now support CIDR notation also  So  basically  -- CIDR Notation GRANT     TO  user   192 168 1 0 24  IDENTIFIED BY      -- Netmask Notation GRANT     TO  user   192 168 1 0 255 255 255 0  IDENTIFIED BY

User · Answer

Just a note of a peculiarity I faced  Consider   db server   192 168 0 101 web server  192 168 0 102   If you have a user defined in mysql user as  user   192 168 0 102  with password1 and another  user   192 168 0    with password2   then    if you try to connect to the db server from the web server as  user  with password2   it will result in an  Access denied  error because the single IP   user   192 168 0 102  authentication is used over the wildcard  user   192 168 0    authentication

User · Answer

You would just use     as your wildcard like this   GRANT ALL ON     to root  192 168 1    IDENTIFIED BY  your-root-password

User · Answer

after you connect server and you want to connect on your host  you should do the steps below    write mysql to open mysql write GRANT ALL ON   to root  write your ip addres  IDENTIFIED BY  write password to connect   press control and X to quit from mysql write nano  etc mysql my cnf  write   before bind-address   127 0 0 1 in my cnf folder  bind-address             127 0 0 1  save my cnf folder with control   X write service mysql restart you could connect via navicat on your host

User · Answer

Motivated by  Malvineaus answer I tried it myself and noticed that it didn t work for me  You can specify subnet masks with  192 168 1    or  192 168 1 0 255 255 255 0  but the subnet must always be on complete octets  see https   mariadb com kb en create-user  host-name-component  As result the functionality between one way of specification and the other is the same  For example  192 168 1 0 255 255 255 128  will not work as it is not on a complete octet boundary

User · Answer

EDIT  Consider looking at and upvoting Malvineous s answer on this page  Netmasks are a much more elegant solution     Simply use a percent sign as a wildcard in the IP address   From http   dev mysql com doc refman 5 1 en grant html     You can specify wildcards in the host name  For example  user name    example com  applies to user name for any host in the example com domain  and user name  192 168 1    applies to user name for any host in the 192 168 1 class C subnet

User · Answer

mysql gt  GRANT ALL ON     to root  192 168 1    IDENTIFIED BY  your-root-password       The wildcard character is a     instead of an

[mysql] How to grant remote access to MySQL for a whole subnet?

Examples related to mysql

Examples related to grant