USER Storage Location Types

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1775
Joined: Sat Jan 21, 2012 4:58 am
Contact:

USER Storage Location Types

Post by JimKnicely » Wed Apr 17, 2013 5:00 pm

Hi,

New in Vertica 6.1.1 is the USER storage location type. This storage type allows a non-dbadmin user on a remote client to load data that resides on the database server into the database.

Example

I'll try to load some data remotely via vsql as the non-dbadmin user jim:

Code: Select all

[root@cognosdb1 ~]# vsql -h verticatst01 -U jim -c "COPY jim_schema.test FROM '/usr/home/dbadmin/jim.txt' EXCEPTIONS '/usr/home/dbadmin/jim.err';"
ERROR 4368:  Permission denied for storage location [/usr/home/dbadmin/jim.txt]
In Vertica 6.1.1 I can now create a USER storage location and grant privileges to jim on it:

Code: Select all

dbadmin=> SELECT add_location('/usr/home/dbadmin', '', 'USER', 'JIM');
       add_location
--------------------------
/usr/home/dbadmin added.
(1 row)

dbadmin=> GRANT ALL ON LOCATION '/usr/home/dbadmin' TO jim;
GRANT PRIVILEGE
Then I tried the remote copy again:

Code: Select all

[root@cognosdb1 ~]# vsql -h verticatst01 -U jim -c "COPY jim_schema.test FROM '/usr/home/dbadmin/jim.txt' EXCEPTIONS '/usr/home/dbadmin/jim.err';"
Rows Loaded
-------------
           3
(1 row)
It works now!

Have fun!
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Tips, Lessons and Examples”