GRANT
SQL - Language Statements
GRANT
Grants access privilege to a user, a group or all users
1999-07-20
GRANT privilege [, ...] ON object [, ...]
TO { PUBLIC | GROUP group | username }
1998-09-23
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 statement).
ALL
Grant all privileges.
object
The name of an object to which to grant access.
The possible objects are:
table
view
sequence
index
PUBLIC
A short form representing all users.
GROUP group
A group to whom to grant privileges.
In the current release, the group must be created explicitly as described below.
username
The name of a user to whom grant privileges. PUBLIC is a short form
representing all users.
1998-09-23
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.
1998-09-23
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 permissions, 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.
1998-09-23
Notes
Currently, to grant privileges in Postgres
to only 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
Currently, to create a GROUP you have to insert
data manually into table pg_group as:
INSERT INTO pg_group VALUES ('todos');
CREATE USER miriam IN GROUP todos;
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
1998-09-23
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 the 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 specific
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.