GRANT (Storage Location)

Grants privileges to users and roles on a USER-defined storage location. For more information, see Creating Storage Locations in the Administrator's Guide.

Syntax

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

Parameters

privilege

One of the following privileges:

  • 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. For details, see How to Grant Privileges in the Administrator's Guide.

Privileges

One of the following:

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

Examples

In the following series of commands, a superuser creates a new storage location and grants it to user Bob:

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

Now the superuser grants a user named 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