MySQL Grant Usage vs Grant Select: Which One to Use?

MySQL is a popular open source relational database management system used by developers worldwide. It provides various privileges and options for granting permissions to users for accessing the database. The two primary privileges are ‘GRANT USAGE’ and ‘GRANT SELECT’. But what are they, and what distinguishes them from one another? In this blog post, we will discuss the differences between ‘GRANT USAGE’ and ‘GRANT SELECT’, when to use them, and how to grant them to users.

What is ‘GRANT USAGE’?

‘GRANT USAGE’ is one of the MySQL privileges available for granting to users. When a user is granted ‘GRANT USAGE’, it means the user has permission to execute specific SQL statements such as ‘SHOW DATABASES’, ‘SHOW TABLES,’ and ‘SHOW CREATE TABLE’. In other words, the user can access the metadata of the database but cannot access or modify the data. Usage privileges are useful when you want to provide a user with access to the database schema or structure, enabling them to view tables, column details, and indexes without reading data from the database. Granting ‘GRANT USAGE’ to a user does not provide them with table-level privileges but enables them to interact with the database schema.

What is ‘GRANT SELECT’?

On the other hand, ‘GRANT SELECT’ is a database privilege that grants users select privileges to fetch data from tables within a database. It allows the user to read the data from a table or column but not insert, update, or delete any data in the database. ‘GRANT SELECT’ is commonly used to grant users read-only access to a specific table or a set of tables. This type of user can be beneficial when you don’t want users to modify data, but they might need access to read the data for reporting purposes.

When to Use GRANT USAGE and GRANT SELECT

So when should you use ‘GRANT USAGE’ or ‘GRANT SELECT’ privileges? Both privileges are types of database permissions, but they have different use cases. Use ‘GRANT USAGE’ when:

• You want to grant a user access to the database schema or metadata
• You don’t want the user to be able to read or modify data in the database
• You want to allow the user to execute ‘SHOW’ statements

Use ‘GRANT SELECT’ when:

• You want to grant read-only access to specific tables or columns
• You want to provide users with the ability to perform queries for reporting purposes

It should be noted that when a user is granted ‘GRANT USAGE,’ they cannot run any SQL queries on the database. For this, they need SELECT privileges granted explicitly.

How to Grant Privileges in MySQL

To grant privileges in MySQL, you should log in to the MySQL server using a privileged account that has the ‘GRANT’ privilege to grant privileges to other users. Once logged in, use the ‘GRANT’ statement to grant necessary privileges.For example, to grant ‘GRANT USAGE’ to the user ‘tester’ on the database ‘testdb,’ you can run the following command:

GRANT USAGE ON testdb.* TO ‘tester’@’localhost’;

Similarly, to grant ‘GRANT SELECT’ privilege to the user ‘tester’ to select data from the ‘users’ table in the ‘testdb’ database, run the following command:

GRANT SELECT ON testdb.users TO ‘tester’@’localhost’;

By following this syntax, you can grant various privileges to users in the MySQL database.

Conclusion

Granting privileges to users has always been challenging, but MySQL has made it relatively simple with the help of ‘GRANT USAGE’ and ‘GRANT SELECT’ privileges. In summary, ‘GRANT USAGE’ is useful when you want to grant users access to the database schema or metadata. In contrast, ‘GRANT SELECT’ is useful when you want to provide users with read-only access to specific tables or columns. By understanding the difference between both, you can grant privileges that meet your specific needs.