NAME

GRANT - 定義訪問權(quán)限

SYNOPSIS

GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
    [,...] | ALL [ PRIVILEGES ] }
    ON [ TABLE ] tablename [, ...]
    TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
    ON DATABASE dbname [, ...]
    TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON FUNCTION funcname ([type, ...]) [, ...]
    TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE langname [, ...]
    TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schemaname [, ...]
    TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

DESCRIPTION 描述

GRANT 命令將某對象（表，視圖，序列，函數(shù)過程語言，或者模式） 上的特定權(quán)限給予一個用戶或者多個用戶或者一組用戶。 這些權(quán)限將增加到那些已經(jīng)賦予的權(quán)限上，如果存在這些權(quán)限的話。

 鍵字 PUBLIC 表示該權(quán)限要賦予所有用戶, 包括那些以后可能創(chuàng)建的用戶。PUBLIC  可以看做是一個隱含定義好的組，它總是包括所有用戶。 任何特定的用戶都將擁有直接賦予他/她的權(quán)限，加上他/她所處的任何組， 以及再加上賦予 PUBLIC 的權(quán)限的總和。

 如果聲明了 WITH GRANT OPTION，那么權(quán)限的受予者也可以賦予別人。 缺省的時候這是不允許的。賦權(quán)選項只能給獨立的用戶，而不能給組或者 PUBLIC。

 對對象的所有者（通常就是創(chuàng)建者）而言，沒有什么權(quán)限需要賦予， 因為所有者缺省就持有所有權(quán)限。（不過，所有者出于安全考慮可以選擇廢棄一些他自己的權(quán)限。） 刪除一個對象的權(quán)力，或者是任意修改它的權(quán)力都不是可賦予的權(quán)利所能描述的； 它是創(chuàng)建者固有的，并且不能賦予或撤銷。

 根據(jù)對象的不同，初始的缺省權(quán)限可能包括給 PUBLIC  賦予一些權(quán)限。缺省設(shè)置對于表和模式是沒有公開訪問權(quán)限的； TEMP 表為數(shù)據(jù)庫創(chuàng)建權(quán)限；EXECUTE 權(quán)限用于函數(shù)； 以及 USAGE 用于語言。對象所有者當然可以撤回這些權(quán)限。 （出于最大安全性考慮，在創(chuàng)建該對象的同一個事務(wù)中發(fā)出 REVOKE； 那么就不會打開給別的用戶使用該對象的窗口。）

 可能的權(quán)限有：

SELECT

 允許對聲明的表，試圖，或者序列 SELECT [select(7)] 任意字段。還允許做 COPY [copy(7)] TO 的源。 對于序列而言，這個權(quán)限還允許使用 currval 函數(shù)。

INSERT

 允許向聲明的表 INSERT [insert(7)] 一個新行。 同時還允許做 COPY [copy(7)] FROM。

UPDATE

 允許對聲明的表中任意字段做 UPDATE [update(7)] 。 SELECT ... FOR UPDATE 也要求這個權(quán)限 （除了 SELECT 權(quán)限之外）。比如， 這個權(quán)限允許使用nextval 和 setval。

DELETE

 允許從聲明的表中 DELETE [delete(7)] 行。

RULE

 允許在該表/視圖上創(chuàng)建規(guī)則。（參閱 CREATE RULE [create_rule(7)] 語句。）

REFERENCES

 要創(chuàng)建一個外鍵約束，你必須在參考表和被參考表上都擁有這個權(quán)限。

TRIGGER

 允許在聲明表上創(chuàng)建觸發(fā)器。（參閱 CREATE TRIGGER [create_trigger(7)] 語句。）

CREATE

 對于數(shù)據(jù)庫，允許在該數(shù)據(jù)庫里創(chuàng)建新的模式。

 對于模式，允許在該模式中創(chuàng)建新的對象。 要重命名一個現(xiàn)有對象，你必需擁有該對象并且。 對包含該對象的模式擁有這個權(quán)限。

TEMPORARY

TEMP

 允許在使用該數(shù)據(jù)庫的時候創(chuàng)建臨時表。

EXECUTE

 允許使用指定的函數(shù)并且可以使用任何利用這些函數(shù)實現(xiàn)的操作符。 這是適用于函數(shù)的唯一的一種權(quán)限類型。 （該語法同樣適用于聚集函數(shù)。）

USAGE

 對于過程語言， 允許使用指定過程語言創(chuàng)建該語言的函數(shù)。 這是適用于過程語言的唯一的一種權(quán)限類型。

 對于模式，允許訪問包含在指定模式中的對象（假設(shè)該對象的所有權(quán)要求同樣也設(shè)置了）。 最終這些就允許了權(quán)限接受者"查詢"模式中的對象。

ALL PRIVILEGES

 一次性給予所有適用于該對象的權(quán)限。 PRIVILEGES 關(guān)鍵字在 PostgreSQL 里是可選的， 但是嚴格的 SQL 要求有這個關(guān)鍵字。

 其它命令要求的權(quán)限都在相應(yīng)的命令的參考頁上列出。  

NOTES 注意

