CODEWITHSUNDEEP
sqlsql-serverentity-frameworkdatabase-administration
gwt
gwt

Reputation: 2413

Best way to restrict access to different schemas in a single database

In SQL Server, I want to create a database with multiple Schemas each of which relevant to a specific Domain of my application.

I would also like to create a restricted access to each schema meaning that when a user logs in to the database (either from ssms or from entityframework inside application) he/she can only access to objects in the database with the one specific schema he/she has access to and also can do all ddl/dml commands with that schema.

I have came up with the following solution:

1- Create a database login

CREATE LOGIN [AccountingDataBaseLogin] WITH PASSWORD='AccountingDataBaseLoginPassword'

2- Create a database user for the created login in step 1.

CREATE USER [AccountingDataBaseUser] FOR LOGIN [AccountingDataBaseLogin]

3- Create a Schema:

    BEGIN
        EXECUTE('CREATE SCHEMA Accounting  AUTHORIZATION AccountingDataBaseUser')
        ALTER USER AccountingDataBaseUser WITH DEFAULT_SCHEMA = Accounting
        ALTER AUTHORIZATION ON SCHEMA::Accounting TO AccountingDataBaseUser;
        GRANT CREATE TABLE TO AccountingDataBaseUser;
        GRANT CREATE VIEW TO AccountingDataBaseUser;
        GRANT CREATE PROCEDURE TO AccountingDataBaseUser;
        GRANT CREATE TYPE TO AccountingDataBaseUser;
        GRANT CREATE FUNCTION TO AccountingDataBaseUser;
        GRANT CREATE DEFAULT TO AccountingDataBaseUser;
    END

In my application I create a seperate .edmx file for each Schema and I will have to have a distinct connection string for each .edmx as follows:

<add name="AccountingDataBaseEntities" connectionString="metadata=res://*/ModelDesigners.Accounting.AccountingDataBaseModel.csdl|res://*/ModelDesigners.Accounting.AccountingDataBaseModel.ssdl|res://*/ModelDesigners.Accounting.AccountingDataBaseModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.;initial catalog=APADataBase;user id=AccountingDataBaseLogin;password=AccountingDataBaseLoginPassword;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

I would like to know if what I am doing is the best way to achieve what I want and if not what would be a better way?

Upvotes: 3

Views: 8708

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89141

That is a reasonable implementation of your objective. In particular, you didn't make the common mistake of having the schema owned by dbo and allowing other users to create objects in it.

A couple of possible improvements to your model are

1) Have a role own the schema instead of a user

2) Have one role for the schema owner, and another one without DDL privileges for the application

Upvotes: 1

PeterHe
PeterHe

Reputation: 2766

You can grant permissions to the schema directly:

 GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Accounting TO AccountingDataBaseUser;

If you have more than one users need to access the same schema, you can create a database role and GRANT the above permissions to the role. Then add the users to the role.

Upvotes: 3

Related Questions