MySQL:如何避免用户看到我还有其他数据库，并授予对一个数据库上一个视图的选择访问权限? [英] MySQL: How to avoid a user from even seeing I have other DB's and grant select access to one view on one DB?

问题描述

我的服务器中有多个数据库，我需要允许一个用户从其中一个数据库的视图中选择记录.但是，我需要该用户不要看到其他数据库，也不要看到视图所属的数据库中的其他表.这可能吗?

我有一个托管公司的帐户，一个共享托管帐户，当我通过phpmyadmin访问数据库时，我只能看到我的数据库.这类似于我所需要的.谢谢您的帮助.

解决方案

我发现 user 表(提供最粗略的控制)中的大多数特权，然后仅在更细粒度的表中仅使用所需的特定替代.

尤其是，有一个名为的特权.SHOW_DATABASES ，由 mysql.user 表中的 Show_db_priv 列确定；您需要将有问题的用户的权限设置为"N"(如上所述，您可能还希望将用户表中的大多数其他权限也设置为"N")，然后仅授予该用户的特权实际需要 mysql.db 或 mysql.tables_priv 表中的内容或适合您特定情况的任何内容.

I have several DB's in my server, and I need to allow one user to select records from a view in one of the DB's. But I need for this user to not even see that there are other DB's nor see that there are other tables in the DB where the view belongs. Is this possible?

I had an account with a hosting company, a shared hosting account, and I could only see my DB when I accessed it through phpmyadmin. This is similar to what I need. Thank you for your help.

解决方案

I've found the overview contained in this article even more helpful than the actual MySQL documentation for describing the big picture of how MySQL privileges are granted or denied.

The gist of the overview article is that privileges are controlled by a series of increasingly finer-grained permissions tables in the mysql database: mysql.user, mysql.db, mysql.host, mysql.tables_priv, mysql.columns_priv, mysql.procs_priv. The general rule is that a "Y" value for a privilege in a more fine-grained table overrules a "N" value in a more coarse-grained table. So the recommended strategy is to start by denying most privileges in the user table (which gives the coarsest control), and then make only the specific overrides that you want in the more fine-grained tables.

In particular, there is a privilege called SHOW_DATABASES which is determined by the Show_db_priv column in the mysql.user table; you'll want to set this to "N" for the user in question (and as described above, you may want to set most other permissions in the user table to "N" as well) and then grant only the privileges that the user actually needs in the mysql.db or mysql.tables_priv table or whatever would be appropriate for your particular case.

这篇关于MySQL:如何避免用户看到我还有其他数据库，并授予对一个数据库上一个视图的选择访问权限?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！