vertica permission

@sreeblr
Ran the test in 9.0 and 10.0 and it's fail in both the Vesion with same error Insufficient privilege.
After giving grant to schema and table working ok and behavior same on the both version.
If I have missed any steps, can you provide working example from both the version?
GRANT CREATE, USAGE ON SCHEMA tt to tom1;
GRANT SELECT, INSERT, UPDATE ON TABLE tt.test2 TO tom1 WITH GRANT OPTION;

Version 10.0

dbadmin=> select count() from aaa.t1;
ERROR 3580: Insufficient privilege: USAGE on SCHEMA 'aaa' not granted for current user
dbadmin=>
dbadmin=> select count() from t100;

count

76349
(1 row)

Version 9.0

dbadmin=> \c - tom1
You are now connected as user "tom1".
dbadmin=> select count() from tt.test2;
ERROR 3580: Insufficient privilege: USAGE on SCHEMA 'tt' not granted for current user
dbadmin=>
dbadmin=> select count() from test;

count

 9

(1 row)

Hi Nimmi , We earlier had only usage privilege on schema to user/role. and the GRANT ALL ON ALL TABLES IN SCHEMA <> to <> user or role and DML's worked . Will try to create scenario and check

Hi Nimmi , Thanks for your instant response as always
In your example could you try in 9.x with usage on SCHEMA 'tt (without create) and then try the insert.
We were able to insert in 9.x but without create privilege on schema it is not working with 10.x

Below example from 9.x
dbadmin=> select version();

version

Vertica Analytic Database v9.0.1-8
(1 row)
dbadmin=> CREATE USER bob;
CREATE USER
dbadmin=> create schema bob_schema;
CREATE SCHEMA
dbadmin=> create table bob_schema.bob_test(i int);
CREATE TABLE
dbadmin=> GRANT USAGE ON SCHEMA bob_schema to bob;
GRANT PRIVILEGE
dbadmin=> \c - bob
You are now connected as user "bob".
dbadmin=> insert into bob_schema.bob_test values(12);
ERROR 4367: Permission denied for relation bob_test
Worked after running grant on select, insert and update on the table we tried to insert to as a bob user.
dbadmin=> GRANT SELECT, INSERT, UPDATE ON TABLE bob_schema.bob_test TO bob WITH GRANT OPTION;
GRANT PRIVILEGE
dbadmin=>
dbadmin=> \c - bob
You are now connected as user "bob".
dbadmin=>
dbadmin=> insert into bob_schema.bob_test values(12);

OUTPUT

  1

(1 row)

Yes Nimmi,
This was working with 9.2 but when i run insert without create privilege for user in 10.x getting error. Could you revoke Create privilege on schema to user in 10.x environment and try inserting maybe into a new table?

@sreeblr - Is this an example of what you seeing?

dbadmin=> CREATE SCHEMA test;
CREATE SCHEMA

dbadmin=> CREATE TABLE test.test (c2 INT NOT NULL, c1 VARCHAR NOT NULL);
CREATE TABLE

dbadmin=> ALTER TABLE test.test ADD CONSTRAINT test_pk PRIMARY KEY(c2) ENABLED;;
ALTER TABLE

dbadmin=> CREATE USER test;
CREATE USER

dbadmin=> GRANT USAGE ON SCHEMA test TO test;
GRANT PRIVILEGE

dbadmin=> GRANT ALL ON ALL TABLES IN SCHEMA test TO test;
GRANT PRIVILEGE

dbadmin=> \c - test
You are now connected as user "test".

dbadmin=> INSERT INTO test.test SELECT 1, 'A';
ERROR 6774:  Failed to create default key projections for table "test"."test": Permission denied for schema test
HINT:  Ensure that tables involved in the query and their key projections exist (DDL interference)

Yes @Jim_Knicely , this is exactly what i see .

So the second INSERT works...

dbadmin=> DROP TABLE test.test;
DROP TABLE

dbadmin=> CREATE TABLE test.test (c2 INT NOT NULL, c1 VARCHAR NOT NULL);
CREATE TABLE

dbadmin=> ALTER TABLE test.test ADD CONSTRAINT test_pk PRIMARY KEY(c2) ENABLED;
ALTER TABLE

dbadmin=> GRANT ALL ON ALL TABLES IN SCHEMA test TO test;
GRANT PRIVILEGE

dbadmin=> \c - test
You are now connected as user "test".

dbadmin=> INSERT INTO test.test SELECT 1, 'A';
ERROR 6774:  Failed to create default key projections for table "test"."test": Permission denied for schema test
HINT:  Ensure that tables involved in the query and their key projections exist (DDL interference)

dbadmin=> INSERT INTO test.test SELECT 1, 'A';
 OUTPUT
--------
      1
(1 row)

I opened Jira VER-76584 per this issue.

Thanks Jim, for now workaround would be to grant create schema privilege? Asking since we cant run insert twice on all tables in client environments.

Maybe you can create the projection manually prior to the user INSERTing...

dbadmin=> \c
You are now connected as user "dbadmin".

dbadmin=> DROP TABLE test.test CASCADE;
DROP TABLE

dbadmin=> CREATE TABLE test.test (c2 INT NOT NULL, c1 VARCHAR NOT NULL);
CREATE TABLE

dbadmin=> ALTER TABLE test.test ADD CONSTRAINT test_pk PRIMARY KEY(c2) ENABLED;
ALTER TABLE

dbadmin=> CREATE PROJECTION test.test_super AS SELECT c2, c1 FROM test.test ORDER BY test.c2 SEGMENTED BY HASH(c2) ALL NODES;
CREATE PROJECTION

dbadmin=> GRANT ALL ON ALL TABLES IN SCHEMA test TO test;
GRANT PRIVILEGE

dbadmin=> \c - test
You are now connected as user "test".

dbadmin=> INSERT INTO test.test SELECT 1, 'A';
 OUTPUT
--------
      1
(1 row)

Or add ORDER BY and SEGMENTED BY clauses to the CREATE TABLE statement (which will create a super projection that'll enforce the PK for you):

dbadmin=> \c
You are now connected as user "dbadmin".

dbadmin=>  DROP TABLE test.test CASCADE;
DROP TABLE

dbadmin=> CREATE TABLE test.test (c2 INT NOT NULL, c1 VARCHAR NOT NULL) ORDER BY c2 SEGMENTED BY HASH(c2) ALL NODES;
CREATE TABLE

dbadmin=> GRANT ALL ON ALL TABLES IN SCHEMA test TO test;
GRANT PRIVILEGE

dbadmin=> \c - test
You are now connected as user "test".

dbadmin=> INSERT INTO test.test SELECT 1, 'A';
 OUTPUT
--------
      1
(1 row)

i tried insert twice but still got same permission issue.