Master MySQL Permissions: Granting Access, Skip-Grant Tables, and Security Best Practices

Grant Remote Access to MySQL

Steps to allow a user (e.g., root) to connect from any host using graphical tools such as Navicat.

Update the mysql.user table so the host column for the user is %, which matches any IP address.

Grant the desired privileges. For full access use:

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

Reload the privilege tables so the changes take effect: FLUSH PRIVILEGES; After executing these statements the user can connect remotely with any MySQL client.

Why Connections May Still Work Without These Steps

If the server is started with the skip-grant-tables option in my.cnf, the entire MySQL privilege system is disabled. This allows any client to connect without authentication. Removing or commenting out this line restores normal permission checks.

MySQL Permission System Overview

The permission system operates in two stages:

Stage 1: The server checks whether the incoming connection is allowed based on the user table (host and user).

Stage 2: If the connection is permitted, the server checks which operations the authenticated user may perform.

Key system tables that store privileges: mysql.user – global (super‑user) privileges and host filtering. mysql.db – database‑level privileges. mysql.host – rarely used; not affected by GRANT/REVOKE. mysql.tables_priv – table‑level privileges. mysql.columns_priv – column‑level privileges.

Any change to these tables requires FLUSH PRIVILEGES (or a server restart) to refresh the in‑memory cache.

Privilege Levels

Global level – stored in mysql.user. Example: GRANT ALL ON *.* TO 'root'@'%'; Database level – stored in mysql.db (and optionally mysql.host). Example: GRANT SELECT, INSERT ON db_name.* TO 'user'@'host'; Table level – stored in mysql.tables_priv. Example: GRANT UPDATE ON db_name.tbl_name TO 'user'@'host'; Column level – stored in mysql.columns_priv. Example:

GRANT SELECT (col_name) ON db_name.tbl_name TO 'user'@'host';

Stored routine level – applies to functions or procedures (GRANT syntax not shown).

Best Practices

Avoid using wildcards such as % for hosts or *.* for databases for non‑administrative accounts. Over‑permissive privileges increase the risk of unauthorized data access.