使用应用程序角色保护数据

• 下载示例 - 21.36 KB

1. 引言

本文介绍了 SQL Server 2008 应用程序角色的概念。除了概念之外，本文还讨论了创建和使用应用程序角色相关的以下几个方面。

• 创建应用程序角色，并关联数据库架构及授予应用程序角色权限
• 创建一个 Windows 窗体应用程序来说明以下内容
  • 启用和禁用应用程序角色
  • 使用 cookie（SQL Server 2005 起引入）来取消设置应用程序角色
  • 应用程序角色的生命周期

2. 背景

应用程序角色是数据库级别的安全主体。它可以拥有一个或多个数据库架构，并被授予特定权限。应用程序以应用程序角色的权限访问数据，而与连接到数据库的用户无关。应用程序角色需要被启用才能生效。这为数据和其他数据库对象提供了一层安全保护。任何希望通过应用程序作为网关访问数据的用户，都将受到应用程序角色（该角色在当前应用程序实例的生命周期内存在）的限制。

3. 示例应用程序 - 一个说明性示例

理解应用程序角色的最佳方法是使用一个实际的应用程序来访问数据库。因此，我将介绍一个实际的基于 Windows 窗体的应用程序，该应用程序控制对 `AdventureWorks` 数据库中 `HumanResources.Employee` 表的只读访问。请注意，`HumanResources` 是 `Employee` 表所在的架构。我使用了 `AdventureWorks` 数据库，但如果您愿意，也可以创建自己的数据库和表来使用。我提供的示例应用程序足够简单，可以修改以适应您的 SQL Server 实例、数据库和表。

3.1 创建应用程序角色

以管理员身份打开 SQL Server 企业管理器控制台，展开 `AdventureWorks` 示例数据库（如果您尚未在 SQL Server 实例上安装 `AdventureWorks` 示例数据库，可以从网上下载并安装）。在“安全性”下展开“角色”节点。右键单击“应用程序角色”节点，然后单击“新建应用程序角色...”菜单。下图显示了用于创建角色的“常规”选项卡下的对话框。

我将该角色命名为 `HRUPDATER`。将架构选择为 `HumanResources`，并将角色拥有的数据库架构设置为 `db_datareader` 和 `db_datawriter`。现在，选择“常规”选项卡下方的“安全对象”选项卡。这将显示一个对话框，用于选择您要保护的数据库对象，并向 `HRUPDATER` 角色授予对该对象的必要权限。通过单击“搜索...”按钮，选择 `HumanResources` 架构下的 `Employee` 表，如下图所示。然后在“授予”列下选中“选择”权限。

单击“确定”按钮关闭对话框，并验证 `HRUPDATER` 角色是否已在“应用程序角色”节点下创建。

从现有登录名在 `AdventureWorks` 数据库下创建一个用户。在此示例中，我创建了一个名为 `sqluser` 的登录名，并使用相同的名称创建了一个数据库用户。请注意，此用户对 `AdventureWorks` 数据库对象没有任何权限。将此登录名添加为主数据库下的用户，命名为 sqluser，并赋予其对两个系统存储过程 `sys.sp_setapprole` 和 `sys.sp_unsetapprole` 的执行权限。这样做是为了允许登录到数据库的同一用户启用和禁用应用程序角色。这仅用于说明目的。

3.2 创建 Windows 窗体应用程序

创建一个简单的 Windows 窗体应用程序。我使用 Visual Studio 2008、C# 和 .NET 3.5 创建了一个应用程序，如下图所示。简单的窗体包含一个 `DataGridView` 控件和三个按钮，如图所示。

窗体加载事件会打开到 `AdventureWorks` 数据库的 SQL 连接。连接字符串配置在应用程序的 `app.config` 文件中。连接打开后，它将被缓存在一个窗体级别的变量中。窗体启动后，执行以下操作以验证应用程序角色的功能。

