|
Hopefully, this page is exactly what you are looking for, but if not, you can always find further assistance on Unix/Linux Forum!
SQL Commands GRANT()
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 ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
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 ]
GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespacename [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT role [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH ADMIN OPTION ]
DESCRIPTION
The GRANT command has two basic variants: one that grants
privileges on a database object (table, view, sequence,
database, function, procedural language, schema, or
tablespace), and one that grants membership in a role. These
variants are similar in many ways, but they are different
enough to be described separately.
As of PostgreSQL 8.1, the concepts of users and groups have
been unified into a single kind of entity called a role. It
is therefore no longer necessary to use the keyword GROUP to
identify whether a grantee is a user or a group. GROUP is
still allowed in the command, but it is a noise word.
GRANT ON DATABASE OBJECTS
This variant of the GRANT command gives specific privileges
on a database object to one or more roles. These privileges
are added to those already granted, if any.
SQL - Language StatemLast change: 2005-11-05 1
SQL Commands GRANT()
The key word PUBLIC indicates that the privileges are to be
granted to all roles, including those that may be created
later. PUBLIC may be thought of as an implicitly defined
group that always includes all roles. Any particular role
will have the sum of privileges granted directly to it,
privileges granted to any role it 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. Without a grant
option, the recipient cannot do that. Grant options cannot
be granted to 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. The owner
implicitly has all grant options for the object, too.
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, schemas, and
tablespaces; 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(5)] from any column of the speci-
fied table, view, or sequence. Also allows the use of
COPY [copy(5)] TO. For sequences, this privilege also
allows the use of the currval function.
INSERT
Allows INSERT [insert(5)] of a new row into the speci-
fied table. Also allows COPY [copy(5)] FROM.
UPDATE
Allows UPDATE [update(5)] of any column of the speci-
fied table. SELECT ... FOR UPDATE and SELECT ... FOR
SHARE also require this privilege (besides the SELECT
privilege). For sequences, this privilege allows the
use of the nextval and setval functions.
DELETE
SQL - Language StatemLast change: 2005-11-05 2
SQL Commands GRANT()
Allows DELETE [delete(5)] of a row from the specified
table.
RULE Allows the creation of a rule on the table/view. (See
the CREATE RULE [create_rule(5)] statement.)
REFERENCES
To create a foreign key constraint, it is necessary to
have this privilege on both the referencing and refer-
enced tables.
TRIGGER
Allows the creation of a trigger on the specified
table. (See the CREATE TRIGGER [create_trigger(5)]
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.
For tablespaces, allows tables and indexes to be
created within the tablespace, and allows databases to
be created that have the tablespace as their default
tablespace. (Note that revoking this privilege will not
alter the placement of existing objects.)
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 func-
tion. This is the only type of privilege that is appli-
cable to functions. (This syntax works for aggregate
functions, as well.)
USAGE
For procedural languages, allows the use of the speci-
fied 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
SQL - Language StatemLast change: 2005-11-05 3
SQL Commands GRANT()
allows the grantee to ``look up'' objects within the
schema.
ALL PRIVILEGES
Grant all of the available privileges 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.
GRANT ON ROLES
This variant of the GRANT command grants membership in a
role to one or more other roles. Membership in a role is
significant because it conveys the privileges granted to a
role to each of its members.
If WITH ADMIN OPTION is specified, the member may in turn
grant membership in the role to others, and revoke member-
ship in the role as well. Without the admin option, ordinary
users cannot do that. However, database superusers can grant
or revoke membership in any role to anyone. Roles having
CREATEROLE privilege can grant or revoke membership in any
role that is not a superuser.
NOTES
The REVOKE [revoke(5)] command is used to revoke access
privileges.
When a non-owner of an object attempts to GRANT privileges
on the object, the command will fail outright if the user
has no privileges whatsoever on the object. As long as some
privilege is available, the command will proceed, but it
will grant only those privileges for which the user has
grant options. The GRANT ALL PRIVILEGES forms will issue a
warning message if no grant options are held, while the
other forms will issue a warning if grant options for any of
the privileges specifically named in the command are not
held. (In principle these statements apply to the object
owner as well, but since the owner is always treated as
holding all grant options, the cases can never occur.)
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
SQL - Language StatemLast change: 2005-11-05 4
SQL Commands GRANT()
by the object owner. (For role membership, the membership
appears to have been granted by the containing role itself.)
GRANT and REVOKE can also be done by a role that is not the
owner of the affected object, but is a member of the role
that owns the object, or is a member of a role that holds
privileges WITH GRANT OPTION on the object. In this case the
privileges will be recorded as having been granted by the
role that actually owns the object or holds the privileges
WITH GRANT OPTION. For example, if table t1 is owned by role
g1, of which role u1 is a member, then u1 can grant
privileges on t1 to u2, but those privileges will appear to
have been granted directly by g1. Any other member of role
g1 could revoke them later.
If the role executing GRANT holds the required privileges
indirectly via more than one role membership path, it is
unspecified which containing role will be recorded as having
done the grant. In such cases it is best practice to use SET
ROLE to become the specific role you want to do the GRANT
as.
Currently, PostgreSQL does not support granting or revoking
privileges for individual columns of a table. One possible
workaround is to create a view having just the desired
columns and then grant privileges to that view.
Use psql(1)'s \z command to obtain information about exist-
ing privileges, for example:
=> \z mytable
Access privileges for database "lusitania"
Schema | Name | Type | Access privileges
--------+---------+-------+------------------------------------------------------------
public | mytable | table | {miriam=arwdRxt/miriam,=r/miriam,"group todos=arw/miriam"}
(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
SQL - Language StatemLast change: 2005-11-05 5
SQL Commands GRANT()
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/miriam}) and then modify them per the speci-
fied request.
Notice that the owner's implicit grant options are not
marked in the access privileges display. A * will appear
only when grant options have been explicitly granted to
someone.
EXAMPLES
Grant insert privilege to all users on table films:
GRANT INSERT ON films TO PUBLIC;
Grant all available privileges to user manuel on view kinds:
GRANT ALL PRIVILEGES ON kinds TO manuel;
Note that while the above will indeed grant all privileges
if executed by a superuser or the owner of kinds, when exe-
cuted by someone else it will only grant those permissions
for which the someone else has grant options.
Grant membership in role admins to user joe:
GRANT admins TO joe;
COMPATIBILITY
SQL - Language StatemLast change: 2005-11-05 6
SQL Commands GRANT()
According to the SQL standard, the PRIVILEGES key word in
ALL PRIVILEGES is required. The SQL standard does not sup-
port setting the privileges on more than one object per com-
mand.
PostgreSQL allows an object owner to revoke his own ordinary
privileges: for example, a table owner can make the table
read-only to himself by revoking his own INSERT, UPDATE, and
DELETE privileges. This is not possible according to the SQL
standard. The reason is that PostgreSQL treats the owner's
privileges as having been granted by the owner to himself;
therefore he can revoke them too. In the SQL standard, the
owner's privileges are granted by an assumed entity ``_SYS-
TEM''. Not being ``_SYSTEM'', the owner cannot revoke these
rights.
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,
tablespaces, schemas, languages, and sequences are Post-
greSQL extensions.
SEE ALSO
REVOKE [revoke(5)]
SQL - Language StatemLast change: 2005-11-05 7
Man(1) output converted with
man2html and wrapped by fishsponge
This page was generated on Wed Sep 12 11:27:48 GMT 2007
|
Your favourite pages:
No pages logged yet.
Trying to save cookie... Top 10 most popular pages:
sqlite3 man page (5334 hits)
(openSUSE 10.2)
svn man page (5208 hits)
(FreeBSD 6.2)
adv_cap_autoneg man page (4870 hits)
(Solaris 10 11_06)
CPAN man page (4607 hits)
(Suse Linux 10.1)
ssh man page (4342 hits)
(Suse Linux 10.1)
ssh-socks5-proxy-connect man page (2877 hits)
(Solaris 10 11_06)
netcat man page (2717 hits)
(Suse Linux 10.1)
pprosetup man page (2488 hits)
(Solaris 10 11_06)
startproc man page (2471 hits)
(Suse Linux 10.1)
signal man page (2407 hits)
(Suse Linux 10.1)
|
Man Pages 
