2

I'm building a shared hosting SQL Server tool for linux, where users only receive access to their own database, with a SQL Login and SQL Database User that has db_datareader, db_datawriter and db_ddladmin rights to make sure they can not run any destructive actions on the database server (or choose settings we don't want them to).

We want to enable them to restore backups through a script. Obviously we'll run this restore with a sysadmin account to facilitate this for them (as a means of privilege escalation). Now on the Microsoft documentation site here the following notes are made (quoted verbatim)

We recommend that you do not attach or restore databases from unknown or untrusted sources. These databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure. Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server and also examine the code, such as stored procedures or other user-defined code, in the database.

Herein lies my problem. How can I make sure that I do not risk my database server security when allowing end-users (unknown an untrusted sources) to restore arbitrary backup files? Since this process would be automated, there's no time for 'user intervention', but instead perhaps I need to (re)set some safe defaults and/or settings. Does anyone has guidance on what would be my best approach?

  • If these databases are the users, then they are “trusted” as far as you know the developer. You could always clean out permissions after the restore and set in place yours ( but they have access to everything else possibly) You could always ensure users do not have access to CLR, xp_cmdshell and other serious privileges. I should add that your current privileges are extreme. – clifton_h Jun 25 '18 at 0:35
1

I think your best approach would be to supply each user with his or her personal database engine process. With Linux, consider using Docker or some other container framework which further enhances your security by sandboxing the SQL server process.

One of the problems that may arise from your approach, clifton_h already mentioned some other things to watch for in his comment: Any user that is member of the "public" Server Role will be able to see at least the names of all databases on the instance when connecting with SQL Server Management Studio - but if you set the securable "View any database" to "Deny", the user won't even be able to browse databases he is allowed to connect to using the Object Explorer (USE [databasename], however, remains possible). This will sure cause some headscratching among your users and desparate calls on your help line :)

At the very minimum do not allow using contained databases (see https://docs.microsoft.com/en-us/sql/relational-databases/databases/security-best-practices-with-contained-databases?view=sql-server-2017), the already mentioned "View any database" permission and the items that are part of the Surface Area Configuration Policy (https://docs.microsoft.com/en-us/sql/relational-databases/security/surface-area-configuration?view=sql-server-2017): Facet Properties - Surface Area Configuration

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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