Sql-server – Allowing non-privileged users to cause privileged code to run

permissionsSecuritysql serversql-server-2019

Context:

Our SQL server is used for teaching, and we need to be able to automatically create databases for the students on demand and in real-time. We also need the students to be able to delete databases that they created. The students cannot be given administrator privilege, or any other level of access that would potentially allow them to break the server.

We had a hacky but working solution on our old server, based on an extended stored procedure: there was a "management database" whose job was to track and manipulate the student's databases, and an SQL Server Agent job that was constantly running. The extended stored procedure allowed the job to go to sleep, and to be woken up, at which point it created or deleted databases as indicated by entries in a table, and subject to various business rules, then went back to sleep again. (I can expand on this if necessary, but hopefully you get the idea.)

We have decommissioned the old server because it was running SQL Server 2008 R2, and will be building a new one for the upcoming trimester running SQL Server 2019. From the documentation I've found, extended stored procedures are deprecated but still supported, so in principle I could just reuse the existing solution. However, I would like to do something saner if possible.

Question:

What I think I need to ask is how I can best allow a non-privileged user to cause a stored procedure, which runs in a sufficiently privileged context to dynamically create and delete databases, to run, or failing that, to wake up from sleep.

However, I would be happy to receive suggestions of other possible solutions to my underlying problem of allowing a non-privileged user to create and delete databases on demand, subject to business rules. (For example, we require the databases to be prefixed with the student's username. We also need to configure the database security when it is created so that the student has access to it. And of course the student should only be able to delete their own databases, not other people's.)

If I remember correctly (and based on some quick research) triggers and the like won't work because they run in the context of the user that triggered them, e.g., see this post. But perhaps a signed stored procedure would work? I'm looking for the simplest, sanest options here, thinking forwards to whoever winds up looking after this server after me and looking for solutions that will be understandable and maintainable.

Solutions whose server-end can be implemented entirely in SQL are preferred, but this is not an absolute requirement.

Additional notes, in response to comments:

  • Students are authenticated via Windows logon, and access is granted based on membership in a Windows group.

  • On the old server, we did not give the students dbo access to their own databases, because we were not sure of the security implications. We did give them the db_ddladmin, db_datareader, and db_datawriter roles.

Best Answer

perhaps a signed stored procedure would work? I'm looking for the simplest, sanest options here, thinking forwards to whoever winds up looking after this server after me and looking for solutions that will be understandable and maintainable.

Yes. A signed stored procedure absolutely would work, and would work well. As individual requirements go, "simplest" is not always overlapping with "sanest"; all too often people choose the simplest approach, giving up far too much sanity (which is understandable given that the simplest approach is, by definition, the easiest to understand, and the "saner" approach isn't obviously saner (at least not at first)). This is where Module Signing comes in. While not being the simplest approach, it is by far the sanest, all things considered. Unfortunately, it is also not well understood, and so appears to be more confusing / difficult than it really is.

The general concept is:

  1. Create the stored procedure (in the "management" DB) that creates the student's database:
    1. Have a parameter for the project name (remember: max DB name can be 128 characters, and you need to reserve some number of those for the students logon name as a prefix of the DB name, so depending on how long those get, size this appropriately, maybe 20 - 30 characters max, hence NVARCHAR(20ish))
    2. Create the database with the desired naming convention (including project name). You can get their logon name from a variety of system variables and built-in functions, though ORIGINAL_LOGIN() might be the best option. You might need to do simple string manipulation as the name will be prefixed with the machine/domain name.
    3. USE the new DB
    4. Create a user for that student's account: 
      DECLARE @User NVARCHAR(258) = ORIGINAL_LOGIN();
SET @User = QUOTENAME(manipulated_@User);
EXEC (N'CREATE USER ' + @User);
    5. Add permissions / roles for student's account: 
      EXEC (N'ALTER ROLE [db_datareader] ADD MEMBER ' + @User);
...
  2. Create the stored procedure (in the "management" DB) that drops the student's database:
    1. Have a parameter for the project name (remember: max DB name can be 128 characters, and you need to reserve some number of those for the students logon name as a prefix of the DB name, so depending on how long those get, size this appropriately, maybe 20 - 30 characters max, hence NVARCHAR(20ish))
    2. Drop the database having the desired naming convention (including project name). 1. Grant EXECUTE on these modules to whatever user and/or roles need to perform these actions (could be "student" AD group, or [public] as noted in the chat room
  3. Create a certificate (in the "management" DB)
  4. Sign the stored procedures using that certificate (using ADD SIGNATURE)
  5. Copy the certificate to the [master] database (i.e. create a certificate in [master] using the public key of the certificate used to sign the stored procedures).
  6. Create a login from the certificate copied to [master]
  7. Grant whatever instance-level permissions are necessary to that certificate-based login (which can include adding it to instance-level roles).

For some examples, please see:

Related Question