Strategies for connecting Shiny applications to databases

Broadly speaking, there are two strategies for connecting a shiny application to a database using secure authentication. The first is running the application as a service account. The second is running the application as the authenticated user. Both methods require RStudio Connect and involve setting the shiny application to run as a different user.

You can control which user the application runs as using the “Who runs this content on the server” setting in the “Access” tab of the right hand settings panel for each individual application.

Additionally, it is possible to run all deployed content as a different user. More information for this setting can be found here: https://docs.rstudio.com/connect/admin/process-management/

Strategy 1: Run as the service account

This approach establishes connections to the database as the service account. Whatever permissions are granted to the service account will be granted to the Shiny application. By default, all apps run as the "rstudio-connect" user, which is a service account created as part of the RStudio Connect installation process. However, you can run your Shiny apps under any local account of your choosing - see here for more: https://docs.rstudio.com/connect/admin/process-management/#runas.

1. [admin] Set up your authentication scheme when installing and configuring RStudio Connect. 

2. [app developer] Restrict access to applications based on username and/or group. The username and group are defined by the authentication scheme (i.e. AD / LDAP). This is controlled via the “Sharing settings” of the “Access” tab in the right hand settings menu for each specific piece of deployed content.

3. [app developer] Set the application to run as the service account (the default service user is "rstudio-connect"). https://docs.rstudio.com/connect/user/content-settings/#content-access

 5. [app developer] Build your apps and test them to make sure they work with the service account database permissions.

Strategy 2: Run as the authenticated user

This approach establishes connections to the database as the authenticated user. Whatever permissions are granted to that user will be granted to the Shiny application. This approach requires any user that will access the shiny application to have a corresponding local account on the server. When the user requests the application and authenticates, the R process will start under the corresponding local account and can request -- if needed -- a Kerberos ticket. The application code can use the ticket for database connections. For example, you can use SQL Server's trusted connection.  

1. [admin] Set up your Connect server’s authentication with PAM sessions. If desired, configure Kerberos so that a Kerberos ticket is created for the user at login. https://docs.rstudio.com/connect/admin/process-management/#pam-sessions

 3. [app developer] Configure your applications to run as the authenticated user. Note: every user must have a local account on the server.

 4. [app developer] Build your application so that the session object passes through the app. The session object contains the user name and group of the authenticated user. http://shiny.rstudio.com/articles/client-data.html

 5. [app developer] Write code that you would typically use in R to connect to your data sources. Your driver should pick up the Kerberos ticket if it was created at authentication. 

Method 2 Alternative: Request Credentials

An alternative to method 2 is to run the application with a service account, but have the shiny application prompt the user for their database credentials. The credentials are used to construct the database connection string. See the documentation on Shiny's passwordInput control for more information. This approach is a blend of Method 1 and Method 2 that includes the convenience of method 1 (local user accounts are not required) with the security of method 2 (data access is restricted based on the user accessing the application). This method does not support Kerberos.