3

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.

  • Assuming that you have unique user IDs for students a combination of EXECUTE AS in DB management SPs and ORIGINAL_LOGIN() to identify students should do the trick. Maybe I am missing something? – Alex Dec 4 '19 at 14:04
  • @Alex, I've done some research based on your suggestion, and it looks to me like an EXECUTE AS clause would work so long as the management database is marked TRUSTWORTHY. Although I did also find this article strongly recommending against this approach for reasons I don't quite follow. So I'm torn. :-) – Harry Johnston Dec 4 '19 at 19:54
  • Let us continue this discussion in chat. – Solomon Rutzky Dec 4 '19 at 22:46
1

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:

| improve this answer | |
  • 1
    I'd just like to add that it took me only a few hours to modify my code to use module signing and get it up and running on the new server. The only significant complication was that the newly created databases wound up owned by the student and it took me a few tries to figure out how to change this - I had to create an extra login (StudentDatabaseOwner) to act as the owner of the student's databases and use an EXECUTE AS LOGIN statement in the signed stored procedure that creates them. Thanks again! – Harry Johnston Feb 16 at 21:31
  • Hi @HarryJohnston , glad this worked out for you!! Not sure I understand the need for those extra steps. Who should be the owner of the student DBs in the end? Using the EXECUTE AS LOGIN does complicate things a little. Might be cleaner to simply give the certificate-based login associated with the signed procedure the necessary permission to execute ALTER AUTHORIZATION ON DATABASE::[{student_db_name}] TO [{desired_owner_login}]; after the DB has been created. – Solomon Rutzky Feb 16 at 22:24
  • I considered that, but it seemed to me that it would leave a race condition - there would be a brief period during which the student owned the newly created database, and could add themselves to the dbowner role or make other changes we'd rather they weren't able to. Not that I'm actually expecting any of our students to be putting that sort of effort into attacking the server, mind you, but once you start looking for potential security issues it can be a hard habit to break. :-) – Harry Johnston Feb 17 at 0:05
  • @HarryJohnston Ok, sounds good. Just FYI: students wouldn't be able to add themselves to the db_owner DB role. As dbo, their user is dbo, which is meaningless to add to db_owner. Upon changing the db owner, the new owner's SID would replace the student's SID, and the student would have no access to the DB, then the student is added a user and they aren't a member of anything. If the process somehow broke before changing the owner, the student couldn't add themselves as a user in order to add to db_owner as the SID cannot be in sys.database_principals more than once. – Solomon Rutzky Feb 17 at 4:16

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.