Understanding MySQL Privileges in Linux

Preparing the MySQL Server: Root Accounts and Initial Setup

MySQL runs under its own system account, and it also has a special user called root that controls all database operations. On a typical Linux installation the OS root user is different from the MySQL root account. When you install the server the package often creates a MySQL root account with an empty password so you can log in locally without authentication. This is why you can connect with the command mysql -u root inside the same machine, even if the OS account is not root.

After the package finishes, it is a good habit to set a strong password for the MySQL root user. The easiest way to do this from a shell is to invoke the mysqladmin tool that lives in the bin directory of the MySQL installation. If you installed the server in /usr/local/mysql you would change to that directory and run:

cd /usr/local/mysql/bin

mysqladmin -u root password 'newstrongpassword'

This command updates the mysql.user table and forces future connections to require the password you supplied. If you see an “access denied” error when you try to connect after setting the password, the most common cause is that the server is still running with the old configuration or that the user you used to run mysqladmin does not have permission to modify the mysql database. A workaround that many sysadmins use is to start the server with privilege checks disabled, tweak the configuration, and then restart it normally.

To start MySQL without the privilege system you launch the mysqld binary with the --skip-grant-tables option. First stop the running service:

systemctl stop mysqld

Then start it manually:

mysqld_safe --skip-grant-tables &

Running in this mode means that any client that can reach the port can log in without a password and can issue any command. Because of that, you should only use it for a short period while you reset the root password or perform other maintenance. After you finish the changes, stop the server again and start it normally:

systemctl stop mysqld
systemctl start mysqld

With a clean password in place you can now use the GRANT statement to create other accounts. A tool that simplifies this process is MySQL‑Front, a lightweight graphical client that ships with a user manager interface. The application writes the necessary INSERT statements into the mysql.user, mysql.host, and mysql.db tables behind the scenes, so you can visualise the privileges you are assigning. Although the front‑end hides some of the complexity, it is still a good idea to understand the underlying tables, which are described in the next section.

On many distributions the mysql_secure_installation script is provided as a quick way to perform these tasks. Running sudo mysql_secure_installation will prompt you for the current root password, allow you to set a new one, remove anonymous users, disallow remote root logins, and remove the test database. The script also offers the option to reload the privilege tables, which is effectively the same as running FLUSH PRIVILEGES. The sequence of commands that the script executes can be found in the official documentation if you wish to replicate the process manually.

After you set the password, you can verify that the root account has the expected host entry. The mysql.user table should contain a row where user='root' and host='localhost'. If you have added a root entry for a network host, make sure that you immediately restrict it to the local machine, because leaving a network‑accessible root account is a common security hole.

Understanding the Three Key Tables: USER, HOST, and DB

When a client tries to connect to a MySQL server, the engine performs a three‑step lookup that begins with the mysql.user table. This table holds the authentication credentials and the list of hosts that the account may connect from. A row looks like:

SELECT user, host, authentication_string FROM mysql.user WHERE user='alice';

For the default installation you will find a row for root with host set to “localhost”, which restricts the account to local connections only. The host column can contain a hostname, an IP address, or a wildcard pattern such as ‘%’, which matches any host. If you want to allow a user named bob to connect from a specific server, you would insert a row:

INSERT INTO mysql.user (user, host, authentication_string, plugin) VALUES ('bob', '192.168.10.42', PASSWORD('bobpass'), 'mysql_native_password');

After the authentication phase MySQL looks up the privileges granted to the user for each database. The mysql.db table records global, database‑level, and table‑specific privileges. A typical row looks like:

SELECT Db, User, Host, Select_priv, Insert_priv, Update_priv FROM mysql.db WHERE User='bob';

The privilege columns are Y or N. The same privileges are also stored in mysql.tables_priv and mysql.columns_priv for table‑ and column‑level restrictions. Because the privilege system checks the tables in a specific order, a more specific rule that matches a particular host or database can override a broader rule that applies to all hosts or all databases. The algorithm is: the server first searches mysql.user, then mysql.db, then mysql.tables_priv, and finally mysql.columns_priv, using the first matching row it finds.

To see the full set of privileges a user has, you can run SHOW GRANTS FOR 'bob'@'192.168.10.42'; This statement prints the GRANT statements that MySQL would use to reproduce the current privileges. The output is useful when you want to copy an account to another server, or when you want to audit the access control. For example, the output might look like:

GRANT SELECT, INSERT, UPDATE ON `sales`. TO 'bob'@'192.168.10.42' IDENTIFIED BY PASSWORD 'C7F0F4E9...' WITH GRANT OPTION;

