Using Snowflake, Changing Roles, how to get DB Explorer to Update

[Lee_Str]

In snowflake, you have access Roles, which you change to gain access to different objects.

Role1 can see 4 objects

Role2 can see 100 objects, and I have drop/create permissions

I use Role1 for analytics and Role2 for database administration.

command used:  

• Use Role Role1; 
• Use Role Role2;
  

When I'm in Role1, I need to not see all 100 objects, as Role1 doesn't have access to all of that and seeing it is confusing.

Likewise, when I'm in Role2, I need to see all 100 objects, and seeing only 4 makes it frustrating to manage.

How can I get the DB Tree to update based on what role I'm using?  Vs. which Role was used to login?

I know I can use the DB Explorer and hit refresh - it refreshes.  But the tree is a bit more intuitive to use and is preferred by my end users.

(The role names, and the # of objects seen in each role were anonomized for security reasons)

SQL Workbench/J -> Build 124 (2018-08-20 22:43)

Product Name: Snowflake

Product Version: 3.7

Product Info: 3.7.5 (driver change version: 3.6.21)

Driver Name: Snowflake

Driver Class: net.snowflake.client.jdbc.SnowflakeDriver

Driver Version: driver change version: 3.6.21

URL: jdbc:snowflake://<EDITED>.snowflakecomputing.com/?user=<EDITED>

Isolation Level: NONE Username: <EDITED>Schema: Database (Catalog): Workbench DBID: snowflake Connection ID: WbWin-1

[Thomas Kellerer]

The DbTree always uses a different physical connection, so any SET ROLE you run, has not effect to that connection.

Can't you pass the desired role as part of the JDBC connection information?

Thomas

Lee_Str schrieb am 21.12.2018 um 15:54:
> In snowflake, you have access Roles, which you change to gain access to different objects.
>
> Role1 can see 4 objects
> Role2 can see 100 objects, and I have drop/create permissions
>
> I use Role1 for analytics and Role2 for database administration.
>
> command used:
>

> * Use Role Role1;
> * Use Role Role2;
>
>
> When I'm in Role1, I need to *_not _*see all 100 objects, as Role1 doesn't have access to all of that and seeing it is confusing.

[Akash Nath]

@Lee you would have to use different profiles for different roles and you can either switch between the roles or have the say Role2 open in a different window

FILE >> NEW WINDOW >> SELECT ROLE 2