RStudio

Securing Deployed Content

There are multiple ways to secure access to a database in deployed content.

The types of content that we can deploy are:

  • Shiny Apps
  • flexdashboards
  • RMarkdown documents

The most common methods are:

Service Account

It is typical for shiny applications and R Markdown reports to provide insight from data that is not directly accessible by the content audience. In these 1-to-many cases, it is common to define service accounts that access the database on behalf of the content audience. The previous examples assumed this type of model.

Sometimes, during local development, the data scientist might be expected to use their own credentials. It is possible through a DSN or the config package to specify that local connections use the data scientist’s credentials and deployed connections use a service account. Be sure the code works for results for both accounts!

Query by User (Shiny)

Even when a service account is used, it is still possible to restrict access to data using logic inside the application code. One option is to update the query based on the logged-in user. The username is available in Shiny applications through the session$user object. For example:

library(shiny)
library(DBI)
library(odbc)

con <- dbConnect(odbc::odbc(), "Datawarehouse")

# ... rest of shiny code 

server <- function(input, output, session) {
   data <- reactive({
       q <- "SELECT * FROM sales-data WHERE user == ?user"
       query <- sqlInterpolate(con, q, user = session$user)
       sqlGetQuery(con, query)
   })
   
   # ... some code that uses data()
}

Prompt for Credentials (Shiny)

In Shiny it is also possible to prompt the user for their database credentials. For example:

library(shiny)
library(DBI)
library(odbc)

# ... rest of shiny code 

server <- function(input, output, session) {
   data <- reactive({
      req(input$uid, input$pwd)
      con <- dbConnect(odbc::odbc(),
        Driver = "Postgres",
        Server = "mydb.company.com",
        Port = 5432,
        Database = "sales-data",
        UID = input$uid,
        PWD = input$pwd,
      )
      
      data <- dbGetQuery(con, "SELECT * FROM sales")
      
      dbDisconnect(con)
   
   })
   
   # ... some code that uses data()
}

Run As the Logged-in User (Kerberos)

In rare cases, it may be necessary for the data to be accessed by the application or report on behalf of the specific logged-in user without prompting the user for their credentials.

This scenario is rare because it implies that each end user of the report or application has an account and access controls in the database. In other words, this model assumes a 1-to-1 model instead of the 1-to-many distribution model facilitated by a service account.

In these scenarios, it is most common to use Kerberos. RStudio Connect will need to be setup to run the application as the logged-in user. The admin guide contains more details.

Deployment of this type of content is usually straightforward because the connection code does not include any credentials, and is the same in the local and deployed context.

For example:

library(DBI)
library(odbc)

con <- dbConnect(odbc::odbc(),
  Driver = "SQLServer",
  Database  = "Datawarehouse",
  trusted_connection = "True"
)

For more information on data access, see this article. In all cases, the credentials should not be stored as plain text in either the configuration file or the R code. See securing credentials for more details.