Role Based Security

Introduction

 

To provide a more secure environment and allow non system administrators to take advantage of SentryOne's features, roles are placed on the SentryOne database during its installation or upgrade. Users are placed in these roles to allow them access to the features they need, while restricting access to features that may be above and beyond their responsibility.

Setting up Role Based Security

Role Based Security is configured through T-SQL statements or by using SSMS to set up database roles in SQL Server. There are two role-based security models to choose from:

  • Allow All:  Create a user on the SentryOne database, and add them to the allow_all role. This provides full access to the SentryOne database. From here, add the user to any of the custom deny_ roles to restrict that user's access to the different functions of SentryOne. Typically, there's a role to deny updating the specified information, and one to deny reading the information at all.
  • Least Privilege: Create a user on the SentryOne database, and add them to the allow_least_privilege role. This provides a minimal level of access, with the culmination of all the explicit deny_update roles available. From here, add the user to any of the custom allow_ roles to expand that user's access to the different functions of SentryOne.

Roles

Warning:  The roles starting with db_ are SQL Server default roles placed on every database. Using these roles in the SentryOne database may cause unpredictable behavior.

Unsupported: The allow_readonly role has been deprecated. You should switch to the least privilege model if you are currently using allow_readonly.

Allow All Model

Use the allow_all model to create users with full access to all of SentryOne's features, then remove access to any desired role(s) by applying the appropriate deny_update role. The following roles are available:

RoleDescription
allow_allProvides full access to SentryOne's features. Place all non-sa users in this role,  then add deny roles from this table to restrict access.
deny_actions_readDenies the ability to view all General, Failsafe, Audit, and Custom Condition actions.
deny_actions_updateDenies the ability to make changes to any actions, but allows the viewing of those settings, making them read-only.
deny_appsettings_updateDenies any changes made under the SentryOne Monitoring Service > Settings node.
deny_contact_updateDenies the ability to update information for individual users, but allows viewing the information, making it read-only.
deny_contactgroup_updateDenies the ability to update group information, making it read-only.
deny_customconditions_updateDenies the ability to enable, disable, create, or edit advisory conditions.
deny_eventchain_readDenies the ability to view Event Chain information.
deny_eventchain_updateDenies the ability to make changes to event chains.
deny_fragman_manual_analyze
Denies the ability to manually execute analyze fragmentation now through Indexes tab.
deny_fragman_manual_defrag
Denies the ability to manually execute defragment now through Indexes tab.
deny_quick_trace
Denies the ability to run a QuickTrace™.
deny_settings_connection_readDenies the ability to view information under the Settings tab at the instance level for the specified instance type.
deny_settings_connection_updateDenies the ability to make changes under the Settings tab at the instance level for the specified instance type.
deny_settings_object_readDenies the ability to view information under the Settings tab at the object level.
deny_settings_object_updateDenies the ability to make changes under the Settings tab at the object level.
deny_settings_source_readDenies the ability to view source information from the Settings tab.
deny_settings_source_updateDenies the ability to make changes to Source information from the Settings tab.
deny_site_updateDenies changes made to Site Configuration.
deny_watch_connectionDenies the ability to watch or stop watching an instance.
deny_watch_objectDenies the ability to watch or stop watching an individual object.

Allow Least Privilege Model

Use the allow_least_privilege model to create users with basic access to SentryOne's features, then add access to any desired role(s) with the appropriate allow_update role. The following roles are available:

RoleDescription
allow_least_privilegeProvides access to read and view SentryOne's features, and denies update permissions to any update action listed in this table. You can place any non-sa users in this role, and then add allow roles to allow access.

For example, users that are assigned the allow_least_privilege role can create custom event views in the SentryOne Client, but can not make any changes to the monitoring service settings without being assigned to the appropriate allow_update role from this table (allow_appsettings_update in this case).
allow_actions_updateAllows the ability to make changes to any actions.
allow_appsettings_updateAllows any changes made under the SentryOne Monitoring Service > Settings node.
allow_contact_updateAllows the ability to update information for individual users.
allow_contactgroup_updateAllows the ability to update group information.
allow_customconditions_updateAllows the ability to enable, disable, create, or edit advisory conditions.
allow_eventchain_updateAllows the ability to make changes to event chains.
allow_fragman_manual_analyze
Allows the ability to manually execute analyze fragmentation now through Indexes tab.
allow_fragman_manual_defrag
Allows the ability to manually execute defragment now through Indexes tab.
allow_quick_trace
Allows the ability to run a QuickTrace™.
allow_settings_connection_updateAllows the ability to make changes under the Settings tab at the instance level for the specified instance type.
allow_settings_object_updateAllows the ability to make changes under the Settings tab at the object level.
allow_settings_source_updateAllows the ability to make changes to Source information from the Settings tab.
allow_site_updateAllows changes made to site configuration.
allow_watch_connection
Allows the ability to watch or stop watching an instance.
allow_watch_object
Allows the ability to watch or stop watching an individual object.

Role Based Example

Scenario

You have a junior DBA that needs to use SentryOne's Calendar view to check for any failures or long running jobs overnight, but you don't want them to make changes to any of SentryOne's settings.

Solution

Add their login as a User on the SentryOne database. Place that user in the allow_all role. This ensures the user has access to all the information they need while being explicitly denied any information specified in the additional roles assigned to them. Finally, for this example, you may want to add this user to all deny_ roles except the ones ending in _read. This denies changes to any settings along with the ability to Watch or Stop Watching an instance or object.

It's important to remember that logins using SQL Server Authentication must be specified in the SentryOne client instance information. To specify SQL Server Authentication, select File > Connect to Installation. Uncheck the box marked Integrated Windows Authentication, and then enter the user's login and password. Restart the SentryOne client to apply the settings. These new settings remain in effect on this SentryOne client until explicitly changed.

Viewing Role Details

To view the full details behind any role:

  1. Open SSMS
  2. Navigate to your SQL Server instance Databases SentryOne Security Roles Database Roles
  3. Select a role and view properties
  4. Select Securables

SentryOne Role Based Security in SSMSExample of looking at role details in SQL Server Management Studio