Sql Server 2008 schema separation and permissions

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:

• Common
• Schema_A
• Schema_B

and applications:

Admin app:

• Login Admin
• ownership of schema Common
• Domain model Common

Application A:

• Login A
• Ownership of Schema_A
• Read-only over ownership of schema Common
• Domain model Model_A
• Domain model Common

Application B:

• Login B
• Ownership of Schema_B
• Read-only over schema Common
• Doma开发者_StackOverflow中文版in model Model_B
• Domain model Common

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

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.

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;

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).

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.

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.