grant 中文man页面

系统
GRANT 命令将某对象(表,视图,序列,函数过程语言,或者模式) 上的特定权限给予一个用户或者多个用户或者一组用户。 这些权限将增加到那些已经赋予的权限上,如果存在这些权限的话。

NAME

GRANT - 定义访问权限

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 命令将某对象(表,视图,序列,函数过程语言,或者模式) 上的特定权限给予一个用户或者多个用户或者一组用户。 这些权限将增加到那些已经赋予的权限上,如果存在这些权限的话。


 键字 PUBLIC 表示该权限要赋予所有用户, 包括那些以后可能创建的用户。PUBLIC  可以看做是一个隐含定义好的组,它总是包括所有用户。 任何特定的用户都将拥有直接赋予他/她的权限,加上他/她所处的任何组, 以及再加上赋予 PUBLIC 的权限的总和。


 如果声明了 WITH GRANT OPTION,那么权限的受予者也可以赋予别人。 缺省的时候这是不允许的。赋权选项只能给独立的用户,而不能给组或者 PUBLIC。


 对对象的所有者(通常就是创建者)而言,没有什么权限需要赋予, 因为所有者缺省就持有所有权限。(不过,所有者出于安全考虑可以选择废弃一些他自己的权限。) 删除一个对象的权力,或者是任意修改它的权力都不是可赋予的权利所能描述的; 它是创建者固有的,并且不能赋予或撤销。


 根据对象的不同,初始的缺省权限可能包括给 PUBLIC  赋予一些权限。缺省设置对于表和模式是没有公开访问权限的; TEMP 表为数据库创建权限;EXECUTE 权限用于函数; 以及 USAGE 用于语言。对象所有者当然可以撤回这些权限。 (出于最大安全性考虑,在创建该对象的同一个事务中发出 REVOKE; 那么就不会打开给别的用户使用该对象的窗口。)


 可能的权限有:

SELECT

 允许对声明的表,试图,或者序列 SELECT [select(7)] 任意字段。还允许做 COPY [copy(7)] TO 的源。 对于序列而言,这个权限还允许使用 currval 函数。
INSERT

 允许向声明的表 INSERT [insert(7)] 一个新行。 同时还允许做 COPY [copy(7)] FROM。
UPDATE

 允许对声明的表中任意字段做 UPDATE [update(7)] 。 SELECT ... FOR UPDATE 也要求这个权限 (除了 SELECT 权限之外)。比如, 这个权限允许使用nextval 和 setval。
DELETE

 允许从声明的表中 DELETE [delete(7)] 行。
RULE

 允许在该表/视图上创建规则。(参阅 CREATE RULE [create_rule(7)] 语句。)
REFERENCES

 要创建一个外键约束,你必须在参考表和被参考表上都拥有这个权限。
TRIGGER

 允许在声明表上创建触发器。(参阅 CREATE TRIGGER [create_trigger(7)] 语句。)
CREATE

 对于数据库,允许在该数据库里创建新的模式。


 对于模式,允许在该模式中创建新的对象。 要重命名一个现有对象,你必需拥有该对象并且。 对包含该对象的模式拥有这个权限。

TEMPORARY
TEMP

 允许在使用该数据库的时候创建临时表。
EXECUTE

 允许使用指定的函数并且可以使用任何利用这些函数实现的操作符。 这是适用于函数的唯一的一种权限类型。 (该语法同样适用于聚集函数。)
USAGE

 对于过程语言, 允许使用指定过程语言创建该语言的函数。 这是适用于过程语言的唯一的一种权限类型。


 对于模式,允许访问包含在指定模式中的对象(假设该对象的所有权要求同样也设置了)。 最终这些就允许了权限接受者"查询"模式中的对象。

ALL PRIVILEGES

 一次性给予所有适用于该对象的权限。 PRIVILEGES 关键字在 PostgreSQL 里是可选的, 但是严格的 SQL 要求有这个关键字。


 其它命令要求的权限都在相应的命令的参考页上列出。  

NOTES 注意

REVOKE [revoke(7)] 命令用于删除访问权限。


 我们要注意数据库超级用户可以访问所有对象, 而不会受对象的权限设置影响。这个特点类似 Unix 系统的 root  的权限。和 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 里只对某几列赋予权限, 你必须创建一个拥有那几行的视图然后给那个视图赋予权限。


 使用 psql(1) 的 \z 命令获取在现有对象上的与权限有关的信息。

=> \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 的权限
         uname=xxxx -- 赋予一个用户的权限
   group gname=xxxx -- 赋予一个组的权限

                  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)
                  * -- 给前面权限的授权选项

              /yyyy -- 授出这个权限的用户


 用户 miriam 在建完表之后再做下面的语句, 就可以得到上面例子的结果

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


 如果一个给定的对象的 "Access privileges" 字段是空的, 这意味着该对象有缺省权限(也就是说,它的权限字段是 NULL)。 缺省权限总是包括所有者的所有权限,以及根据对象的不同,可能包含一些给 PUBLIC  的权限。 对象上第一个 GRANT 或者 REVOKE  将实例化这个缺省权限(比如,产生 {=,miriam=arwdRxt}) 然后根据每次特定的需求修改它。  

EXAMPLES 例子


 把表 films 的插入权限赋予所有用户:

GRANT INSERT ON films TO PUBLIC;


 赋予用户manuel对视图kinds的所有权限:

GRANT ALL PRIVILEGES ON kinds TO manuel;

COMPATIBILITY 兼容性


 根据 SQL 标准,在 ALL PRIVILEGES 里的 PRIVILEGES  关键字是必须的。SQL 不支持在一条命令里对多个表设置权限。

SQL 标准允许在一个表里为独立的字段设置权限:

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

SQL 标准对其它类型的对象提供了一个 USAGE 权限:字符集,校勘,转换,域。

RULE 权限,以及在数据库,模式,语言和序列上的权限是 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)]

责任编辑:韩亚珊 来源: CMPP.net
相关推荐

2011-08-24 16:48:36

man中文man

2011-08-15 10:21:09

man中文man

2011-08-11 16:11:49

at中文man

2011-08-25 10:21:56

man.conf中文man

2011-11-01 13:46:50

中文mantac

2011-08-25 16:55:26

gets中文man

2011-08-25 17:18:07

putc中文man

2011-08-25 15:49:02

freopen中文man

2011-08-25 16:00:56

fflush中文man

2011-08-25 16:08:55

fsetpos中文man

2011-08-25 15:24:31

execlp中文man

2011-08-25 15:33:18

exit中文man

2011-08-15 10:00:08

lsattr中文man

2011-08-24 17:19:00

raw中文man

2011-08-25 10:55:37

services中文man

2011-08-25 09:35:26

units中文man

2011-08-24 16:16:07

LISTEN中文man

2011-08-24 13:57:35

DECLARE中文man

2011-08-11 15:28:43

ali中文man

2011-08-16 10:10:58

setup中文man
点赞
收藏

51CTO技术栈公众号