在SCHEMA什么格兰特使用？ [英] What GRANT USAGE ON SCHEMA exactly do?

问题描述

我试图第一次创建一个Postgres数据库，所以这可能是一个愚蠢的问题。我为我必须从我的php脚本访问数据库的数据库角色分配基本只读权限，我有一个好奇心：如果我执行

I'm trying to create for the first time a Postgres database, so this is probably a stupid question. I assigned basic read-only permissions to the db role that must access the database from my php scripts, and I have a curiosity: if I execute

GRANT some_or_all_privileges ON ALL TABLES IN SCHEMA schema TO role;

是否还需要执行

GRANT USAGE ON SCHEMA schema TO role;

？

http://www.postgresql.org/docs/9.1/static/sql-grant.html\">文档：

From documentation:

USAGE：对于模式，允许访问
指定模式中包含的对象（假设还满足对象自己的特权
要求）。基本上这允许被授予者
在模式中查找对象。

USAGE: For schemas, allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to "look up" objects within the schema.

我认为如果我可以选择操纵模式中包含的任何数据，我可以访问模式本身的任何对象。我错了吗？如果不是，在SCHEMA上使用GRANT使用是用于？

I think that if I can select or manipulate any data contained in the schema, I can access to any objects of the schema itself. Am I wrong? If not, what GRANT USAGE ON SCHEMA is used for? And what does the documentation means exactly with "assuming that the objects' own privilege requirements are also met"?

推荐答案

GRANT 在不同的对象上是分开的。 GRANT 在数据库中不对中的模式的 GRANT 权限。类似地， GRANT 在模式上不授予对表内的权限。

GRANTs on different objects are separate. GRANTing on a database doesn't GRANT rights to the schema within. Similiarly, GRANTing on a schema doesn't grant rights on the tables within.

SELECT ，但是无法在包含它的模式中看到它，那么您不能访问该表。权限测试按顺序完成：您在模式上是否具有 USAGE ？否：拒绝访问。是的：你对桌子有适当的权利吗？否：拒绝访问。是：检查列权限。

If you have rights to SELECT from a table, but not the right to see it in the schema that contains it then you can't access the table. The rights tests are done in order: Do you have USAGE on the schema? No: Reject access. Yes: Do you also have the appropriate rights on the table? No: Reject access. Yes: Check column privileges.

您的混淆可能是因为 public 模式的默认值<$每个用户/组都是其成员的角色 public 的所有权限的c $ c> GRANT

Your confusion may arise from the fact that the public schema has a default GRANT of all rights to the role public, which every user/group is a member of. So everyone already has usage on that schema.

阶段：

假设还满足对象自己的特权要求）

(assuming that the objects' own privilege requirements are also met)

说你必须有 USAGE 在模式中使用其中的对象，但在模式中具有 USAGE 不是自身足以使用模式中的对象，您还必须对象本身的权限。

Is saying that you must have USAGE on a schema to use objects within it, but having USAGE on a schema is not its self sufficient to use the objects within the schema, you must also have rights on the objects themselves.

它就像一个目录树。如果您在其中创建一个目录 somedir 与文件 somefile ，然后设置它，以便只有您自己的用户可以访问该目录或文件（模式 rwx ------ 在目录，模式 rw -------

It's like a directory tree. If you create a directory somedir with file somefile within it then set it so that only your own user can access the directory or the file (mode rwx------ on the dir, mode rw------- on the file) then nobody else can list the directory to see that the file exists.

如果您授予对文件的世界读权限（模式 rw-r - r - ）但是不改变目录权限没有什么区别。

If you were to grant world-read rights on the file (mode rw-r--r--) but not change the directory permissions it'd make no difference. Nobody could see the file in order to read it, because they don't have the rights to list the directory.

如果您改为设置，则无法查看文件以读取该文件。 rwx-r-xr-x ，以便人们可以列出并遍历目录，但不能更改文件权限，人们可以列出

If you instead set rwx-r-xr-x on the directory, setting it so people can list and traverse the directory but not changing the file permissions, people could list the file but could not read it because they'd have no access to the file.

您需要设置两者 / em>允许其他人查看该文件。

You need to set both permissions for people to actually be able to view the file.

Pg中的相同内容。您需要使用模式 USAGE 权限和对象权限对对象执行操作，如表中的 SELECT 。

Same thing in Pg. You need both schema USAGE rights and object rights to perform an action on an object, like SELECT from a table.

（类比下来，PostgreSQL没有行级安全性，所以用户仍然可以看到表中存在的模式 SELECT 直接从 pg_class 中输入，他们不能以任何方式与它进行交互，列表部分不完全相同。）

(The analogy falls down a bit in that PostgreSQL doesn't have row-level security yet, so the user can still "see" that the table exists in the schema by SELECTing from pg_class directly. They can't interact with it in any way, though, so it's just the "list" part that isn't quite the same.)

这篇关于在SCHEMA什么格兰特使用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！