This week I set up a a user in our SQL Server. To save time, I simply assigned all of the permission that user needed. Then I remembered how many times I have been burned by this shortcut. It is so much easier in the long run to create a role in SQL Server, assign permissions to the role and then make the user a member of that role. This discussion looks at why it is best to assign permissions to roles rather than to users and logins in SQL Server.
In one sense it is really so much easier to assign permissions to the user. You have a specific user in mind and you can determine what exactly that person needs. On the other hand, using roles adds work: creating a role; assigning permissions to that role; adding the user to the role. You add an additional steps to the process.
Assigning a user permissions directly involves the following script in SQL Server (I assume that the login has already been created and the user added to the database).
grant select on table1 to alex;
grant select on table2 to alex ;
However if you use a role, the syntax would be as follows.
create role salesmanager;
grant select on table1 to salesmanager;
grant select on table2 to salesmanager;
alter role salesmanager add member alex;
Clearly this is added work.
Problems with assigning to user
However, there are several problems with this approach.
Adding users
When you add another user who has the same needs as your first user, you will need to recreate all of the permissions for that second user. If you kept the permission script, then this is less of a problem. But it is so much easier if you have created a role. So if I add a user alexa, I will need to repeat all of the permission assignment
grant select on table1 to alexa;
grant select on table2 to alexa;
However, if I use the role approach, then all I need to do is to add alexa to the role.
alter role salesmanager add member alexa;
The code is much simpler the second time.
Removing user permissions
If a user no longer needs any access to the database at all, removing permission is easy: just drop the user from the database.
drop user alex;
-- or drop user if exists alex; -- works in more recent versions of SQL Server
The benefit of this is that when alex is dropped, all of alex‘s permissions are also dropped. That is a minor problem as well (unless you keep the permission script): you will lose the reference to the permissions that alex has.
However, let’s say that you have a small set of tables that everyone has access to. But alex has access to considerably more tables. How do you remove the additional permissions? For example alex is moved to a new position in the company and no longer needs the additional permissions?. How is that accomplished? You will need to create a series of revoke statements to remove the permissions that that user has.
revoke select on table1 to alex;
revoke select on table2 to alex;
You would need to be careful not to remove the “alex” permissions that that user gets from being part of “everyone.”
If you gave alex permissions by using role assignment, you would simply need to remove alex from the role.
alter salesmanager drop member alex;
Dropping the member still leaves the permission definitions in tact. The salesmanager role will continue to maintain the permissions that you defined up front.
Changing environments
I find that I make so many fewer critical mistakes when I can develop and test my code in a separate database or better server. If at all possible, I develop in one environment, test in a second environment and then deploy to production.
But here’s the thing: the permissions I need in development are much more elevated in development. In test environment, I would prefer that I have permission that is closer to that of an ordinary user, possibly with permission to view the definition of the objects. Finally, in production, the business (for good reason) may not want me to have any permissions at all, except possibly permission to view the definitions of objects.
I would end up with separate, very detailed scripts for permissions in each environment. And if I am working with others, which happens from time to time, each of those additional people will need the same permissions.
Using roles, I create my developer permissions, my ordinary user permission and so forth. II then simply add my user to the developer role in development, the ordinary user role in test and then possibly leave my user out of production
Adding permission
Let’s say that you realize that you need to give alex additional permission. you can do that with an additional grant statement.
grant select on table3 to alex
But it is very easy to forget that other users may need permission. However, if you assign permission to the salesmanager role, as soon as you add the permission to the role, then all of the members of salesmanager will automatically be assigned permission.
Understanding why permissions granted
When someone looks at the permissions you have given a particular user, is it clear why you did it that way? You just have a long list of grant statements. When you add another user, you have decipher whether that user needs as much permission or less.
However, if you use role names that make sense it becomes clear why you the particular permissions are granted. In my development environment, I in the developer role, which allows me to see all of the data and to make changes in my development environment. I also am an ordinary user and there are select permissions and execute permissions on certain objects.
In reviewing these permissions, my focus is not on the individuals (“does alex need these permissions?”) but rather on the roles (“does a salesmanager need these permissions?).
Problems with role permissions
There are some problems with using role permissions.
Auditing
I worked in one environment where they needed to know who had specific permissions. Their audit software recorded the specific permissions that each user had and therefore, permissions needed to be assigned to the user rather than to a role.
Narrow permissions
When you assign permission to a role, you are going to need to assign all of the permissions that all of the members need. However, what if your requirement is that unless a person needs access to an object, they should not be granted access to that object. This would make using user roles more challenging.
Summary
I need to remind myself to use roles for my permissions in SQL Server rather than assigning them to users. It will save administrative work.
What additional problems and opportunities do you see with assigning permissions to roles rather than users? Comment below.