MySql:授予只读选项? [英] MySql : Grant read only options?
问题描述
我有一个用户，我想授予该用户对数据库模式的所有READ权限.
I have a user, whom I want to grant all the READ permission on a db schema.
这是一种方法:
GRANT SELECT, SHOW_VIEW ON test.* TO 'readuser'@'%';
有没有一种方法可以将所有读取操作归类到Grant中?
Is there a way to group all read operations in grant ?
推荐答案
如果有任何一个特权代表对数据库的所有读取操作.
If there is any single privilege that stands for ALL READ operations on database.
这取决于您如何定义全部阅读".
It depends on how you define "all read."
从表和视图中读取"是SELECT特权.如果那是全部阅读"的意思，那么是:
"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:
阅读"视图的定义是SHOW VIEW特权.
"Reading" the definition of views is the SHOW VIEW privilege.
读取"其他用户当前正在执行的查询的列表是PROCESS特权.
"Reading" the list of currently-executing queries by other users is the PROCESS privilege.
读取"当前的复制状态是REPLICATION CLIENT特权.
"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)
更改"not_leet"和"localhost"以匹配要添加的新用户以及密码，将导致可重复使用的GRANT语句来创建新用户.
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，如果您希望单个操作来设置并向用户授予有限的特权集，并可能删除所有未获得的特权，则可以通过创建一个存储过程来封装您想要做的所有事情来实现.在该过程的主体中，您将使用动态SQL构建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 GRANT statement with dynamic SQL and/or directly manipulate the grant tables themselves.
在有关数据库管理员的最新问题中，发布者希望具有特权用户能够修改其他用户的功能，从而可以当然，这通常是无法完成的-从定义上说，可以修改其他用户的用户几乎不是非特权用户-但是-在这种情况下，存储过程提供了一个很好的解决方案，因为它们与他们的DEFINER用户的安全上下文，从而允许对该过程具有EXECUTE特权的任何人临时承担逐步升级的特权，以允许他们执行该过程所完成的特定操作.
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.
这篇关于MySql:授予只读选项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
