MySql: read-only?

Question

I have a user to whom I want to grant all READ permissions for the db scheme.

One of the methods:

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

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

Answer 1

If there is any single privilege that denotes ALL READ operations in the database.

It depends on how you define "all read."

Reading from tables and views is a SELECT privilege. If this is what you mean by “all read,” then yes:

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

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

Reading the definition of views is a SHOW VIEW privilege.

"Reading" the list of queries currently being executed by other users is a PROCESS privilege.

Reading the current state of replication is a REPLICATION CLIENT privilege.

Please note that any or all of them may display more information than you intend to exhibit, depending on the nature of the user.

If you want to do this reading, you can combine any of them (or any other privileges available ) in one GRANT .

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

However, there is no single privilege that provides a subset of the other privileges, which is similar to what you are asking for.

If you are doing something manually and looking for an easier way around this without having to remember the exact grant that you usually make for a particular class of users, you can find an expression to restore comparable user grants and modify it 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 that you want to add along with the password will reuse the GRANT statement to create a new user.

Of, if you want a single operation to configure and provide a limited set of privileges for users and possibly delete any undeserved privileges, this can be done by creating a stored procedure that encapsulates everything that you want to do. Inside the procedure procedure, you must build a GRANT statement with dynamic SQL and / or directly manipulate the grant tables themselves.

In this recent question about database administrators, the poster wanted the unprivileged user to be able to modify other users, which, of course, is not something that can usually be done - a user who can modify other users, to a large extent by definition, but not a non-privileged user - however - a stored procedure provided a good solution in this case, because they are carried out with the security context of the user DEFINER , which allows anyone with a privilege EXECUTE in the proce Ur to temporarily take escalated privileges, allowing them to carry out specific actions to be performed the procedure.

Answer 2

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

This will create a user with a SELECT privilege for the entire database, including Views.

Answer 3

Various permissions you can grant the user:

 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 grant permission to a specific user, you can use this platform:

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

I found this article very helpful.

Answer 4

I found a walkthrough here .

To create a read-only database user account for MySQL

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

 mysql -u root -p 

Enter the password for the root account. At the mysql command prompt, do one of the following:

To give the user access to the database from any host, enter 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, enter 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 only from the local host. If you know the host name or IP address of the host on which the collector will be installed, enter 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 resolved by the DNS or local hosts file. At the mysql command prompt, enter the following command:

 flush privileges; 

Enter quit .

The following is a list of sample 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 

Answer 5

Even the user has an answer, and @Michael - sqlbot almost completely scored points in his post, but one point is missing, so I'm just trying to cover it.

If you want to provide read permission for a simple user (not for an administrator) -

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

Note: EXECUTE is required here so that the user can read data if there is a stored procedure that creates a report (has several selection statements).

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

Additional read permissions -

• SHOW VIEW: If you want to show the display scheme.
• REPLICATION CLIENT: If the user needs to check the replication / slave status. But you need to give permission to all databases.
• PROCESS: If the user needs to check the current process. Will work with all Only DB.

Answer 6

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

Answer 7

Note for mysql 8 it's different

You need to do this in two steps:

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