REVOKE [revoke(7)] 命令用于刪除訪問權(quán)限。

 我們要注意數(shù)據(jù)庫超級用戶可以訪問所有對象， 而不會受對象的權(quán)限設(shè)置影響。這個特點類似 Unix 系統(tǒng)的 root  的權(quán)限。和 root 一樣，除了必要的情況，總是以超級用戶身分進行操作是不明智的做法。

If a superuser chooses to issue a GRANT or REVOKE command, the command is performed as though it were issued by the owner of the affected object. In particular, privileges granted via such a command will appear to have been granted by the object owner.

 目前，要在 PostgreSQL 里只對某幾列賦予權(quán)限， 你必須創(chuàng)建一個擁有那幾行的視圖然后給那個視圖賦予權(quán)限。

 使用 psql(1) 的 \z 命令獲取在現(xiàn)有對象上的與權(quán)限有關(guān)的信息。

=> \z mytable

        Access privileges for database "lusitania"
 Schema |  Table  |           Access privileges
--------+---------+---------------------------------------
 public | mytable | {=r/postgres,miriam=arwdRxt/postgres,"group todos=arw/postgres"}
(1 row)

\z 顯示的條目解釋如下：

              =xxxx -- 賦予 PUBLIC 的權(quán)限
         uname=xxxx -- 賦予一個用戶的權(quán)限
   group gname=xxxx -- 賦予一個組的權(quán)限

                  r -- SELECT ("讀")
                  w -- UPDATE ("寫")
                  a -- INSERT ("追加")
                  d -- DELETE
                  R -- RULE
                  x -- REFERENCES
                  t -- TRIGGER
                  X -- EXECUTE
                  U -- USAGE
                  C -- CREATE
                  T -- TEMPORARY
            arwdRxt -- ALL PRIVILEGES (for tables)
                  * -- 給前面權(quán)限的授權(quán)選項

              /yyyy -- 授出這個權(quán)限的用戶

 用戶 miriam 在建完表之后再做下面的語句， 就可以得到上面例子的結(jié)果

GRANT SELECT ON mytable TO PUBLIC;
GRANT SELECT, UPDATE, INSERT ON mytable TO GROUP todos;

 如果一個給定的對象的 "Access privileges" 字段是空的， 這意味著該對象有缺省權(quán)限（也就是說，它的權(quán)限字段是 NULL）。 缺省權(quán)限總是包括所有者的所有權(quán)限，以及根據(jù)對象的不同，可能包含一些給 PUBLIC  的權(quán)限。 對象上第一個 GRANT 或者 REVOKE  將實例化這個缺省權(quán)限（比如，產(chǎn)生 {=,miriam=arwdRxt}） 然后根據(jù)每次特定的需求修改它。  

EXAMPLES 例子

 把表 films 的插入權(quán)限賦予所有用戶：

GRANT INSERT ON films TO PUBLIC;

 賦予用戶manuel對視圖kinds的所有權(quán)限：

GRANT ALL PRIVILEGES ON kinds TO manuel;

COMPATIBILITY 兼容性

 根據(jù) SQL 標準，在 ALL PRIVILEGES 里的 PRIVILEGES  關(guān)鍵字是必須的。SQL 不支持在一條命令里對多個表設(shè)置權(quán)限。

SQL 標準允許在一個表里為獨立的字段設(shè)置權(quán)限：

GRANT privileges
    ON table [ ( column [, ...] ) ] [, ...]
    TO { PUBLIC | username [, ...] } [ WITH GRANT OPTION ]

SQL 標準對其它類型的對象提供了一個 USAGE 權(quán)限：字符集，校勘，轉(zhuǎn)換，域。

RULE 權(quán)限，以及在數(shù)據(jù)庫，模式，語言和序列上的權(quán)限是 PostgreSQL 擴展。  

SEE ALSO 參見

REVOKE [revoke(7)]  

#p#

NAME

GRANT - define access privileges

SYNOPSIS

GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
    [,...] | ALL [ PRIVILEGES ] }
    ON [ TABLE ] tablename [, ...]
    TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
    ON DATABASE dbname [, ...]
    TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON FUNCTION funcname ([type, ...]) [, ...]
    TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE langname [, ...]
    TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schemaname [, ...]
    TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

DESCRIPTION

The GRANT command gives specific privileges on an object (table, view, sequence, database, function, procedural language, or schema) to one or more users or groups of users. These privileges are added to those already granted, if any.

The key word PUBLIC indicates that the privileges are to be granted to all users, including those that may be created later. PUBLIC may be thought of as an implicitly defined group that always includes all users. Any particular user will have the sum of privileges granted directly to him, privileges granted to any group he is presently a member of, and privileges granted to PUBLIC.

If WITH GRANT OPTION is specified, the recipient of the privilege may in turn grant it to others. By default this is not allowed. Grant options can only be granted to individual users, not to groups or PUBLIC.

There is no need to grant privileges to the owner of an object (usually the user that created it), as the owner has all privileges by default. (The owner could, however, choose to revoke some of his own privileges for safety.) The right to drop an object, or to alter its definition in any way is not described by a grantable privilege; it is inherent in the owner, and cannot be granted or revoked. It is not possible for the owner's grant options to be revoked, either.

