Vertica issue - Additional truncate privilege

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.

Comments

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

     

  • Hi Adrian_oPREA,

     

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

     

  •  

     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>;
  • 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. 

  •  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.

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

  • Thank you very much.

  • Hi FiliN,

     

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.