File: sql-grant.html

package info (click to toggle)
mpsql 2.1-2
links: PTS
area: non-free
in suites: potato
size: 3,528 kB
ctags: 4,886
sloc: ansic: 35,184; makefile: 3,761; sh: 44
file content (605 lines) | stat: -rw-r--r-- 8,849 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML
><HEAD
><TITLE
>GRANT</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet version 1.19"><LINK
REL="HOME"
TITLE="PostgreSQL User's Guide"
HREF="user.html"><LINK
REL="UP"
TITLE="SQL Commands"
HREF="sql-commands.html"><LINK
REL="PREVIOUS"
TITLE="FETCH"
HREF="sql-fetch.html"><LINK
REL="NEXT"
TITLE="INSERT"
HREF="sql-insert.html"></HEAD
><BODY
BGCOLOR="#FFFFFF"
TEXT="#000000"
><DIV
CLASS="NAVHEADER"
><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>PostgreSQL User's Guide</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
HREF="sql-fetch.html"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="sql-insert.html"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
>GRANT</H1
><DIV
CLASS="REFNAMEDIV"
><H2
>Name</H2
>GRANT &#8212; Grants access privilege to a user, a group or all users</DIV
><DIV
CLASS="REFSYNOPSISDIV"
><PRE
CLASS="SYNOPSIS"
>GRANT <TT
CLASS="REPLACEABLE"
><I
>privilege</I
></TT
> [, ...]
    ON <TT
CLASS="REPLACEABLE"
><I
>object</I
></TT
> [, ...]
    TO { PUBLIC | GROUP <TT
CLASS="REPLACEABLE"
><I
>group</I
></TT
> | <TT
CLASS="REPLACEABLE"
><I
>username</I
></TT
> }</PRE
><DIV
CLASS="REFSECT2"
><H3
>Inputs</H3
><P
>&#13;<P
></P
></P><DL
><DT
><TT
CLASS="REPLACEABLE"
><I
>privilege</I
></TT
></DT
><DD
><P
>The possible privileges are:

<P
></P
></P><DL
><DT
>SELECT</DT
><DD
><P
>Access all of the columns of a specific
                        table/view.&#13;</P
></DD
><DT
>INSERT</DT
><DD
><P
>Insert data into all columns of a
                        specific table.&#13;</P
></DD
><DT
>UPDATE</DT
><DD
><P
>Update all columns of a specific
                        table.&#13;</P
></DD
><DT
>DELETE</DT
><DD
><P
>Delete rows from a specific table.&#13;</P
></DD
><DT
>RULE</DT
><DD
><P
>Define rules on the table/view
                        (See CREATE RULE statement).&#13;</P
></DD
><DT
>ALL</DT
><DD
><P
>Grant all privileges.&#13;</P
></DD
></DL
><P>&#13;</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>object</I
></TT
></DT
><DD
><P
>The name of an object to which to grant access.
          The possible objects are:

<P
></P
></P><UL
COMPACT="COMPACT"
><LI
STYLE="list-style-type: disc"
><P
>table &#13;</P
></LI
><LI
STYLE="list-style-type: disc"
><P
>view &#13;</P
></LI
><LI
STYLE="list-style-type: disc"
><P
>sequence&#13;</P
></LI
><LI
STYLE="list-style-type: disc"
><P
>index</P
></LI
></UL
><P>&#13;</P
></DD
><DT
>PUBLIC</DT
><DD
><P
>A short form representing all users.&#13;</P
></DD
><DT
>GROUP <TT
CLASS="REPLACEABLE"
><I
>group</I
></TT
></DT
><DD
><P
>A <TT
CLASS="REPLACEABLE"
><I
>group</I
></TT
> to whom to grant privileges.
In the current release, the group must be created explicitly as described below.&#13;</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>username</I
></TT
></DT
><DD
><P
>The name of a user to whom grant privileges. PUBLIC is a short form
representing all users.&#13;</P
></DD
></DL
><P>&#13;</P
></DIV
><DIV
CLASS="REFSECT2"
><H3
>Outputs</H3
><P
>&#13;<P
></P
></P><DL
><DT
>CHANGE</DT
><DD
><P
>Message returned if successful.&#13;</P
></DD
><DT
>ERROR:  ChangeAcl: class "<TT
CLASS="REPLACEABLE"
><I
>object</I
></TT
>"
 not found</DT
><DD
><P
>Message returned if the specified object is not available or 
if it is impossible
          to give privileges to the specified group or users.&#13;</P
></DD
></DL
><P>&#13;</P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>Description</H2
><P
>   GRANT allows the creator of an object to give specific permissions to
   all users (PUBLIC) or to a certain user or group. 
   Users other than the creator don't have any access permission 
   unless the creator GRANTs permissions, after the object
   is created.&#13;</P
><P
>   Once a user has a privilege on an object, he is enabled to exercise
that privilege.
There is no need to GRANT privileges to the creator of 
   an object, the creator automatically holds ALL privileges, and can 
   also drop the object. &#13;</P
