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.

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.
   
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.
   
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.
     
   • Enter their SQL Server Login name in the Login field if the user connects to the SQL Sentry Installation using SQL Server Authentication.
     
4. Save the User by selecting Save on the toolbar.
   

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.
2. At the bottom of the Rights tab, select Add to open the Search Results window that shows a list of available instances.
   
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.
   
4. Select the Allow or Deny checkboxes for each desired instance to Allow or Deny visibility to the user or group.
   
5. Save the changes by selecting Save from the toolbar.
   

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

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:

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

The following commands are unavailable:

• Tools > Manage Response Rulesets
• All Targets context menu > Show System Status
• All Targets context menu > Show Monitoring Service List 

Applying Users/Groups Rights

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.