Row Level Security Feature

Hi, we are interested in MemSQL’s row level security features and have read the following document:

But there are a few questions I like to get clarifications on:

  1. We have both a Java based multi-tenant application and a 3rd party BI tool (Microstrategy) and our application connects to the MemSQL database as the schema owner and not the user that has logged into the web page. How does the actual front end client user’s granted roles get evaluated if the database connection is being made using an entirely different user (i.e. the schema owner) who won’t have those roles?

  2. The document indicates that you have to create Views for each table that has the ACCESS_ROLES column. Does that mean that the application cannot query the tables but now have to be refactored to query the views instead? And if we have queries with lots of joins those will now be joins against views and not tables with indexes, etc? Would that be a performance hit for tables that are a mix of reference, rowstore, and columnstore?

Thanks for any help

1 Like

Hi Mike,

Thanks for contacting us.

  1. The current row-level security mechanism cannot help you if your applications always use the same schema user to connect to MemSQL. Our mechanism is to intersect the current db user’s security roles with the roles setup on the rows. You may need to handle row-level security in your applications or your own views through some filtering logic.

  2. The performance impact for queries on the row-level security views is very minimal. You can still take advantage of the indexes on the underneath tables. You are correct that you need to refactor your queries to look through these views.

Hope this helps,