单击“显示”按钮。此按钮的单击事件使用用户的当前连接，并尝试从 `Employee` 表中获取 `Employee` 记录。请记住，用户 `sqluser` 对 `Employee` 表没有任何读取权限。因此，将显示以下“访问被拒绝”对话框。

现在，单击错误对话框上的“确定”，然后单击“启用 AppRole”按钮。此按钮的单击事件使用所需参数执行 `sys.sp_setapprole` 过程，并将返回的 cookie 存储在窗体作用域变量中的字节数组中。此 cookie 用于禁用应用程序角色。启用应用程序角色后，再次单击“显示”按钮，这次将使用 SQL 查询结果填充网格视图。

现在，单击“禁用 AppRole”按钮。此按钮的单击事件将清除网格视图中的数据，然后使用 cookie 禁用应用程序角色。现在，如果您单击“显示”按钮，将禁止访问 `Employee` 表中的数据，并会生成一个错误。在此示例中，应用程序角色提供了比用户权限更多的访问权限。也可以测试相反的情况，即用户可以访问和显示数据，但应用程序角色会禁止访问。

4. 代码讨论

以下是本文讨论的代码片段。您还可以下载完整的解决方案，修改连接参数并在本地计算机上进行测试。

// Reading the connection string variable from app.config
private string ReadConfigurationString()
{
    string configName = "ADWorksConnectionString";
    string connStr = "";
    try
    {
        connStr = ConfigurationManager.ConnectionStrings[configName].ConnectionString;
    }
    catch (System.Configuration.ConfigurationException ex)
    {
        connStr = "";
    }
    catch(Exception ex)
    {
        connStr = "";
    }
    return connStr;
}

// Fetching the HumanResources.Employee data
private DataSet GetAdventureEmployees(out string msg)
{
    msg = "";

    string sqlCommandString = "SELECT EmployeeID, ManagerID, Title, 
				VacationHours FROM HumanResources.Employee";
    SqlCommand cmd = new SqlCommand(sqlCommandString);
    if ((this._sqlConn == null) || (this._sqlConn.State != ConnectionState.Open))
    {
        this.OpenSqlConnection();
    }
    cmd.Connection = this._sqlConn;
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmd;

    DataSet ds = new DataSet();
    try
    {
        da.Fill(ds);
    }
    catch (Exception ex)
    {
        ds = null;
        msg = "Could not fetch employee records." + Environment.NewLine + ex.Message;
    }

    return ds;
}

// Display button click event handler
private void btnFetchData_Click(object sender, EventArgs e)
{
    // Read the configuration string and fetch AdventureWorks data for
    // display.
    string errMsg = "";
    DataSet ds = GetAdventureEmployees(out errMsg);
    if (ds == null)
    {
        MessageBox.Show(errMsg);
    }
    else
    {
        this.dgvAdventureData.DataSource = ds.Tables[0].DefaultView;
    }
}

