SqlDependency所需的最低数据库权限

• 下载源码 - 3.84 KB

引言

.NET 2.0 有一个很棒的新功能叫做 SqlDependency ，它提供了一种机制，当缓存失效时通知应用程序。 我们不打算在这篇文章中解释这项技术的使用，因为已经有很多很好的资源，比如这篇。 本文将向您展示 SQL Server 2005 用户为了让 SqlDependency 正常工作应该拥有的最小权限。 Sushil Cordia 在 MSDN 博客上描述了这些权限中的大部分，但我注意到这个博客并没有列出所有需要的权限。 因此，就有了这篇文章。

Using the Code

本文附带两段代码。 第一个是设置必要权限的 SQL 脚本；另一个是 C# 控制台应用程序，它使用 SqlDependency 来监视特定表中的更改。

我最初的想法是在 SQL Server 中创建两个角色，这两个角色拥有运行 SQLDependency所需的所有权限；一个角色拥有启动 SqlDependency 监听器的所有权限，另一个角色拥有订阅更改所需的权限。 但是，我注意到这是不可能的。 只有两个角色的问题在于，这些角色的成员将属于 dbo 模式。 当尝试运行 SqlDependency.Start时，这会引起问题，因为此方法尝试在用户的模式中创建一个队列，但它没有足够的权限这样做。 因此，本文中提到的解决方案创建了这两个包含所需权限的角色，但您还应该确保启动 SqlDependency 的用户拥有自己的模式，并且是该模式的所有者。

以下是 SQL 脚本，它执行以下操作

• 创建一个名为 SqlDependencyTest 的测试数据库
• 创建一个名为 startUser 的用户，该用户将拥有足够的权限来调用 SqlDependency.Start
• 创建一个名为 subscribeUser 的用户，该用户将拥有足够的权限来订阅更改通知
• 创建一个名为 sql_dependency_starter 的角色，该角色设置了一些权限，使该角色的所有成员都有足够的权限来运行 SqlDependency.Start
• 创建一个名为 sql_dependency_subscriber 的角色，该角色设置了一些权限，使该角色的所有成员都有足够的权限来订阅通知。

USE master

-- Cleaning up before we start
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'SqlDependencyTest')
DROP DATABASE [SqlDependencyTest]
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'startUser')
DROP LOGIN [startUser]
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'subscribeUser')
DROP LOGIN [subscribeUser]

-- Creating a database
CREATE DATABASE [SqlDependencyTest]
GO

-- Ensuring that Service Broker is enabled 
ALTER DATABASE [SqlDependencyTest] SET ENABLE_BROKER
GO 

-- Creating users
CREATE LOGIN [startUser] WITH PASSWORD=N'startUser', 
            DEFAULT_DATABASE=[SqlDependencyTest], 
            CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
CREATE LOGIN [subscribeUser] WITH PASSWORD=N'subscribeUser', 
            DEFAULT_DATABASE=[SqlDependencyTest], CHECK_EXPIRATION=OFF, 
            CHECK_POLICY=OFF
GO

-- Switching to our database
use [SqlDependencyTest]

-- Creating a table. All changes made to the contents of this table will be
-- monitored.
CREATE TABLE Users (ID int, Name nvarchar(50))
GO

/*
 * Creating the users in this database
 *
 * We're going to create two users. One called startUser. This is the user 
 * that is going to have sufficient rights to run SqlDependency.Start.
 * The other user is called subscribeUser, and this is the user that is 
 * going to actually register for changes on the Users-table created earlier.
 * Technically, you're not obligated to make two different users naturally, 
 * but I did here anyway to make sure that I know the minimal rights required
 * for both operations
 *
 * Pay attention to the fact that the startUser-user has a default schema set.
 * This is critical for SqlDependency.Start to work. Below is explained why.
 */
CREATE USER [startUser] FOR LOGIN [startUser] 
WITH DEFAULT_SCHEMA = [startUser]
GO
CREATE USER [subscribeUser] FOR LOGIN [subscribeUser]
GO

