Restrict the Schema Visibility in Redshift JDBC

Solved!
raviagrawal
Level 3
Restrict the Schema Visibility in Redshift JDBC

Hi,

We are using the redshift as source for dataiku but while doing search/import user can see all the schema's and tables even if he do not have access on that. Is there a way that schema  visibility can be restricted to couple of schema's only.

Regards,

Ravi Agrawal

 

 

1 Solution
Clรฉment_Stenac

Hi,

There are two use cases here:

  • If your Redshift connection is configured with per-user-credentials (i.e. each user enters his own Redsfhit username/password combination), then each user who goes into the connection explorer will only see the tables that his Redshift user is allowed to see. The burden of making sure that grants on Redshift are correct falls on your Redshift DBA.

 

  • If your Redshift connection is configured with a single global credential, then DSS has no way to guess which schemas "should" be visible by each user. In essence, all connections from DSS to Redshift are performed as a single user with a single security context. Even if DSS tried to perform some filtering in the connections explorer, it would be very easy for the user to workaround by writing custom SQL.

 

So you have two options:

  • Either user per-user credentials. This is the most recommended method.
  • Or create multiple Redshift connections, with different users, and leverage the "Freely usable by" setting in DSS to give differentiated access to the connection. Beware that this second solution makes cross-connection flows more complicated.

 

View solution in original post

1 Reply
Clรฉment_Stenac

Hi,

There are two use cases here:

  • If your Redshift connection is configured with per-user-credentials (i.e. each user enters his own Redsfhit username/password combination), then each user who goes into the connection explorer will only see the tables that his Redshift user is allowed to see. The burden of making sure that grants on Redshift are correct falls on your Redshift DBA.

 

  • If your Redshift connection is configured with a single global credential, then DSS has no way to guess which schemas "should" be visible by each user. In essence, all connections from DSS to Redshift are performed as a single user with a single security context. Even if DSS tried to perform some filtering in the connections explorer, it would be very easy for the user to workaround by writing custom SQL.

 

So you have two options:

  • Either user per-user credentials. This is the most recommended method.
  • Or create multiple Redshift connections, with different users, and leverage the "Freely usable by" setting in DSS to give differentiated access to the connection. Beware that this second solution makes cross-connection flows more complicated.