// Method to enable the HRUPDATER application role.
private bool ExecuteEnableAppRole(string procName, out string msg)
{
    msg = "";
    bool result = true;
    SqlCommand cmd = new SqlCommand(procName);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection = _sqlConn;
    SqlParameter paramAppRoleName = new SqlParameter();
    paramAppRoleName.Direction = ParameterDirection.Input;
    paramAppRoleName.ParameterName = "@rolename";
    paramAppRoleName.Value = "HRUPDATER";
    cmd.Parameters.Add(paramAppRoleName);

    SqlParameter paramAppRolePwd = new SqlParameter();
    paramAppRolePwd.Direction = ParameterDirection.Input;
    paramAppRolePwd.ParameterName = "@password";
    paramAppRolePwd.Value = "manu1962";
    cmd.Parameters.Add(paramAppRolePwd);

    SqlParameter paramCreateCookie = new SqlParameter();
    paramCreateCookie.Direction = ParameterDirection.Input;
    paramCreateCookie.ParameterName = "@fCreateCookie";
    paramCreateCookie.DbType = DbType.Boolean;
    paramCreateCookie.Value = 1;
    cmd.Parameters.Add(paramCreateCookie);

    SqlParameter paramEncrypt = new SqlParameter();
    paramEncrypt.Direction = ParameterDirection.Input;
    paramEncrypt.ParameterName = "@encrypt";
    paramEncrypt.Value = "none";
    cmd.Parameters.Add(paramEncrypt);

    SqlParameter paramEnableCookie = new SqlParameter();
    paramEnableCookie.ParameterName = "@cookie";
    paramEnableCookie.DbType = DbType.Binary;
    paramEnableCookie.Direction = ParameterDirection.Output;
    paramEnableCookie.Size = 1000;
    cmd.Parameters.Add(paramEnableCookie);

    try
    {
        cmd.ExecuteNonQuery();
        SqlParameter outVal = cmd.Parameters["@cookie"];
        // Store the enabled cookie so that approle  can be disabled with the cookie.
        _appRoleEnableCookie = (byte[]) outVal.Value;
    }
    catch (Exception ex)
    {
        result = false;
        msg = "Could not execute enable approle proc." + Environment.NewLine + ex.Message;
    }

    return result;
}

// Method to enable application role
private void btnEnableAppRole_Click(object sender, EventArgs e)
{
    // Enable the Application role on the database.
    string errMsg = "";
    this.dgvAdventureData.DataSource = null;
    bool result = ExecuteEnableAppRole("sys.sp_setapprole", out errMsg);
    if (!result)
    {
        MessageBox.Show(errMsg);
    }
}

// Enable AppRole button click event.
private bool ExecuteDisableAppRole(string procName, out string msg)
{
    msg = "";
    bool result = true;
    SqlCommand cmd = new SqlCommand(procName);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection = _sqlConn;
    SqlParameter paramEnableCookie = new SqlParameter();
    paramEnableCookie.Direction = ParameterDirection.Input;
    paramEnableCookie.ParameterName = "@cookie";
    paramEnableCookie.Value = this._appRoleEnableCookie;
    cmd.Parameters.Add(paramEnableCookie);

    try
    {
        cmd.ExecuteNonQuery();
        _appRoleEnableCookie = null;
    }
    catch (Exception ex)
    {
        result = false;
        msg = "Could not execute disable approle proc." + Environment.NewLine + ex.Message;
    }

    return result;
}

// Disable AppRole button click event.
private void btnDisableAppRole_Click(object sender, EventArgs e)
{
    // Enable the Application role on the database.
    string errMsg = "";
    this.dgvAdventureData.DataSource = null;
    bool result = ExecuteDisableAppRole("sys.sp_unsetapprole", out errMsg);
    if (!result)
    {
        MessageBox.Show(errMsg);
    }
}

// Method to open sql connection
private void OpenSqlConnection()
{
    string connStr = ReadConfigurationString();
    if (string.IsNullOrEmpty(connStr))
    {
        return;
    }
    this._sqlConn = new SqlConnection(connStr);
    try
    {
        _sqlConn.Open();
    }
    catch (Exception ex)
    {
        this._sqlConn = null;
        MessageBox.Show("Could open connection." + Environment.NewLine + ex.Message);
    }
}

// Method to close sql connection.
private void CloseSqlConnection()
{
    if ((this._sqlConn != null) && (this._sqlConn.State == ConnectionState.Open))
    {
        this._sqlConn.Close();
        this._sqlConn = null;
    }
}

5. 值得关注的要点

• 应用程序角色提供应用程序级别的安全保护，用于控制对数据库对象的访问。
• 应用程序角色一旦设置，将持续到当前数据库连接的整个生命周期。
• 提供了系统存储过程 `sys.sp_setapprole` 和 `sys.sp_unsetapprole` 来启用和禁用应用程序角色。
• `sys.sp_setapprole` 返回一个 cookie，可用于取消设置应用程序角色。

6. 历史记录

• 2010 年 3 月 6 日：初始版本