SQL Server 2008架构分离和权限 [英] Sql Server 2008 schema separation and permissions

问题描述

我目前正在开发一个基础架构，该架构将使我们能够为多个应用程序拥有一个数据库，这些应用程序都共享一组公共的实体(表).每个模式都通过ORM映射到一组域对象.

I'm currently working on an infrastructure that will allow us to have a single database for multiple applications where they all share a common set of entities (tables). Each of the schemas is mapped to a set of domain objects through an ORM.

这个想法是要有一个管理类型的应用程序来管理通用的一组实体.每个其他应用程序都有自己的架构，但它们只能读取公共集中的实体.由于这些应用程序中的每个应用程序都有其自己的数据库登录名和对指定模式的模式所有权，因此最初会导致通用模式向所有这些登录名授予只读权限.

The idea is to have one admin type application to administer common set of entities. Each of the other applications has it's own schema for it's own entities but in addition it can read only entities from the common set. Since each of those applications has it's own database login and schema ownership over it's designated schema initially this results in the common schema to grant read-only permissions to all those logins.

也许可以简单地说，我们有以下三种模式:

Perhaps to put it simple, we have the following three schemas:

• 普通
• Schema_A
• Schema_B

和应用程序:

管理应用:

• 登录管理员
• 通用模式的所有权
• 通用域模型

应用程序A:

• 登录A
• Schema_A的所有权
• 对模式Common的所有权具有只读权限
• 域模型Model_A
• 通用域模型

应用B:

• 登录B
• Schema_B的所有权
• 只读的通用模式
• 域模型Model_B
• 通用域模型

上述情况非常简单:在登录A和B的通用模式上添加SELECT权限.

The above scenario is fairly simple: add SELECT permission over schema Common to logins A and B.

但是说我现在想授予Application A权限，以便在架构Common中插入，删除，更新特定表.为了清楚起见，我们假设有一个名为文件"的表，任何应用程序都可以插入该表.

But say I now want to grant Application A permission to INSERT, DELETE, UPDATE a specific table in schema Common. For clarity sake let's say we have a table called Files that any application can insert into.

我设法完成此操作的唯一方法是向登录A授予所有这些架构权限.如果我仅向其授予Common模式中的表Files的那些权限，则它将在运行时被拒绝权限.现在，悬停执行此操作将向登录A授予模式A中所有表的所有权限-这确实不是很理想.

The only way I managed to get this done was to grant login A all those schema permissions. If I only gave it those permissions over the table Files in the Common schema it would be denied permission in run time. Hover doing this now grants login A all the permissions over all tables in the schema - which is not really desirable.

如何或在何处授予所需的权限，使其仅适用于特定表?

How or where to grant the permissions needed so it only applies to a specific table?

推荐答案

首先:您可以向单个表授予权限，请参见

First of all: you can grant permissions to individual tables, see the very first example in GRANT Object Permissions:

GRANT SELECT ON OBJECT::Person.Address TO RosaQdM;

因此，您可以简单地将对特定表Common.Files的INSERT/UPDATE/DELETE权限授予User A和User B(它们是用户，而不是登录名，因为您正在谈论

So you could simple grant INSERT/UPDATE/DELETE permission on the specific table Common.Files to both User A and User B (they're Users, not Logins, since you're talking about database principals).

在最长的时间内，推荐的解决方案是拥有一组控制访问的存储过程，并授予EXECUTE和这些存储过程的权限.请参见在SQL Server中使用存储过程管理权限.您可以让您的ORM使用这些存储过程，而不是原始表访问.这对于写操作很好用，但是对于读操作不能与允许将任意查询推送到数据库(例如LINQ)的ORM一起使用，因为存储过程输出结果集无法像笔直表一样进行操作.但是由于布局仍然允许R/O访问Common模式，因此可以将其用于所有SELECT，并且仅将存储过程用于写操作.

For the longest time the recommended solution was to have a set of stored procedures that control access, and grant EXECUTE and those stored procedures. See Managing Permissions with Stored Procedures in SQL Server. You can have your ORM use these stored procedures instead of raw table access.This works fine for the write operations, but for reads does not work well with an ORM that allows arbitrary queries to be pushed to the database (eg. LINQ) because a stored procedure output result set cannot be manipulated like a straight table. But since your layout allows R/O access to Common schema anyway, you can use that for all SELECTs and only use stored procedures for write operations.

因此，您可以使用这两种解决方案中的任何一种，或者授予对表的写权限，或者使用存储过程并授予对它们的EXECUTE权限.存储过程添加了一层验证，并强制应用程序在使用表时遵循特定的API(例如，它们不能执行DELETE FROM Common.Files并擦除整个表).另一方面，在ORM中更容易使用直接访问表进行写操作.

So you could use any of these two solutions, either grant write permissions on the table(s) or use stored procedures and grant EXECUTE permission on them. Stored procedure add a layer of validation and enforce the application to obey a certain API in using the table(s) (eg. they cannot do a DELETE FROM Common.Files and wipe the entire table). On the other hand direct access to the table for writes is easier to use in ORMs.

这篇关于SQL Server 2008架构分离和权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！