GRANT (Storage Location)

Grants privileges to non-superusers or roles to read from or write to a Vertica storage location. First, a superuser creates a special class of storage location with the USER keyword through the usage parameter. Creating a storage location with a USER type specifies that the location can be made accessible to non-dbadmin users. The superuser must then grant users or roles the appropriate privileges through the GRANT (Storage Location) statement.

Note: GRANT/REVOKE (Storage Location) statements are applicable only to 'USER' storage locations. If the storage location is dropped, all privileges are revoked automatically.

Syntax

GRANT { READ | WRITE | ALL [ PRIVILEGES ] }
... ON LOCATION 'path' [ ON node ]
... TO { username | role | PUBLIC } [, ...]   
... [ WITH GRANT OPTION ]

Parameters

READ

Permits the grantee to copy data from files in the storage location into a table.

 
WRITE

Permits the grantee to 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

Grants all available privileges to the grantee for the storage location. 

PRIVILEGES

[Optional] For SQL standard compatibility and is ignored.

 
ON LOCATION 'path'  [ ON node ]
  • path — Specifies the path name mount point of the storage location
  • node — [Optional] Grants access to the storage location residing on the node. If you leave this blank, node defaults to all nodes on the specified path in that cluster. If a path exists for only some nodes, the entire grant rolls back, even on the nodes that reside in the path. 
{ username | role | PUBLIC } [,...]

Specifies the privilege grantee, which can be one or more users, one or more roles, or all users (PUBLIC).

  • username – A specific user
  • role – A particular role
  • PUBLIC – Grants the specified privileges to all users and roles. 
WITH GRANT OPTION

[Optional] Allows the grantee to grant the same privileges to others.

Notes

Only a superuser can add, alter, retire, drop, and restore a location. The superuser can grant only READ and/or WRITE access privileges to storage locations for other users or roles.

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

See Also