Sql-server – How to add a flexible database role to a database

loginsroleSecuritysql-server-2012sql-server-2016

I know there are two types of roles, one predefined (fixed), another user-defined (flexible). Here is my use case: I have a large number of AD Groups, and I am trying to map them up to a role and define permissions on that role. For example, ADGroup1, 2, 3 and 4 only should have access to database 1 tables 1, 2 and 3. To avoid re-defining the permission every single time for every single AD Group, I am trying to assign permissions to a ROLE and define the proper permissions to that single role.

Here is the issue I get:
For Logins, when I get properties, I get this nice "User Mappings" page that lets me define which DBs a user can have access to. But I cannot do that with a role, I cannot pick and choose databases a role can have access to.

What should I use to achieve this? How do you combine logins? (AD groups)?

THank you very much in advance

Best Answer

You need to do the user/role mapping per database.

So you create an AD group and add that as a login on the server

Within the database you define a role and set permissions for the role. Then you need to add the AD Group login as a user to the database and set them as a member of the database role. Each role can have multiple members so you can add many groups as members of the role.

You can also map the users to a server role and then add the server role as a user in the database and make that member of a database role.

Robin Page has a simple explanation on simple talk and it's all explained in details in multiple details in many StackExchange answers

Related Solutions

SQL Server – Finding Active Directory Group Permissions Source

select * from sys.login_token

Somewhere in the output list will be token that grants you access. For instance you can try this (not guaranteed to work):

select * 
from sys.login_token lt
join sys.server_principals sp on sp.sid = lt.sid;

Schema – Best Practices for Login, Role, and User Setup

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:

Duplicate these components so that they exist in both databases. (Preferred)
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:

Public website should only connect to public database and nothing that is deemed internal or private.
Private website and applications should not connect to the public database.
Shared components/tables should be present in both databases and replicated between them.
Permissions should be the absolute minimum to complete the task they are performing, nothing more nothing less.
Don't use code components in your public website that can access and amend private data.
Be prepared to lose everything.
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.

Best Answer

Related Solutions

SQL Server – Finding Active Directory Group Permissions Source

Schema – Best Practices for Login, Role, and User Setup

Related Question