We have migrated to new Google sites!
Post date: Apr 29, 2013 11:45:57 AM
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.
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-level role 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 role that created it), as the owner has all privileges by default. 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; CONNECT privilege and TEMP table creation privilege for databases;
EXECUTE privilege for functions; and USAGE privilege for languages. The object owner may of course revoke these privileges.
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 membership in the role as well. 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.
Unlike the case with privileges, membership in a role cannot be granted to PUBLIC.
SELECT :Allows SELECT from any column of the specified table, view, or sequence. Also allows the use of COPY TO. For sequences, this privilege also allows the use of the
currval function.
INSERT: Allows INSERT of a new row into the specified table. Also allows COPY FROM.
UPDATE:Allows UPDATE of any column of the specified table. SELECT ... FOR UPDATE and SELECT ... FOR SHARE also require this privilege (as well as the SELECT
privilege). For sequences, this privilege allows the use of the nextval and setval functions.
DELETE: Allows DELETE of a row from the specified table.
REFERENCES: This keyword is accepted, although foreign key constraints are currently not supported in Greenplum Database. 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.
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.)
CONNECT: Allows the user to connect to the specified database. This privilege is checked at connection startup (in addition to checking any restrictions imposed by
pg_hba.conf).
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 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 allows the grantee to look up objects within the schema. For sequences, this privilege allows the use of the currval and nextval functions.
ALL PRIVILEGES: Grant all of the available privileges at once. The PRIVILEGES key word is optional in Greenplum Database, though it is required by strict SQL.
PUBLIC: A special group-level role that denotes that the privileges are to be granted to all roles, including those that may be created later.
WITH GRANT OPTION: The recipient of the privilege may in turn grant it to others.
WITH ADMIN OPTION: The member of a role may in turn grant membership in the role to others.