Login Schema Role User Setup

best practicesloginsroleschemausers

Environment:

  • Windows Server 2012
  • SQL Server 2014 Standard
  • Public website (Site-A) on Server
  • Private website (Site-B) on Server
  • Windows App running on workstations

SQL Server 2014 Standard:
2 databases – DB-A & DB-B

Class Library:
Handles get/set of all data via stored procedures. Data is on DB-A & DB-B

Site A:
Public facing eCommerce ASP.NET website (http://www.rackattack.com) using a class library to interface with DB-A & DB-B. Contains additional calls (via Stored Procedure) to both DBs

Site B:
Private ASP.NET website using a class library to interface with DB-A & DB-B. Contains additional calls (via Stored Procedure) to both DBs

Windows App:
In house app that uses class library to interface with DB. Contains additional calls (via Stored Procedure) to both DBs.

Question: How do I want to set up the Server Logins, Database Schemas, Database Roles, Database Users to comply with best practices for our Environment?

My Thoughts:

  • Server Login for Site-A, Site-B, WinApp
  • DB Role for ClassLib – permission to call stored procs for Lib
  • DB Role for Site-A – permission to call stored procs for Site-A on DB-A & DB-B
  • DB Role for Site-B – permission to call stored procs for Site-B on DB-A & DB-B
  • DB Role for WinApp – permission to call stored procs for WinApp on DB-A & DB-B
  • DB User for Site-A (member of Role for ClassLib & Site-A)
  • DB User for Site-B (member of Role for ClassLib & Site-B)
  • DB User for WinApp (member of Role for ClassLib & WinApp)

Question:

  • Does this set up make sense?
  • Do I want separate roles for the ClassLib and the Site users?
  • Where do I add the premissions to execute the list of SPs in need, in
    the role or should I be using schema for this as well? If so, how?

Disclaimer: I am a developer and not a DB admin. I have a basic understanding of the concepts but please "Explain like I'm 5". Thank you!

Best Answer

OK, so I'm just going to go off general best practices here and give you some advice on what I would do and what I have seen implemented in the past. I am also going to assume that you have sufficient security protocols in place to protect your public-facing website.

Firstly, be willing to lose everything in the public database if you get hacked (obviously back it up regularly but don't be surprised if it gets taken down).

Secondly, the database that runs your website needs be isolated. Everything that you need to run it needs to be independent and generally should not be linked via a library directly to a private database. If there are components in the private database that the public website requires then you have two options:

  1. Duplicate these components so that they exist in both databases. (Preferred)
  2. Create a "shared" database that contains only these components.

Once you have your public database isolated you immediately reduce the amount risk that you need to mitigate. If you need information from the public database, or need to get information into your public database from your private database then you could set up replication or some other sort of schedule to perform this task.

If you cannot separate your databases then you will need to be absolutely militant about what permissions are granted to your public facing website/data library. Be prepared to document a lot!

If you create roles, then you need to ensure that each role has the minimum permission required to complete the task assigned to it. For instance, your class library that is shared between the two sites, give it the least permission possible that enables it to run.

Any user accounts created for the public website should have access to the public database and nothing more. If the public website has direct access to your private database then it should be considered public as well even if that was not your intention.

Again, your windows application, should not call directly into your public facing database. This is to reduce the surface area of attack and also to ensure that someone does not unintentionally do something that takes your public website down (like deleting all content).

So to summarize:

  1. Public website should only connect to public database and nothing that is deemed internal or private.
  2. Private website and applications should not connect to the public database.
  3. Shared components/tables should be present in both databases and replicated between them.
  4. Permissions should be the absolute minimum to complete the task they are performing, nothing more nothing less.
  5. Don't use code components in your public website that can access and amend private data.
  6. Be prepared to lose everything.
  7. Never, ever give the public website SA or equivalent. Everything it needs to do should be explicitly granted to it.

If you isolate your public database then assigning permissions to your internal applications become trivial and easy to do.

Related Question