MySql : Grant read only options?

If there is any single privilege that stands for ALL READ operations on database.

It depends on how you define "all read."

"Reading" from tables and views is the SELECT privilege. If that's what you mean by "all read" then yes:

GRANT SELECT ON *.* TO 'username'@'host_or_wildcard' IDENTIFIED BY 'password';

However, it sounds like you mean an ability to "see" everything, to "look but not touch." So, here are the other kinds of reading that come to mind:

"Reading" the definition of views is the SHOW VIEW privilege.

"Reading" the list of currently-executing queries by other users is the PROCESS privilege.

"Reading" the current replication state is the REPLICATION CLIENT privilege.

Note that any or all of these might expose more information than you intend to expose, depending on the nature of the user in question.

If that's the reading you want to do, you can combine any of those (or any other of the available privileges) in a single GRANT statement.

GRANT SELECT, SHOW VIEW, PROCESS, REPLICATION CLIENT ON *.* TO ...

However, there is no single privilege that grants some subset of other privileges, which is what it sounds like you are asking.

If you are doing things manually and looking for an easier way to go about this without needing to remember the exact grant you typically make for a certain class of user, you can look up the statement to regenerate a comparable user's grants, and change it around to create a new user with similar privileges:

mysql> SHOW GRANTS FOR 'not_leet'@'localhost';+------------------------------------------------------------------------------------------------------------------------------------+| Grants for not_leet@localhost                                                                                                      |+------------------------------------------------------------------------------------------------------------------------------------+| GRANT SELECT, REPLICATION CLIENT ON *.* TO 'not_leet'@'localhost' IDENTIFIED BY PASSWORD '*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' |+------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

Changing 'not_leet' and 'localhost' to match the new user you want to add, along with the password, will result in a reusable GRANT statement to create a new user.

Of, if you want a single operation to set up and grant the limited set of privileges to users, and perhaps remove any unmerited privileges, that can be done by creating a stored procedure that encapsulates everything that you want to do. Within the body of the procedure, you'd build the GRANT statement with dynamic SQL and/or directly manipulate the grant tables themselves.

In this recent question on Database Administrators, the poster wanted the ability for an unprivileged user to modify other users, which of course is not something that can normally be done -- a user that can modify other users is, pretty much by definition, not an unprivileged user -- however -- stored procedures provided a good solution in that case, because they run with the security context of their DEFINER user, allowing anybody with EXECUTE privilege on the procedure to temporarily assume escalated privileges to allow them to do the specific things the procedure accomplishes.

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

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

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 databasesDROP- allows them to them to delete tables or databasesDELETE- allows them to delete rows from tablesINSERT- allows them to insert rows into tablesSELECT- allows them to use the Select command to read through databasesUPDATE- allow them to update table rowsGRANT 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