GRANT (storage location)

Grants privileges to users and roles on a USER-defined storage location.

Grants privileges to users and roles on a USER-defined storage location. For details, see Creating storage locations.

Syntax

GRANT { privilege[,...] | ALL [ PRIVILEGES ] }
   ON LOCATION 'path' [ ON node ]
   TO grantee[,...]
   [ WITH GRANT OPTION ]

Parameters

privilege
The following privileges are valid for storage locations:

  • READ: Copy data from files in the storage location into a table.

  • WRITE: Export data from the database to the storage location. With WRITE privileges, grantees can also save COPY statement rejected data and exceptions files to the storage location.

ALL [PRIVILEGES]
Grants all storage location privileges that also belong to the grantor. Grantors cannot grant privileges that they themselves lack.

The optional keyword PRIVILEGES conforms with the SQL standard.

ON LOCATION 'path' [ ON node]
Specifies the path name mount point of the storage location. If qualified by ON NODE, Vertica grants access to the storage location residing on node.

If no node is specified, the grant operation applies to all nodes on the specified path. All nodes must be on the specified path; otherwise, the entire grant operation rolls back.

grantee

Who is granted privileges, one of the following:

WITH GRANT OPTION

Allows the grantee to grant and revoke the same privileges to other users or roles. For details, see Granting privileges.

Privileges

Non-superuser, one of the following:

Examples

Create a storage location:

=> CREATE LOCATION '/home/dbadmin/UserStorage/BobStore' NODE 'v_mcdb_node0007' USAGE 'USER';
CREATE LOCATION

Grant user Bob all available privileges to the /BobStore location:

=> GRANT ALL ON LOCATION '/home/dbadmin/UserStorage/BobStore' TO Bob;
GRANT PRIVILEGE

Revoke all storage location privileges from Bob:

=> REVOKE ALL ON LOCATION '/home/dbadmin/UserStorage/BobStore' FROM Bob;
REVOKE PRIVILEGE

Grant privileges to Bob on the BobStore location again, specifying a node:

=> GRANT ALL ON LOCATION '/home/dbadmin/UserStorage/BobStore' ON v_mcdb_node0007 TO Bob;
GRANT PRIVILEGE

Revoke all storage location privileges from Bob:

=> REVOKE ALL ON LOCATION '/home/dbadmin/UserStorage/BobStore' ON v_mcdb_node0007 FROM Bob;
REVOKE PRIVILEGE

See also