Support

Strategies for connecting Shiny applications to databases

Follow
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 Shiny Server Pro, and both involve the run_as setting in the Shiny Server configuration file. The run_as command dictates which user runs which app; for more detailed information, see: http://docs.rstudio.com/shiny-server/#run_as.
 
*Note: RStudio Connect uses an analogous setting -- RunAS. More information for this setting can be found here: http://docs.rstudio.com/connect/admin/process-management.html#appendix-process-management-runas
 
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 "shiny" user, which is a service account created as part of the Shiny Server Pro installation process. However, you can run your Shiny apps under any local account of your choosing.
 
1. [admin] Set up your authentication scheme. 
 
2. [admin] Restrict access to applications based on username and/or group. The username and group are defined by the authentication scheme (i.e. AD / LDAP). Specifically, set the required_group or required_user directive in the configuration file. Restricting by group is recommended; additional information on this setting can be found here: http://docs.rstudio.com/shiny-server/#group-authentication
 
3. [admin] Set the applications to run as the service account (the default service account is "shiny"). http://docs.rstudio.com/shiny-server/#run_as
 
4. [admin] Configure the service account to have all the database permissions that the app will need.
 
5. [developer] Build your apps and test them to make sure they work with the service account database permissions.

6. [developer] (Optional). The user's username and group are available at runtime in the session object, discussed here: http://shiny.rstudio.com/articles/client-data.html The application code can use this object to limit queries to the database, e.g., SELECT * FROM table WHERE userid = session$user. A complete example can be found here: http://shiny.rstudio.com/gallery/authentication-and-database.html
 
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 authentication with PAM sessions. If desired, configure Kerberos. http://docs.rstudio.com/shiny-server/#pam-and-kerberos
 
2. [admin] Restrict access to applications using the required_user directive in the configuration file. http://docs.rstudio.com/shiny-server/#user-authentication
 
3. [admin] Configure your applications to run as the authenticated user. Note: every user must have a local account on the server. http://docs.rstudio.com/shiny-server/#auth_user
 
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://docs.rstudio.com/shiny-server/#authentication-overview
 
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.
 
Have more questions? Submit a request

Comments