Filtering database retrieval based on user

user_1896007 0
Viewed 115

I've made a Shiny dashboard that uses authentication via shinymanager to permit user access to the dashboard. The dashboard pulls values from a database based on user; certain users will show results in the dashboard based on all data, whereas other users will only see data related to themselves (their login credentials will be cross-referenced with a table in which they are designated as admins or regular users). So while I know this is a broad question, but: what is the easiest way to facilitate this?

shiny filtering r authentication sql
1 Answers

Almost impossible to answer without a bit of code, but a general pattern could be as follows (assuming that you use for instance the DBI interface for your database retrieval):

server <- function(input, output, session) {

   is_admin_user <- reactive({
      assigned_role <- user_table %>% 
         filter(user == input$user)
         pull(role)
      assigned_role == "admin"
   })

   get_data <- reactive({
      sql_tbl <- tbl(con, my_table_name)
      if (!is_admin_user) {
         sql_tbl <- sql_tbl %>%
            filter(!user_grp %in% "for admin eyes only"))
      }
      sql_tbl %>%
         collect()
   })

   # ...
}

Whole idea is that in your data pulling you conditionally filter the data according to the user rights (in my toy example only according to admin / non admin - but of course you can do whatever advanced stuff you'd like). With this idea the suers sees only the data which are intended for his/her eyes.

As said without any valid code it is is difficult t help any more than that.

user_41257510
Related