How to grant read only permissions to a user in MySQL?

In MySQL, to grant read-only permissions to users, you can use the GRANT statement. This permission typically means that users can execute SELECT queries but cannot perform any operations that modify data or database structure, such as INSERT, UPDATE, DELETE, or CREATE.

1. Log in to the MySQL server: First, you need to log in to the MySQL server using an account with sufficient privileges. Typically, this account should be root or another account with privileges to authorize users.

sql
mysql -u root -p

1. Create or manage database users: If the user does not exist, you need to create a new user first. If the user already exists, you can directly grant permissions.

The command to create a new user is:

sql
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Here, username is the username, and host specifies the host from which the user connects. Typically, localhost is used for local connections. If the user needs to connect from any host, use %.

1. Grant read-only permissions: Granting read-only permissions primarily involves granting the SELECT privilege. This can be applied to a specific database or to all databases.

• Grant read-only permissions to a specific database:

sql
GRANT SELECT ON database_name.* TO 'username'@'host';

• To grant read-only permissions to all databases, you can use:

sql
GRANT SELECT ON *.* TO 'username'@'host';

Here, replace database_name with the specific database name.

1. Refresh privileges: After granting permissions, run the FLUSH PRIVILEGES; command to make the permissions take effect immediately.

sql
FLUSH PRIVILEGES;

1. Test permissions: To confirm that the user permissions are correctly set, log in with the newly authorized user and attempt to execute a query and a write operation.

sql
mysql -u username -p

Query test:

sql
SELECT * FROM database_name.table_name;

Write operation test (which should be denied):

sql
INSERT INTO database_name.table_name(column1) VALUES ('value1');

By following these steps, you can effectively set up read-only permissions for MySQL users. This permission management is crucial for ensuring data security and integrity, especially in multi-user environments.