Minimum necessary permissions on SQLServer?

LabKey Support Forum
Minimum necessary permissions on SQLServer? Ben Bimber  2018-03-02 09:21
Status: Closed
 
For as far as I remember, the LabKey guidance for installing on SQLServer has bee that the labkey database account should be syadmin or equivalent. While this obviously grants the LK application permission to do whatever it needs, it really is far in excess of what it really ought to be. LabKey does install the CLR functions and create a database; however, it doesnt manger users/permissions or things like this. In an increasingly security conscious environment, having that additional sysadmin account, even if it's a service account, is less defensible and we're getting from DBA pushback. In a scenario in which a single Sqlserver instance hosts multiple LK servers (each potentially sub-admined by different people), that global SQL permission is also less appealing.

Have you considered whether you could issue recommendations/guidance on a more targeted permission set for SQLServer permission set that would give the LK app permission to do what it needs, without opening up everything?

Thanks in advance.
 
 
Jon (LabKey DevOps) responded:  2018-03-02 11:25
The DB user would definitely need to have the ability to do CRUD operations on tables and have the ability to create, drop, and alter tables.

They would at least in the very beginning (and any kind of upgrade work afterwards) need to have the ability to create and drop schemas, especially if custom modules are being added in.

And as you pointed out, it would need to install the CLR functions and create the DB in the very beginning.

Beyond this, I don't think there's anything else that would be needed here.
 
Ben Bimber responded:  2018-03-02 11:57
Hi Jon,

Thanks for the quick reply. I agree it's pretty clear that the LK user needs CRUD and some kind of higher permissions for upgrades and CLR install. My point is that for some time the LabKey recommendation to clients, at least unofficial (though pretty commonplace), has been to give that user sysadmin permission. While effective, this is sort of the lazy answer. As a client, I am asking whether a) you have internally generated anything more specific than your reply, or b) asking that you consider adding better and more specific guidelines to your SQLServer install documentation. SQLServer permissions are not trivial and some kind of reference to generate a best-practice user would be valuable. Many institutions are taking security much more seriously, and it would be useful if LK made it easier to narrow those permissions.

Thanks,
Ben
 
Jon (LabKey DevOps) responded:  2018-03-02 12:05
Hi Ben,

I think we can probably get something official created here.

Recently, we've moved our LabKey.org site to a new AWS environment and have switched to using Amazon's RDS for the database. RDS is not your typical DB since Amazon restricts the ability to create SysAdmins, so we've had to jump through some special hoops to get our DB over to it.

I'll talk to our Docs and DevOps teams to see if this information can be added to our docs.

Regards,

Jon
 
Ben Bimber responded:  2018-03-02 12:08
Thanks
 
Jon (LabKey DevOps) responded:  2018-03-02 15:46
FYI - Docs team has this up on their scrumboard to get done for 18.1 docs.