Depending on the type of object, the initial default privileges may include granting some privileges to PUBLIC. The default is no public access for tables and schemas; TEMP table creation privilege for databases; EXECUTE privilege for functions; and USAGE privilege for languages. The object owner may of course revoke these privileges. (For maximum security, issue the REVOKE in the same transaction that creates the object; then there is no window in which another user may use the object.)

The possible privileges are:

SELECT

Allows SELECT [select(7)] from any column of the specified table, view, or sequence. Also allows the use of COPY [copy(7)] TO. For sequences, this privilege also allows the use of the currval function.

INSERT

Allows INSERT [insert(7)] of a new row into the specified table. Also allows COPY [copy(7)] FROM.

UPDATE

Allows UPDATE [update(7)] of any column of the specified table. SELECT ... FOR UPDATE also requires this privilege (besides the SELECT privilege). For sequences, this privilege allows the use of the nextval and setval functions.

DELETE

Allows DELETE [delete(7)] of a row from the specified table.

RULE

Allows the creation of a rule on the table/view. (See CREATE RULE [create_rule(7)] statement.)

REFERENCES

To create a foreign key constraint, it is necessary to have this privilege on both the referencing and referenced tables.

TRIGGER

Allows the creation of a trigger on the specified table. (See CREATE TRIGGER [create_trigger(7)] statement.)

CREATE

For databases, allows new schemas to be created within the database.

For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and have this privilege for the containing schema.

TEMPORARY

TEMP

Allows temporary tables to be created while using the database.

EXECUTE

Allows the use of the specified function and the use of any operators that are implemented on top of the function. This is the only type of privilege that is applicable to functions. (This syntax works for aggregate functions, as well.)

USAGE

For procedural languages, allows the use of the specified language for the creation of functions in that language. This is the only type of privilege that is applicable to procedural languages.

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.

ALL PRIVILEGES

Grant all of the privileges applicable to the object at once. The PRIVILEGES key word is optional in PostgreSQL, though it is required by strict SQL.

The privileges required by other commands are listed on the reference page of the respective command.  

NOTES

The REVOKE [revoke(7)] command is used to revoke access privileges.

It should be noted that database superusers can access all objects regardless of object privilege settings. This is comparable to the rights of root in a Unix system. As with root, it's unwise to operate as a superuser except when absolutely necessary.

If a superuser chooses to issue a GRANT or REVOKE command, the command is performed as though it were issued by the owner of the affected object. In particular, privileges granted via such a command will appear to have been granted by the object owner.

Currently, to grant privileges in PostgreSQL to only a few columns, you must create a view having the desired columns and then grant privileges to that view.

Use psql(1)'s \z command to obtain information about existing privileges, for example:

=> \z mytable

        Access privileges for database "lusitania"
 Schema |  Table  |           Access privileges
--------+---------+---------------------------------------
 public | mytable | {=r/postgres,miriam=arwdRxt/postgres,"group todos=arw/postgres"}
(1 row)

The entries shown by \z are interpreted thus:

              =xxxx -- privileges granted to PUBLIC
         uname=xxxx -- privileges granted to a user
   group gname=xxxx -- privileges granted to a group

                  r -- SELECT ("read")
                  w -- UPDATE ("write")
                  a -- INSERT ("append")
                  d -- DELETE
                  R -- RULE
                  x -- REFERENCES
                  t -- TRIGGER
                  X -- EXECUTE
                  U -- USAGE
                  C -- CREATE
                  T -- TEMPORARY
            arwdRxt -- ALL PRIVILEGES (for tables)
                  * -- grant option for preceding privilege

              /yyyy -- user who granted this privilege

The above example display would be seen by user miriam after creating table mytable and doing

GRANT SELECT ON mytable TO PUBLIC;
GRANT SELECT, UPDATE, INSERT ON mytable TO GROUP todos;

If the ``Access privileges'' column is empty for a given object, it means the object has default privileges (that is, its privileges column is null). Default privileges always include all privileges for the owner, and may include some privileges for PUBLIC depending on the object type, as explained above. The first GRANT or REVOKE on an object will instantiate the default privileges (producing, for example, {=,miriam=arwdRxt}) and then modify them per the specified request.  

EXAMPLES

Grant insert privilege to all users on table films:

GRANT INSERT ON films TO PUBLIC;

Grant all privileges to user manuel on view kinds:

GRANT ALL PRIVILEGES ON kinds TO manuel;

COMPATIBILITY

According to the SQL standard, the PRIVILEGES key word in ALL PRIVILEGES is required. The SQL standard does not support setting the privileges on more than one object per command.

The SQL standard allows setting privileges for individual columns within a table:

GRANT privileges
    ON table [ ( column [, ...] ) ] [, ...]
    TO { PUBLIC | username [, ...] } [ WITH GRANT OPTION ]

The SQL standard provides for a USAGE privilege on other kinds of objects: character sets, collations, translations, domains.

The RULE privilege, and privileges on databases, schemas, languages, and sequences are PostgreSQL extensions.  

SEE ALSO

REVOKE [revoke(7)]