How to grant all objects under a schema to a user.

Hi,
I have a schema A, and a user B. How can I grant all objects under schema A to user B.
I have used the following statement, but it seems it don't make sense, since I can't select table under that schema A by user B.
GRANT USAGE ON SCHEMA A to B;

Anybody know how to grant all objects under a schema to a user. thanks.

 

Comments

  • Hi,

    What Vertica version are you on.

    Starting version 6.1, we can provide grant on all tables in a schema using "ON ALL TABLES"option

    vsql=> GRANT SELECT ON ALL TABLES IN SCHEMA <schema  name> TO <user name>;


    You can read more about this in Vertica Doc:

    SQL Reference Manual --> SQL Statements --> Grant Statements --> GRANT (Table)

    https://my.vertica.com/docs/6.1.x/HTML/index.htm#3044.htm

    T
    hanks,
    Pravesh
  • Hi,

    Grant USAGE allows the user access to the objects contained within the schema. This allows the user to look up objects within the schema.  But along with this, user must also be granted access to the individual objects.

    Example: 
     grant select on A.<table_name> to B;

    Now user B would be able to run select queries on A.<table_name>.

    If you want to grant all objects under a schema to a user, you can run a small script for the same.

    #!/bin/bash
    user=B
    schema=A
    vsql -ta -w password -c " select 'grant all privileges on table ' || table_schema || '.' || table_name || ' to $user ;' from tables where table_schema = '$schema'; " | vsql


    Note: Pleases run GRANT USAGE ON SCHEMA A to B; before running the script.
               Grant all used in script will grant all privileges to user B on Schema A. I fyou want to grant select only, Please use 'Grant select' instead of '        'grant all'.

    Thanks,
    Vivek 

     

  • Hi Bhardwaj,

    Thank you , it is great, it works!


    Martin

  • Hi Kumar,

    It is a good idea of using the vsql to grant the privileges. Thank you.


    Martin
  • Hi,

    Does the GRANT <privileges> ON ALL TABLES IN SCHEMA <schema  name> TO <user name>; apply the privileges (retroactively) to any tables created in the same schema after the GRANT statement was executed?

    Thanks... Vince
  • sreeblrsreeblr - Select Field - Employee

    as i could see tables after the GRANT ON ALL TABLES IN SCHEMA TO ; dont get added to grants hence guess we need to give grants to new tables.

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    Tables can inherit grants from schemas!

    See:
    https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Security/DBUsersAndPrivileges/GrantInheritedPrivileges.htm

    You can ALTER a current schema so that new grants can be inherited by new tables created in that schema...

    dbadmin=> alter schema vertica_stuff default include privileges;
    ALTER SCHEMA
    

    You have to re-run the GRANT SELECT ON ALL ... statement again after running ALTER SCHEMA statement,

    dbadmin=> grant select on all tables in schema vertica_stuff to jim1;
    GRANT PRIVILEGE
    
  • Thanks Jim! Information that you posted here is really useful

Leave a Comment

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