Vertica issue - Additional truncate privilege

sdscls · March 2016

When copy the privileges from tartget table to temp table, the temp table will get an additional truncate privilege which should have not been existed.

Adrian_Oprea_1 · March 2016

Can you describe better your situation ?! your phrase doesn't make to much sense .

sdscls · March 2016

Hi Adrian_oPREA,

Thanks for your response. I have attached the details in the attachment.

Adrian_Oprea_1 · March 2016

The answer is quite simple.

The user srvc_cdradmin_itg is the owner of the table. Or at least this is what i see .

Run this query to see :

select owner_name from tables where table_name='SRVC_OBLG_DOC_D_bld';

Or you can try to revoke truncate:

revoke truncate on <tbl> from <user>;

sdscls · March 2016

Hi Adrian_Oprea,

Thank you very much. And yes, the user srvc_cdradmin_itg is the owner of the table. And i want to know how could that happen as i didn't grant the truncate access to srvc_cdradmin_itg? And do you have any other ways to fix that except revoking? Thanks.

Adrian_Oprea_1 · March 2016

Well by default when you create a table you inherit the truncate grant on that object.

Try to grant this roles to another user(but the user who created the table) and see that the truncate will not be added.

Here is a short demo to see who owner ship populate the granted roles.

-- i have a table called bla
select grantor,privileges_description,object_name,grantee from grants where  object_name='bla';
grantor  privileges_description                                      object_name  grantee  
-------  ----------------------------------------------------------  -----------  -------  
dbadmin  INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES*, TRUNCATE*  bla          dbadmin  

-- change table owner from dbadmin to test user
ALTER TABLE bla owner TO test;
-- done 
-- see grants now

select grantor,privileges_description,object_name,grantee from grants where  object_name='bla';

-- nothing came back 

-- Now grant JUST select on table bla 
grant select on bla to test; 

--Check grants again

select grantor,privileges_description,object_name,grantee from grants where  object_name='bla';
grantor  privileges_description                                      object_name  grantee  
-------  ----------------------------------------------------------  -----------  -------  
test     INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES*, TRUNCATE*  bla          test     

--- hmmmm .. a bunch of grants came up , but we only give it select, Why ? Is the ownership inherited roles.

hope this makes sense and helped.

FiliN · March 2016

Did you grant USAGE on the table? USAGE priv grants truncate

sdscls · March 2016

Thank you very much.

sdscls · March 2016

Hi FiliN,

Thanks for you response. Usually i just grant the usage on the schema.

Vertica issue - Additional truncate privilege

Comments

Leave a Comment