GRANT -- define access privileges
Synopsis
GRANT { { SELECT | INSERT | UPDATE | DELETE } [,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] tablename [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ] [ CASCADE ] GRANT { { CREATE | CONNECT } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE dbname [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] } ON SCHEMA schemaname [, ...]
TO { username | GROUP rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ] GRANT { INSTALL FILE | CREATE FUNCTION } [, ...] [ PRIVILEGE ]
ON SCHEMA schemaname [, ...]
TO { username | GROUP rolename | PUBLIC } [, ...] GRANT EXECUTE [ PRIVILEGE ]
ON FUNCTION [schemaname.]funcname
TO { username | GROUP rolename | PUBLIC } [, ...]
GRANT rolename [, ...]
TO username [, ...] [ WITH ADMIN OPTION ];
Description
The GRANT command has two basic variants: one that grants privileges on a database object like a table or database (see “GRANT on Database Objects” on page V-61) and one that grants membership in a role (see “GRANT on Roles” on page V-63). These variants are similar in many ways, but they are different enough that we’ll describe them separately, below.
GRANT on Database Objects
This variant of the GRANT command gives privileges on a database object to one or more roles. These privileges are added to those already granted, if any.
The keyword PUBLIC specifies that the privileges are to be granted to all roles, including those that might be created later. PUBLIC can 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.
If CASCADE is specified, then the rights you grant on a parent table cascade to all its child tables.
CASCADE works only when granting table privileges.
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 right to drop an object, or to alter its definition in any
GRANT Aster Data proprietary and confidential
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 might include granting some privileges to PUBLIC. The default is no public access for tables and schemas; CONNECT privilege for databases. The object owner can 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 can use the object.)
The possible privileges are:
CREATE For databases, gives the user/role the right to create new schemas in the database. Note! Granting CREATE on a database does not confer the right to create tables. To do that, you must grant the user CREATE on a schema in the database.
Granting CREATE on a schema gives the user or role the right to create new tables and objects in the schema. To rename an existing object, you must own the object and have this privilege for the containing schema. See also, “Revoking Users Rights to Create Tables” on page V-72.
SELECT Allows SELECT from any column of the specified table. Also allows the use of COPY TO. This privilege is also needed to reference existing column values in UPDATE or DELETE.
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. (In practice, any nontrivial UPDATE command will require SELECT privilege as well, since it must reference table columns to determine which rows to update, and/or to compute new values for columns.)
USAGE Granting USAGE on a schema gives the user or role the right to access 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.
DELETE Allows DELETE of a row from the specified table. (In practice, any nontrivial
DELETE command will require SELECT privilege as well, since it must reference table columns to determine which rows to delete.)
CONNECT Allows the user to connect to the specified database. This privilege is checked when the user attempts to connect. For new databases you create, only you have the CONNECT privilege. If you want other users to be able to CONNECT to a database, you must GRANT CONNECT on the database to the user or group. For example, you can give all users the right to connect as shown here:
GRANT CONNECT ON DATABASE retail_sales TO PUBLIC;
INSTALL FILE, CREATE FUNCTION Allow the user to upload and install files and
SQL-MapReduce functions, respectively, in the schema. See “SQL-MapReduce Security” on page I-79.
EXECUTE Allows the user to run the SQL-MapReduce function. See “SQL-MapReduce Security” on page I-79.
ALL PRIVILEGES Grant all of the available privileges at once. The PRIVILEGES keyword is optional.
Aster Data proprietary and confidential GRANT