Issue with roles and grants

Hi  

 

 

I created a user Fred.

then I created a role reporter and assigned Fred to it using following:

 

GRANT reporter to Fred

 

Then i gave the following rights to the role reporter

 

GRANT SELECT ON <schema_name> to reporter
GRANT ALL ON ALL TABLES IN SCHEMA <schema_name>  TO reporter

 

Then when I login as user Fred , I set the role

 

SET role reporter

 

but as Fred , I cant see the tables in the SCHEMA <schema_name>.  I can see the SCHEMA_NAME in the SQL editor navigation but I cant see the tables.

 

What am i doing wrong here? Is there any thing wrong here? Am i missing something.

 

It works when i grant the access on the schema directly to the uers i.e 

 

GRANT SELECT ON <schema_name> to Fred

GRANT ALL ON ALL TABLES IN SCHEMA <schema_name>  TO Fred

 

It works fine in that case and the user Fred can see the tables in the Sql editor navigator.

 

 

What am i doing wrong here? Why is the role not working properly?

 

-Sameer

 

Comments

  • Try this as Fred and send the outcome

     => SET ROLE ALL;SET

    => SHOW ENABLED_ROLES;

  • Hi Sameer,

     

    Try granting USAGE on the schema to the role as well.

     

      --Sharon

     

  • I actually tried this

     

    GRANT ALL ON <schema_name> to Fred

    GRANT ALL ON ALL TABLES IN SCHEMA <schema_name>  TO Fred

     

    and then again checked Fred, and it couldnt see all the tables. 

    I am granting all that i can to Fred. Is there something else that i need to check as well.

     

    Regards, 

    Sameer Durrani

  • Grant the Role to Fred after you give the permissions to the Role! 

  • > GRANT ALL ON <schema_name> to Fred

     

    The syntax for granting to a schema is GRANT ... ON SCHEMA <schema_name>.    

     

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.