授予用户仅查看 mysql 视图的权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3108656/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Grant a user permission to only view a mysql view
提问by Nelson
The below question pertains to MySQL 5.1.44
以下问题与 MySQL 5.1.44 有关
Let's say I have a table with records inserted by different users of my application. How can I give a specific user access to only see his/her records in that table? I've thought about creating a VIEWwith his/her records, but I don't know how to create a mysql user that can only see that VIEW.
假设我有一个表，其中包含由我的应用程序的不同用户插入的记录。如何授予特定用户访问权限以仅查看他/她在该表中的记录？我想过VIEW用他/她的记录创建一个，但我不知道如何创建一个只能看到那个的 mysql 用户VIEW。
So, is it possible to create a mysql-user that only has access to a single VIEW? can this user also be made so they read-only access to that VIEW?
那么，是否可以创建一个只能访问单个 mysql 的用户VIEW？是否也可以使该用户具有只读访问权限VIEW？
Thanks!
谢谢！
PS: What I call usersin my example are really subsidiary offices that want to access their records with their own applications.
PS：我在示例中所说的用户实际上是希望通过自己的应用程序访问其记录的分支机构。
回答by Naktibalda
GRANT SELECT ON database1.view1 TO 'someuser'@'somehost';
GRANT SELECT ON database1.view1 TO 'someuser'@'somehost';
回答by Dexin Wang
Besides
除了
GRANT SELECT ON <database_name>.<view_name>
TO <user>@<host>
IDENTIFIED BY '<password>'
it's better to also do
最好也这样做
GRANT SHOW VIEW
ON <database_name>.<view_name> TO <user>@<host>
IDENTIFIED BY '<password>'
so that a lot of SQL UI tool can get the view definition and work appropriately for the view.
这样很多 SQL UI 工具都可以获取视图定义并为视图正确工作。
回答by Anax
GRANT SELECT ON <database name>.<view name>
TO <user>@<host> IDENTIFIED BY '<password>'
Source: MySQL Documentation
来源：MySQL 文档
回答by Rich Fried
I believe the original question is actually asking how to limit the rows to those owned by a given user. (The idea of creating one view per user, and then granting just that, seems like a workaround.)
我相信最初的问题实际上是在询问如何将行限制为给定用户拥有的行。（为每个用户创建一个视图，然后仅授予该视图的想法似乎是一种解决方法。）
You can do this by inserting the user() reference into the data table, and then filtering on that.
您可以通过将 user() 引用插入到数据表中，然后对其进行过滤来做到这一点。
Using MySQL 5.6. Create a view that limits SELECT to just records owned by the current user:
使用 MySQL 5.6。创建一个视图，将 SELECT 限制为当前用户拥有的记录：
-- check the current user
select user();
create table t1 (myId int, mydata varchar(200), myName varchar(200));
insert t1 select 1, 'my data yes', user();
insert t1 select 2, 'my data yes2', user();
insert t1 select 3, 'my data no', 'joe';
select * from t1;
create or replace view v1 AS
select * from t1 where myName = user();
select * from v1;
回答by Daniel Fisher
If you want to make the view read only which I suspect you do. Then you should create the view with the ALGORITHM = TEMPTABLE clause.
如果您想让视图只读，我怀疑您会这样做。然后您应该使用 ALGORITHM = TEMPTABLE 子句创建视图。
This will make the view read only virtue of it having to create a temp table.
这将使视图只读，因为它必须创建一个临时表。
Another way to achieve read only and this is depends on your data is to stick an aggregate function. For example if you have a view which is based on a table and shows all columns then you can stick a distinct onto the select.
实现只读的另一种方法（这取决于您的数据）是粘贴聚合函数。例如，如果您有一个基于表并显示所有列的视图，那么您可以在选择上粘贴一个不同的。
