授予数据库特权是否不包括授予所有数据库表的特权？ [英] Doesn't granting privileges on database include granting the privilegs on all database's tables?

问题描述

在PostgreSQL中调用简单的select语句时，我经常出错。错误描述表明，用户无法访问关系'A'

I used to get errors when calling a simple select statement in PostgreSQL. The error description was telling, the user can't access the relation 'A'

我的模式如下：

CREATE TABLE A(
    id SERIAL PRIMARY KEY, 
);

CREATE TABLE B(
    id SERIAL PRIMARY KEY,
    A_Id integer REFERENCES A (id)
);

CREATE TABLE C(
    id SERIAL PRIMARY KEY, 
    A_Id integer REFERENCES A (id)
);

我尝试授予执行以下特权：

I've tried to grant the privileges executing:

GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

没有帮助

GRANT ALL PRIVILEGES ON SCHEMA public TO myuser;

没有帮助

仅在执行后：

GRANT ALL PRIVILEGES ON TABLE A TO myuser;

错误消失了。

Doesn'授予数据库特权包括授予所有数据库表的特权？

Doesn't granting privileges on database include granting the privileges on all database's tables?

推荐答案

在运行 GRANT ALL时将数据库mydb上的特权授予myuser； 授予对数据库有效的所有特权（这样）。

When you run GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser; all privileges that are valid for a database (as such) are granted.

如果您查看手册，您将看到数据库对象支持特权 CREATE ， CONNECT 和 TEMPORARY ，然后在使用 grant all时全部授予。

If you check the manual you will see that a database object supports the privileges CREATE, CONNECT, and TEMPORARY which are then all granted when you use "grant all".

但是这不会授予数据库内部中的任何对象。

This does however not grant anything on the objects inside the database.

因此，对您的问题 不授予数据库特权包括授予所有数据库表的特权的答案很明显：否（如所记录）。

Therefor the answer to your question "Doesn't granting privileges on database include granting the privileges on all database's tables" is a clear: NO (as documented).

您正在寻找的是：

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser; 

这将对所有现有表自动执行。但是，这不适用于将来创建的任何表。如果需要，您需要使用 ALTER默认特权 命令。

which will do it automatically for all existing tables. This however is not applied to any table created in the future. If you want that, you need to use the ALTER DEFAULT PRIVILEGES command.

这篇关于授予数据库特权是否不包括授予所有数据库表的特权？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！