How do I configure the privilege system so that I can use Navicat to access my remote server from my desktop?

From Navicat Wiki
Jump to: navigation, search

Apply OS : Windows, Mac, Linux

Apply Navicat Product : Navicat for MySQL, Navicat for PostgreSQL, Navicat for MariaDB, Navicat Premium

Apply Navicat Version No. : All


MySQL & MariaDB

Below is the steps that we'd like to assist you to use Navicat to access your remote MySQL server from your desktop:


  1. Please check your Internet connection's public IP address or IP range (To check IP address, please enter the command IPConfig in Windows "Dos" Prompt). For security concern, we would suggest you to set the host address with a fixed IP or IP range. Host IP example can be found in the following table.
    IP address or Host value of your desktop's Internet Connection Username Connections matched by entry
    'thomas.loc.gov' 'fred' fred, connecting remote MySQL server from his desktop "thomas.loc.gov"
    'thomas.loc.gov' '' Any user, connecting remote MySQL server from his desktop "thomas.loc.gov"
    '%' 'fred' fred, connecting from any host
    '%' '' Any user, connecting from any host
    '%.loc.gov' 'fred' fred, connecting remote MySQL server from any host in the loc.gov domain
    'x.y.%' 'fred' fred, connecting remote MySQL server from x.y.net, x.y.com,x.y.edu, etc. (this is probably not useful)
    '144.155.166.177' 'fred' fred, connecting remote MySQL server from the host with IP address 144.155.166.177
    '144.155.166.%' 'fred' fred, connecting remote MySQL server from any host in the 144.155.166 class C subnet
    '144.155.166.0/255.255.255.0' 'fred' Same as previous example


    If your remote MySQL server is running in Windows platform, we would suggest you to install Navicat in your remote Windows server. Inside Navicat, you can find a function called "Manage Users"/"Users". You can use Navicat's Manage Users Feature to add a new user in table of database.

    If you updated the privileges, you should execute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges or mysqladmin reload to tell the server to reload the grant tables. Otherwise, your changes will have no effect until you restart the server.


  2. If your remote server is running in Linux/Unix platform, please telnet to your remote server (or by SSH). Next, please logon your MySQL server. You can run the following command in your MySQL server command prompt. GRANT ALL PRIVILEGES ON *.* TO 'YourUserName'@'YourPublicIP' IDENTIFIED BY "YourPassword"; YourUserName is the username that you would like to create. YourPublicIP is the public IP address of your PC (can be IP range or use % to allow any host). YourPassword - You can setup a password for your account. Please remember to reload the new user privileges setting by running this command in mysql/bin directory of your Linux/Unix server: ./mysqladmin reload


Remark : Does your remote server support SSH connection? If the answer is yes, you can use Navicat to connect with your remote MySQL server via a SSH tunnel.


PostgreSQL

Below is the steps that we'd like to assist you to use Navicat to access your remote PostgreSQL server from your desktop:


By default, PostgreSQL only allows connections from the local machine using TCP/IP connections. Other machines will not be able to connect unless you modify listen_addresses in the postgresql.conf file, enable host-based authentication by modifying the $PGDATA/pg_hba.conf file, and restart the server. For more information: Client Authentication


If your remote PostgreSQL server is running in Windows platform, we would suggest you to install Navicat in your remote Windows server. Inside Navicat, you can find a function called "Manage Users"/"Users". You can use Navicat's Manage Users Feature to add a new user in table of database.


Remark : Does your remote server support SSH connection? If the answer is yes, you can use Navicat to connect with your remote PostgreSQL server via a SSH tunnel.