Hi,
I have a Vertica DB user that has INSERT,SELECT access on a table. When I try to load data into the table, it fails with error:
Failed to create default projections for table, permission denied for schema
Thanks.
Privileges to load data
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Privileges to load data
Hi Sean,
Did you grant the user the USAGE privilege on the schema too?
Did you grant the user the USAGE privilege on the schema too?
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Privileges to load data
I'm having trouble duplicating your issue in Vertica 6.0.3.
I didn't have to grant any other privileges to the user JIM...
From page 159 of the 6.0.3 Administrator's Guide:
Code: Select all
dbadmin=> create schema test;
CREATE SCHEMA
dbadmin=> create user jim;
CREATE USER
dbadmin=> grant usage on schema test to jim;
GRANT PRIVILEGE
dbadmin=> create table test.t1 (c1 int);
CREATE TABLE
dbadmin=> grant insert, select on test.t1 to jim;
GRANT PRIVILEGE
dbadmin=> \c mydb jim
You are now connected to database "mydb" as user "jim".
intersect=> copy test.t1 from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1
>> \.
intersect=> select * from test.t1;
c1
----
1
(1 row)
From page 159 of the 6.0.3 Administrator's Guide:
Who is the owner of the table you are having an issue with and in which schema is it?Implicit projection creation and privileges
Projections get implicitly created when you insert data into a table, an operation that automatically creates a superprojection for the table.
Implicitly-created projections do not require any additional privileges to create or drop, other than privileges for table creation. Users who can create a table or drop a table can also create and drop the associated superprojection.
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Privileges to load data
I'm on Vertica version 5.1.4.
My user has USAGE privileges on the schema but i still wasn't able to create projections on the tables. The DBA gave CREATE privileges to the user and it worked then. But glad to know that version 6 does need any explicit grants for projections. we are in the process of moving to the latest version.
Thanks for your help knicely.
Sean
My user has USAGE privileges on the schema but i still wasn't able to create projections on the tables. The DBA gave CREATE privileges to the user and it worked then. But glad to know that version 6 does need any explicit grants for projections. we are in the process of moving to the latest version.
Thanks for your help knicely.
Sean