[mysql] MySql : Grant read only options?

I have a user, whom I want to grant all the READ permission on a db schema.

One way is this :

GRANT SELECT, SHOW_VIEW  ON test.* TO 'readuser'@'%';

Is there a way to group all read operations in grant ?

Note for MySQL 8 it's different

You need to do it in two steps:

CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'some_strong_password';
GRANT SELECT, SHOW VIEW ON *.* TO 'readonly_user'@'localhost';
flush privileges;

GRANT SELECT ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password';

This will create a user with SELECT privilege for all database including Views.

Even user has got answer and @Michael - sqlbot has covered mostly points very well in his post but one point is missing, so just trying to cover it.

If you want to provide read permission to a simple user (Not admin kind of)-

GRANT SELECT, EXECUTE ON DB_NAME.* TO 'user'@'localhost' IDENTIFIED BY 'PASSWORD';

Note: EXECUTE is required here, so that user can read data if there is a stored procedure which produce a report (have few select statements).

Replace localhost with specific IP from which user will connect to DB.

Additional Read Permissions are-

• SHOW VIEW : If you want to show view schema.
• REPLICATION CLIENT : If user need to check replication/slave status. But need to give permission on all DB.
• PROCESS : If user need to check running process. Will work with all DB only.

If you want the view to be read only after granting the read permission you can use the ALGORITHM = TEMPTABLE in you view DDL definition.

Various permissions that you can grant to a user are

ALL PRIVILEGES- This would allow a MySQL user all access to a designated database (or if no database is selected, across the system)
CREATE- allows them to create new tables or databases
DROP- allows them to them to delete tables or databases
DELETE- allows them to delete rows from tables
INSERT- allows them to insert rows into tables
SELECT- allows them to use the Select command to read through databases
UPDATE- allow them to update table rows
GRANT OPTION- allows them to grant or remove other users' privileges

To provide a specific user with a permission, you can use this framework:

GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;

I found this article very helpful

A step by step guide I found here.

To create a read-only database user account for MySQL

At a UNIX prompt, run the MySQL command-line program, and log in as an administrator by typing the following command:

mysql -u root -p

Type the password for the root account. At the mysql prompt, do one of the following steps:

To give the user access to the database from any host, type the following command:

grant select on database_name.* to 'read-only_user_name'@'%' identified by 'password';

If the collector will be installed on the same host as the database, type the following command:

grant select on database_name.* to 'read-only_user_name' identified by 'password';

This command gives the user read-only access to the database from the local host only. If you know the host name or IP address of the host that the collector is will be installed on, type the following command:

grant select on database_name.* to 'read-only_user_name'@'host_name or IP_address' identified by 'password';

The host name must be resolvable by DNS or by the local hosts file. At the mysql prompt, type the following command:

flush privileges;

Type quit.

The following is a list of example commands and confirmation messages:

mysql> grant select on dbname.* to 'readonlyuser'@'%' identified 
by 'pogo$23';
Query OK, 0 rows affected (0.11 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> quit