Here the account has been given SELECT, INSERT, and UPDATE privileges on every table in the sales database, and the WITH GRANT OPTION clause allows bob to grant those privileges to other accounts. The IDENTIFIED BY PASSWORD clause is a hint that the authentication_string column in mysql.user is stored as a hashed value rather than plaintext.

When you change a privilege you usually need to run FLUSH PRIVILEGES; to tell the server to reload the tables. Some clients do this automatically, but if you are making many changes from the command line it is a good practice to issue the command once after you finish. On most installations FLUSH PRIVILEGES is equivalent to restarting the MySQL service, because the privilege tables are cached in memory for the life of the server process.

Understanding these three tables is the key to mastering MySQL access control. The rest of the article focuses on how to fine‑tune access at the table and column levels, so that you can grant precisely the rights that a user needs without exposing unnecessary data.

Fine‑Tuning Access: Table and Column Level Controls

While the mysql.db table covers database‑wide permissions, many applications require a more granular approach. MySQL lets you specify permissions on individual tables and even on specific columns within those tables. The tables_priv and columns_priv tables hold these records. A typical entry in mysql.tables_priv might look like:

SELECT Db, Table_name, User, Host, Table_priv FROM mysql.tables_priv WHERE User='bob';

The Table_priv column contains a comma‑separated list of rights, such as ‘SELECT,INSERT,UPDATE’. If you only want bob to read from the orders table, you would insert:

INSERT INTO mysql.tables_priv (Db, Table_name, User, Host, Table_priv) VALUES ('sales', 'orders', 'bob', '192.168.10.42', 'SELECT');

Similarly, mysql.columns_priv lets you grant rights on individual columns. A record looks like:

SELECT Column_name, Column_priv FROM mysql.columns_priv WHERE User='bob' AND Table_name='orders' AND Db='sales';

To allow bob to modify the price column but keep the quantity column read‑only, you would insert:

INSERT INTO mysql.columns_priv (Db, Table_name, Column_name, User, Host, Column_priv) VALUES ('sales', 'orders', 'price', 'bob', '192.168.10.42', 'UPDATE');

The column privilege supersedes the table privilege for the specified column. If a user has UPDATE on a column, but not on the table itself, MySQL still permits the update because the column rule explicitly grants that right.

In practice, you rarely need to touch these tables manually. The GRANT command is the idiomatic way to set these privileges, and MySQL translates the command into the appropriate INSERT statements behind the scenes. For example, the command:

GRANT SELECT, UPDATE ON sales.orders(price) TO 'bob'@'192.168.10.42';

creates both a row in mysql.tables_priv for the orders table and a row in mysql.columns_priv for the price column. The optional parenthesised column list tells MySQL which columns are being granted, and if you omit it you grant rights on the whole table.

When using GRANT you can also specify the WITH GRANT OPTION clause to let the target user delegate privileges. This can be dangerous if the user is not trusted, so it is best used sparingly. For instance:

GRANT SELECT ON sales.* TO 'alice'@'%' WITH GRANT OPTION;

allows alice to create new users that can read every table in the sales database, including tables that are added in the future.

After you finish setting privileges, the changes take effect immediately. MySQL does not require a FLUSH PRIVILEGES after a GRANT, because the GRANT statement already updates the in‑memory cache. If you have made direct changes to the mysql tables, however, remember to run FLUSH PRIVILEGES; or restart the server.

Graphical tools such as MySQL‑Front can be helpful for visualising and editing table and column privileges. The application shows a hierarchical tree of databases, tables, and columns, and lets you tick checkboxes to enable or disable specific rights. Under the hood it issues the same GRANT or REVOKE statements that you would run on the command line. This can be a useful safety net when you need to double‑check that a privilege was applied correctly, especially when dealing with complex schemas.

In summary, the combination of database, table, and column privileges gives you the flexibility to enforce the principle of least privilege. By granting only the rights that an application or user truly needs, you reduce the attack surface and protect sensitive data. The next section lists a few practical tips that can help you avoid common pitfalls when working with MySQL privileges.

Practical Tips and Common Pitfalls

Managing privileges in a multi‑user environment can feel daunting, but a few simple habits keep the process predictable. First, always keep a recent backup of the mysql database before making large changes. The mysql schema stores all privilege data, so a corrupted user table can lock you out of the system. The easiest way to back it up is to copy the /var/lib/mysql/mysql directory or run mysqldump –all-databases –single-transaction > backup.sql

Second, avoid leaving the root account accessible from the network. The default MySQL configuration binds the server to localhost, but if you change the bind-address to 0.0.0.0 or a public IP you must immediately disable remote root logins. This can be done by setting the host field for root to ‘localhost’ only: