MySql Grant read only options

Question

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

User · Answer

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

User · Answer

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

User · Answer

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 gt  grant select on dbname   to  readonlyuser      identified  by  pogo 23   Query OK  0 rows affected  0 11 sec  mysql gt  flush privileges  Query OK  0 rows affected  0 00 sec  mysql gt  quit

User · Answer

GRANT SELECT ON     TO  user   localhost  IDENTIFIED BY  password     This will create a user with SELECT privilege for all database including Views

User · Answer

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

User · Answer

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 gt  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

User · Answer

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

[mysql] MySql : Grant read only options?

Examples related to mysql

Examples related to sql