Seth Woolley's Man Viewer

grant(7) - GRANT - define access privileges - man 7 grant

([section] manual, -k keyword, -K [section] search, -f whatis)
man plain no title

GRANT(7)                         SQL Commands                         GRANT(7)



NAME
       GRANT - define access(2,5) 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(1,5)  or  groups  of  users. These privileges are added to those
       already granted, if(3,n) any.

       The key word PUBLIC indicates that the privileges are to be granted  to
       all  users(1,5),  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 mem-
       ber of, and privileges granted to PUBLIC.

       If  WITH  GRANT OPTION is specified, the recipient of the privilege may
       in(1,8) turn grant it to others. By  default  this  is  not  allowed.  Grant
       options  can only be granted to individual users(1,5), not to groups or PUB-
       LIC.

       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(1,8) any way is not described by a grantable privilege; it is inherent in(1,8)
       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(2,5) for tables and schemas; TEMP table creation privilege for  data-
       bases;  EXECUTE  privilege  for functions; and USAGE privilege for lan-
       guages.  The object owner may of course revoke these  privileges.  (For
       maximum security, issue the REVOKE in(1,8) the same transaction that creates
       the object; then there is no window in(1,8) which another user may  use  the
       object.)

       The possible privileges are:

       SELECT Allows  SELECT  [select(2,7,2 select_tut)(7)] from any column of the specified ta-
              ble, 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,n)(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 data-
              base.

              For schemas, allows new objects to be created within the schema.
              To  rename(1,2,n)  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  lan-
              guage  for  the  creation of functions in(1,8) that language. This is
              the only type of privilege that is applicable to procedural lan-
              guages.

              For schemas, allows access(2,5) to objects contained in(1,8) the specified
              schema (assuming that the objects'  own  privilege  requirements
              are  also  met).  Essentially  this allows the grantee to ``look(1,8,3 Search::Dict)
              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(1,8) 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(2,5) privileges.

       It  should  be  noted  that  database superusers can access(2,5) all objects
       regardless of object privilege settings.  This  is  comparable  to  the
       rights  of root in(1,8) 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(1,8) 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 privi-
       leges, 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(1,2)=xxxx -- privileges granted to a user
          group gname=xxxx -- privileges granted to a group

                         r -- SELECT ("read(2,n,1 builtins)")
                         w -- UPDATE ("write(1,2)")
                         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(1,5) 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(1,8) ALL PRIVI-
       LEGES is required. The SQL standard does not support setting the privi-
       leges 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)]



SQL - Language Statements         2003-11-02                          GRANT(7)

References for this manual (incoming links)