GRANT() SQL Commands GRANT()NAME
GRANT - Grants access privilege to a user, a group or all users
SYNOPSIS
GRANT privilege [, ...] ON object [, ...]
TO { PUBLIC | GROUP group | username }
INPUTS
privilege
The possible privileges are:
SELECT Access all of the columns of a specific table/view.
INSERT Insert data into all columns of a specific table.
UPDATE Update all columns of a specific table.
DELETE Delete rows from a specific table.
RULE Define rules on the table/view (See CREATE RULE state‐
ment).
ALL Grant all privileges.
object The name of an object to which to grant access. The possible
objects are:
· table
· view
· sequence
PUBLIC A short form representing all users.
GROUP group
A group to whom to grant privileges.
username
The name of a user to whom to grant privileges. PUBLIC is a
short form representing all users.
OUTPUTS
CHANGE Message returned if successful.
ERROR: ChangeAcl: class "object" not found
Message returned if the specified object is not available or if
it is impossible to give privileges to the specified group or
users.
DESCRIPTION
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 per‐
missions, after the object is created.
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.
NOTES
Currently, to grant privileges in Postgres to only a few columns, you
must create a view having desired columns and then grant privileges to
that view.
Use psql \z for further information about permissions on existing
objects:
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
Refer to REVOKE statements to revoke access privileges.
USAGE
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 ON kinds TO manuel;
COMPATIBILITY
SQL92
The SQL92 syntax for GRANT allows setting privileges for individual
columns within a table, and allows setting a privilege to grant the
same privileges to others:
GRANT privilege [, ...]
ON object [ ( column [, ...] ) ] [, ...]
TO { PUBLIC | username [, ...] } [ WITH GRANT OPTION ]
Fields are compatible with those in the Postgres implementation, with
the following additions:
privilege
SQL92 permits additional privileges to be specified:
SELECT
REFERENCES
Allowed to reference some or all of the columns of a spe‐
cific table/view in integrity constraints.
USAGE Allowed to use a domain, character set, collation or
translation. If an object specifies anything other than
a table/view, privilege must specify only USAGE.
object
[ TABLE ] table
SQL92 allows the additional non-functional keyword TABLE.
CHARACTER SET
Allowed to use the specified character set.
COLLATION
Allowed to use the specified collation sequence.
TRANSLATION
Allowed to use the specified character set translation.
DOMAIN Allowed to use the specified domain.
WITH GRANT OPTION
Allowed to grant the same privilege to others.
SQL - Language Statements 29 March 2001 GRANT()
