How to grant select on specific table in mysql

MysqlHow-ToBeginner · 3 min read

How to Grant SELECT Permission on a Specific Table in MySQL

Use the GRANT SELECT ON database_name.table_name TO 'user'@'host'; command to give a user permission to read data from a specific table in MySQL. Replace database_name, table_name, user, and host with your actual database, table, username, and host values.

📐

Syntax

The basic syntax to grant SELECT permission on a specific table is:

GRANT SELECT: Gives read-only access to the table.
ON database_name.table_name: Specifies the exact table in the database.
TO 'user'@'host': Defines the user and the host from which they connect.
;: Ends the SQL statement.

sql

GRANT SELECT ON database_name.table_name TO 'user'@'host';

💻

Example

This example grants SELECT permission on the employees table in the company database to the user reporter connecting from any host.

sql

GRANT SELECT ON company.employees TO 'reporter'@'%';

-- To apply changes immediately
FLUSH PRIVILEGES;

Output

Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec)

⚠️

Common Pitfalls

Common mistakes when granting SELECT on a specific table include:

Forgetting to specify the database name before the table name.
Using incorrect user or host values, which prevents the user from connecting.
Not running FLUSH PRIVILEGES; after changes in some MySQL versions (though usually not needed).
Granting permissions without considering security risks of 'user'@'%' which allows connections from any host.

sql

/* Wrong: Missing database name */
GRANT SELECT ON employees TO 'reporter'@'%';

/* Right: Include database name */
GRANT SELECT ON company.employees TO 'reporter'@'%';

📊

Quick Reference

Part	Description	Example
GRANT SELECT	Gives read-only access	GRANT SELECT
ON database.table	Specifies the exact table	ON company.employees
TO 'user'@'host'	Defines user and host	TO 'reporter'@'%'
FLUSH PRIVILEGES	Reloads privilege tables	FLUSH PRIVILEGES;

✅

Key Takeaways

Use GRANT SELECT ON database.table TO 'user'@'host'; to give read access to a specific table.

Always specify the database name before the table name to avoid errors.

Be careful with the host part; '%' allows access from any host, which may be insecure.

Running FLUSH PRIVILEGES is sometimes needed to apply changes immediately.

Check user and host values carefully to ensure the user can connect and access the table.