Vertica issue - Additional truncate privilege

sdscls

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

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

 

sdscls

Hi Adrian_oPREA,

 

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

 

Adrian_Oprea_1

 

 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

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

 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

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

sdscls

Thank you very much.

sdscls

Hi FiliN,

 

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