0

At the moment our production and development databases give sysadmin access to all of devops, third party developers, etc. I'd like to propose new access levels based upon existing AD groups, but I'm wondering if anyone can give pointers on what levels of access should be defined?

At the moment I've come up with 3 roles:

  1. Admin - giving sysadmin access
  2. Developer - giving permission to create/drop tables, insert/update/delete.
  3. Viewer - read only access.

We also have different applications running on each database/server, and it was proposed that we don't separate these by access level. So, if one person has sysadmin on the 'Sales' database, they would also have the same level on 'Human Resources', etc.

It was also suggest that that our new AD groups would be server agnostic, to ease in transitioning new application access.

I'm wondering if anyone can give their opinions how you've defined your access levels, etc.

Thank you.

| improve this question | |
  • 3
    Clearly access control must be more granular than that; you don't want to allow developers to touch production with a ten-foot pole, let alone "giving permission to create/drop tables, insert/update/delete". In a more strictly regulated environment, such as financial or healthcare, you'd probably be fired just for phrasing your question like you did. – mustaccio Apr 4 '18 at 0:02
  • 1
    @mustaccio the hospitality industry is a little bit different it seems, and until I arrived there was cartle blance power amongst all users - something I am trying to change. – Asher Apr 4 '18 at 0:33
  • @AsherRankin I definitely know the scenario you're in. My best results have come from slowly and methodically restricting access and using any time someone drops a table "by mistake" to push things 10 feet forwards. That being said, I doubt you're going to get any answer here that will fit your current situation since you're sort of stuck with "worst practice" until you can get to a better state. – LowlyDBA Apr 4 '18 at 0:38
  • 1
    You also might find "How To Talk People Out of the SA Account" by Brent Ozar helpful :) – LowlyDBA Apr 4 '18 at 0:43
  • 1
    I'd suggest separate AD groups for each application. Production data has a habit of leaking into test environments. You'll want to limit who can see HR data. If a dev works on several applications she can go into several AD groups. This is once-off work. – Michael Green Apr 4 '18 at 12:18
1

Having generalized security across all servers / applications would be a bad approach. With your suggestion, once you let John Doe from Customer Service have read access the Customer database (which he needs in order to accomplish his job), you effectively give him read access to job applicants in an HR database. The HR database would contain Personally Identifiable Information (PII). Since John Doe is not apart of HR, he shouldn't have the permissions to access this data. Allowing him to read out of the HR database would cause compliance issues and could result in major fines when those auditors bust through the door!

John Doe would also be able to read salaries out of the Payroll database, and ohh boy, once that info goes public you're bound to have some tension in the office; employees will question why the new guy is being paid more than them, etc.

Now that I've beat that horse, here is an article I ran across recently discussing how to setup AD groups with specific levels of access to specific databases. This goes into a little bit more detail than you may need, such as creating scripts to create the AD groups, but should be a helpful start.

http://www.sqlservercentral.com/articles/Security/133778/

| improve this answer | |
  • I would certainly partake in that horse beating. Thank you for the article. – Asher Apr 4 '18 at 5:15

Not the answer you're looking for? Browse other questions tagged or ask your own question.