GRANT (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

Specifies who is granted privileges, one of the following:
WITH GRANT OPTION

Gives grantee the privilege to grant the same privileges to other users or roles, and also revoke them. For details, see Granting Privileges.

Privileges

Non-superuser, one of the following:

Only a superuser can add, alter, retire, drop, and restore a location.

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