Documentation forSQL Sentry

SQL Sentry Rights Based Security

Introduction

 

SQL Sentry supports restricting server visibility within the SQL Sentry client through the application of Rights Based Security. Assign users and groups a limited set of visible sites, target groups, or instances to restrict what the logged-in user sees. Certain objects and commands are disabled for restricted users. For more information, see the Objects Hidden from Restricted Users topic.

Configuring Rights Based Security in SQL Sentry

Configure Rights Based Security in the SQL Sentry client by completing the following steps:

  1. Associate a SQL Sentry User or Group with a Windows or SQL Server Authentication account.
  2. Assign a SQL Sentry User or Group rights that restrict server visibility within the SQL Sentry client.
Note:  Associate a Windows Active Directory security group with a SQL Sentry group. This allows you to easily manage SQL Sentry client security for multiple users. Specify the Active Directory group from the Group Properties tab in the Login field.

1. Associating a User with an Account

The first step in configuring Rights Based Security is to associate a SQL Sentry user with either a Windows account or a SQL Server Authentication account. This account should be the same account that the user uses when opening the SQL Sentry client. For more information, see the Connecting to an Installation topic. 

Associate a user with an account by completing the following steps: 

  1. Open the Navigator pane (View > Navigator), expand the Contacts node, and then the Users node.
    SQL Sentry Navigator Pane Users
  2. Double-click the user you wish to assign an account to, or select Open from the context menu to open the Edit User window. If no user exists, double-click on the Users node to create a new user.
    SQL Sentry Add a User tab
  3. On the properties tab, the Login field specifies which accounts are assigned rights within the SQL Sentry client. Do one of the following:           
    •  Enter their user name in the Login field as Domain\Username if the user connects to the SQL Sentry installation using Integrated Windows Authentication.
      SQL Sentry Integrated Windows Authentication
    • Enter their SQL Server Login name in the Login field if the user connects to the SQL Sentry Installation using SQL Server Authentication.
      SQL Server Authentication
  4. Save the User by selecting Save on the toolbar.
    Edit User tab Save settings
Note:  If the user or group is newly created, it may be necessary to Save and then reopen the User or Group before the Rights tab becomes available.
Note:  New users have complete access in the SQL Sentry client by default.

2. Assigning Rights to a User or Group

The Rights tab is located directly beside the Properties tab when editing a User or Group. Once a user has been associated with an account, restrict which servers are visible for that user, using the Rights tab. Alternatively, you may choose to assign rights to groups of users. Both users and groups can be assigned a limited set of visible instances to restrict what the logged-in user sees. Assign rights to a User or Group by completing the following steps:

  1. From either the User or Group editor, select the Rights tab at the top of the editor. Any restrictions previously assigned are listed, otherwise the list is initially empty. Edit User Rights tab
  2. At the bottom of the Rights tab, select Add to open the Search Results window that shows a list of available instances.
    Edit User Rights tab
  3. Select the instances that you want to configure rights for from Search Results. The instances are added to the Rights tab with checkboxes to Allow or Deny visibility.
    SQL Sentry User Rights tab
  4. Select the Allow or Deny checkboxes for each desired instance to Allow or Deny visibility to the user or group.
    Edit User Rights tab Allow or Deny visibility
  5. Save the changes by selecting Save from the toolbar.
    Edit User Rights tab Save settings

Success: The user can only see the instances in the Rights tab that are checked Allow.

Important:  If no instances exist in the Rights tab, the user or group can see all instances, unless they are a member of another group that has a restricted set of visible instances.

Additional Information

Membership of the sysadmin fixed server role is needed to do the following actions:

  • Edit the login associated with a user.
  • Assign rights to users and groups.

To ensure that a user has rights to log in to the SQL Sentry database, but doesn't have rights to modify their own permissions, add the user to the allow_all database role of the SQL Sentry database, and ensure the user isn't a member of the db_datawriter role. For more information about the available SQL Sentry database roles, see the Role Based Security topic.

Server Visibility: implicit vs. explicit denial

Be aware of the following when assigning rights:

Warning:  Once you define rights for a user or group, any site, target group, or instance that doesn't have rights explicitly defined, becomes implicitly denied to that user or group. This means a user or group is denied visibility for any site, target group, or instance that's not listed in their Rights tab with an Allow checkmark.

When you configure Rights Based Security for sites and target groups be aware of the following:

  • Any Parent node (site or target group) with a Deny permission explicitly configured overrides any of its Child nodes Allow permissions.
  • Important:  In SQL Sentry Portal, explicit permissions on a child node enable the parent(s) within the direct path to reach the child. No siblings are enabled by default. See the Portal Configuration article for more about SQL Sentry Portal security.
  • If a Parent node is being implicitly denied, Allow permissions configured for any of its Child nodes are honored because rights aren't otherwise explicitly defined for it.

Objects Hidden from Restricted Users

The following SQL Sentry client restrictions are applied to any user with Rights Based Security configured:

The following Navigator nodes are hidden/unavailable:

  • Contacts 
  • Monitoring Service Group
  • Monitoring Services
  • Object Groups
Note:  The Contacts node isn't hidden if the user is sysadmin for the SQL Sentry database.

The following commands are unavailable:

  • Tools > Manage Response Rulesets
  • All Targets context menu > Show System Status
  • All Targets context menu > Show Monitoring Service List 
 Note:  Client alerts and object notes are only visible for the instances that the user has rights to see.

Applying Users/Groups Rights

Note: 

  • Deny overrides any Allow that's configured through a group.
  • SQL Sentry checks groups for restricted instances first. After the group membership has been evaluated, user instance restrictions are evaluated.  

Scenario Example

  • There are 100 servers.
  • There are 20 users.
  • There needs to be two different groups Group A (10 Users) and Group B (10 Users) with visibility to different servers.
  • Group A needs access to servers 1-50
  • Group B needs access to servers 51-100
  • One of the users (AllButOne) needs access to all but one of the servers allowed in Group A and Group B.

Setup Example

  1. Add the 20 users in the client.
  2. Create a Group A with rights to servers 1-50 by adding them on the Rights tab and selecting Allow.
  3. Create a Group B with rights to servers 51-100 by adding them on the Rights tab and selecting Allow.
  4. Add the users to their respective groups on the Groups > Properties tab.
  5. Create user AllButOne and add to both groups.
  6. On the Rights tab for the AllButOne user, select Deny for the server the user shouldn't be able to access.