><DIV
CLASS="REFSECT2"
><H3
>Notes</H3
><P
>Use the <B
CLASS="COMMAND"
>psql \z</B
> command
 for further information about permissions 
         on existing objects:
<PRE
CLASS="PROGRAMLISTING"
>   Database    = lusitania
   +------------------+---------------------------------------------+
   |  Relation        |        Grant/Revoke Permissions             |
   +------------------+---------------------------------------------+
   | mytable          | {"=rw","miriam=arwR","group todos=rw"}      |
   +------------------+---------------------------------------------+
   Legend:
         uname=arwR -- privileges granted to a user
   group gname=arwR -- privileges granted to a GROUP
              =arwR -- privileges granted to PUBLIC

                  r -- SELECT
                  w -- UPDATE/DELETE
                  a -- INSERT
                  R -- RULE
               arwR -- ALL</PRE
>

<BLOCKQUOTE
CLASS="TIP"
><P
><B
>Tip: </B
>Currently, to create a GROUP you have to insert 
          data manually into table pg_group as:
<PRE
CLASS="PROGRAMLISTING"
>INSERT INTO pg_group VALUES ('todos');
CREATE USER miriam IN GROUP todos;</PRE
>
   Refer to REVOKE statements to revoke access privileges.</P
></BLOCKQUOTE
>&#13;</P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>Usage</H2
><P
><PRE
CLASS="PROGRAMLISTING"
>-- grant insert privilege to all users on table films:
--
GRANT INSERT ON films TO PUBLIC;</PRE
>

<PRE
CLASS="PROGRAMLISTING"
>-- grant all privileges to user manuel on view kinds:
--
GRANT ALL ON kinds TO manuel;</PRE
>&#13;</P
></DIV
><DIV
CLASS="REFSECT1"
><H2
>Compatibility</H2
><P
></P
><DIV
CLASS="REFSECT2"
><H3
>SQL92</H3
><P
>   The <SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> syntax for GRANT allows setting privileges 
for individual columns
within a table, and allows setting a privilege to grant
the same privileges to others.

<PRE
CLASS="SYNOPSIS"
>GRANT <TT
CLASS="REPLACEABLE"
><I
>privilege</I
></TT
> [, ...]
    ON <TT
CLASS="REPLACEABLE"
><I
>object</I
></TT
> [ ( <TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
> [, ...] ) ] [, ...]
    TO { PUBLIC | <TT
CLASS="REPLACEABLE"
><I
>username</I
></TT
> [, ...] }
    [ WITH GRANT OPTION ]</PRE
>

Fields are compatible with the those in the <SPAN
CLASS="ACRONYM"
>Postgres</SPAN
>
implementation, with the following additions:

<P
></P
></P><DL
><DT
><TT
CLASS="REPLACEABLE"
><I
>privilege</I
></TT
>
SELECT</DT
><DD
><P
><SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> permits additional privileges to be specified:

<P
></P
></P><DL
><DT
>REFERENCES</DT
><DD
><P
>Allowed to reference some or all of the columns of a specific
table/view in integrity constraints.&#13;</P
></DD
><DT
>USAGE</DT
><DD
><P
>Allowed to use a domain, character set, collation
                    or translation.
                    If an object specifies anything other than a table/view,
<TT
CLASS="REPLACEABLE"
><I
>privilege</I
></TT
>
must specify only USAGE.&#13;</P
></DD
></DL
><P>
 
<BLOCKQUOTE
CLASS="TIP"
><P
><B
>Tip: </B
>Currently, to grant privileges in <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>
to only few columns, you must
           create a view having desired columns and then grant privileges
           to that view.</P
></BLOCKQUOTE
>&#13;</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>object</I
></TT
></DT
><DD
><P
>&#13;<P
></P
></P><DL
><DT
><TT
CLASS="REPLACEABLE"
><I
>object</I
></TT
></DT
><DD
><P
><SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> allows an additional non-functional keyword:

<P
></P
><TABLE
BORDER="0"
><TR
><TD
>[ TABLE ] table </TD
></TR
></TABLE
><P
></P
>&#13;</P
></DD
><DT
>CHARACTER SET</DT
><DD
><P
>Allowed to use the specified character set.&#13;</P
></DD
><DT
>COLLATION</DT
><DD
><P
>Allowed to use the specified collation sequence.&#13;</P
></DD
><DT
>TRANSLATION</DT
><DD
><P
>Allowed to use the specified character set translation.&#13;</P
></DD
><DT
>DOMAIN</DT
><DD
><P
>Allowed to use the specified domain.&#13;</P
></DD
></DL
><P>
&#13;</P
></DD
><DT
>WITH GRANT OPTION</DT
><DD
><P
>Allowed to grant the same privilege to others.&#13;</P
></DD
></DL
><P></P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="sql-fetch.html"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="user.html"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="sql-insert.html"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>FETCH</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="sql-commands.html"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>INSERT</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>