/*
 * Creating the schema
 *
 * It is vital that we create a schema specifically for startUser and that we
 * make this user the owner of this schema. We also need to make sure that 
 * the default schema of this user is set to this new schema (we have done 
 * this earlier)
 *
 * If we wouldn't do this, then SqlDependency.Start would attempt to create 
 * some queues and stored procedures in the user's default schema which is
 * dbo. This would fail since startUser does not have sufficient rights to 
 * control the dbo-schema. Since we want to know the minimum rights startUser
 * needs to run SqlDependency.Start, we don't want to give him dbo priviliges.
 * Creating a separate schema ensures that SqlDependency.Start can create the
 * necessary objects inside this startUser schema without compromising 
 * security.
 */
CREATE SCHEMA [startUser] AUTHORIZATION [startUser]
GO

/*
 * Creating two new roles. We're not going to set the necessary permissions 
 * on the user-accounts, but we're going to set them on these two new roles.
 * At the end of this script, we're simply going to make our two users 
 * members of these roles.
 */
EXEC sp_addrole 'sql_dependency_subscriber' 
EXEC sp_addrole 'sql_dependency_starter' 

-- Permissions needed for [sql_dependency_starter]
GRANT CREATE PROCEDURE to [sql_dependency_starter] 
GRANT CREATE QUEUE to [sql_dependency_starter]
GRANT CREATE SERVICE to [sql_dependency_starter]
GRANT REFERENCES on 
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
  to [sql_dependency_starter] 
GRANT VIEW DEFINITION TO [sql_dependency_starter] 

-- Permissions needed for [sql_dependency_subscriber] 
GRANT SELECT to [sql_dependency_subscriber] 
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber] 
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_subscriber] 
GRANT REFERENCES on 
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
  to [sql_dependency_subscriber] 

-- Making sure that my users are member of the correct role.
EXEC sp_addrolemember 'sql_dependency_starter', 'startUser'
EXEC sp_addrolemember 'sql_dependency_subscriber', 'subscribeUser'

我们现在需要的是一个测试应用程序，它使用这两个用户并确保 SqlDependency 正常工作

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

namespace SqlDependencyTest
{
  class Program
  {
    private static string mStarterConnectionString = 
@"Data Source=(local);Database=SqlDependencyTest;Persist Security Info=false;
  Integrated Security=false;User Id=startUser;Password=startUser";
    private static string mSubscriberConnectionString = 
@"Data Source=(local);Database=SqlDependencyTest;Persist Security Info=false;
Integrated Security=false;User Id=subscribeUser;Password=subscribeUser";

    static void Main(string[] args)
    {
      // Starting the listener infrastructure...
      SqlDependency.Start(mStarterConnectionString);

      // Registering for changes... 
      RegisterForChanges();

      // Waiting...
      Console.WriteLine("At this point, you should start the Sql Server ");
      Console.WriteLine("Management Studio and make ");
      Console.WriteLine("some changes to the Users table that you'll find");
      Console.WriteLine(" in the SqlDependencyTest ");
      Console.WriteLine("database. Every time a change happens in this ");
      Console.WriteLine("table, this program should be ");
      Console.WriteLine("notified.\n");
      Console.WriteLine("Press enter to quit this program.");
      Console.ReadLine();

      // Quitting...
      SqlDependency.Stop(mStarterConnectionString);
    }

    public static void RegisterForChanges()
    {
      // Connecting to the database using our subscriber connection string 
      // and waiting for changes...
      SqlConnection oConnection 
                          = new SqlConnection(mSubscriberConnectionString);
      oConnection.Open();
      try
      {
        SqlCommand oCommand = new SqlCommand(
          "SELECT ID, Name FROM dbo.Users",
          oConnection);
        SqlDependency oDependency = new SqlDependency(oCommand);
        oDependency.OnChange += new OnChangeEventHandler(OnNotificationChange);
        SqlDataReader objReader = oCommand.ExecuteReader();
        try
        {
          while (objReader.Read())
          {
            // Doing something here...
          }
        }
        finally
        {
          objReader.Close();
        }
      }
      finally
      {
        oConnection.Close();
      }
    }

    public static void OnNotificationChange(object caller, 
                                            SqlNotificationEventArgs e)
    {
      Console.WriteLine(e.Info.ToString() + ": " + e.Type.ToString());
      RegisterForChanges();
    }
  }
}

关注点

非常感谢我的 Microsoft 伙伴 Nicole Haugen 帮助我解决了一些遇到的问题。