MySql：授予只读选项？

MySql : Grant read only options?

提问by Ajeet Ganga

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

我有一个用户，我想授予他对 db 架构的所有 READ 权限。

One way is this :

一种方法是这样的：

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

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

有没有办法在 grant 中对所有读取操作进行分组？

回答by Michael - sqlbot

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

如果有任何单个权限代表对数据库的所有 READ 操作。

It depends on how you define "all read."

这取决于您如何定义“全部阅读”。

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

从表和视图中“读取”是SELECT特权。如果这就是您所说的“全部阅读”，那么是的：

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 VIEWprivilege.

“阅读”定义意见是SHOW VIEW特权。

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

“读取”其他用户当前正在执行的查询列表是PROCESS特权。

"Reading" the current replication state is the REPLICATION CLIENTprivilege.

“读取”当前复制状态是REPLICATION CLIENT特权。

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 GRANTstatement.

如果这是您想要阅读的内容，您可以将其中任何一个（或任何其他可用权限）组合到一个GRANT语句中。

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 GRANTstatement to create a new user.

更改 'not_leet' 和 'localhost' 以匹配您要添加的新用户以及密码，将导致GRANT创建新用户的可重用语句。

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 GRANTstatement with dynamic SQL and/or directly manipulate the grant tables themselves.

当然，如果您希望通过单个操作来设置和授予用户有限的权限集，并且可能删除任何不值得拥有的权限，那么可以通过创建一个封装了您想要执行的所有操作的存储过程来完成。在过程的主体中，您可以GRANT使用动态 SQL构建语句和/或直接操作授权表本身。

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 DEFINERuser, allowing anybody with EXECUTEprivilege on the procedure to temporarily assume escalated privileges to allow them to do the specific things the procedure accomplishes.

在最近关于 Database Administrators 的问题中，发帖者希望非特权用户能够修改其他用户，这当然不是通常可以完成的事情——根据定义，可以修改其他用户的用户几乎不是一个非特权用户——然而——在这种情况下，存储过程提供了一个很好的解决方案，因为它们在DEFINER用户的安全上下文中运行，允许EXECUTE对该过程具有特权的任何人临时承担升级的特权，以允许他们执行特定的操作程序完成。

回答by Mahesh Patil

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

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

这将创建一个SELECT对所有数据库（包括视图）具有特权的用户。

回答by ath j

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 thisarticle very helpful

我发现这篇文章很有帮助

回答by Zafar Malik

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.

甚至用户也得到了答案，@Michael - sqlbot 在他的帖子中已经很好地涵盖了大部分要点，但缺少一点，所以只是试图涵盖它。

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).

注意：这里需要EXECUTE，如果有生成报告的存储过程（很少有select语句），用户可以读取数据。

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

将 localhost 替换为用户将连接到数据库的特定 IP。

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.

• SHOW VIEW ：如果要显示视图架构。
• REPLICATION CLIENT ：如果用户需要检查复制/从属状态。但需要对所有数据库授予权限。
• PROCESS : 如果用户需要检查正在运行的进程。仅适用于所有数据库。

回答by biniam

A step by step guide I found here.

我在这里找到的分步指南。

To create a read-only database user account for MySQL

为 MySQL 创建只读数据库用户帐户

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

在 UNIX 提示符下，运行 MySQL 命令行程序，并通过键入以下命令以管理员身份登录：

mysql -u root -p

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

键入 root 帐户的密码。在 mysql 提示符下，执行以下步骤之一：

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:

此命令仅授予用户从本地主机对数据库的只读访问权限。如果您知道将安装收集器的主机的主机名或 IP 地址，请键入以下命令：

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:

主机名必须可由 DNS 或本地主机文件解析。在 mysql 提示符下，键入以下命令：

flush privileges;

Type quit.

键入quit。

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

以下是示例命令和确认消息的列表：

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

回答by Daniel Patrick

Note for MySQL 8 it's different

注意 MySQL 8 它是不同的

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;

回答by Daniel Fisher

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.

如果您希望在授予读取权限后仅读取视图，您可以在您的视图 DDL 定义中使用 ALGORITHM = TEMPTABLE。