question

bbunn avatar image
bbunn asked

Allow users permission to do anything they need but prevent deleting another users files.

Setting up a shared DB for multiple users. I want to allow users permission to do anything they need but prevent deleting another users files. However, they do need to be able to delete their own files. Any ideas on how to accomplish this?

permissionsuserspermissionshared
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

can you explain what it means to "prevent deleting another users files" and "be able to delete their own files" in the context of a shared DB?

0 Likes 0 ·

We have a Sandbox DB schema that multiple developers will be utilizing for testing and development. I want to give them full access so they can create objects, tables, views, etc. Since this is a shared space, others can read from and update another developers tables. However, I want to prevent user A from accidentally deleting user Bs objects. Therefore, I only want the creator of a table to be able to drop a table. Is that clearer?

0 Likes 0 ·

much clearer!

0 Likes 0 ·
KenJ avatar image
KenJ answered

Before continuing, I would highly recommend that you spend a few minutes reading The unnecessary evil of the shared development database or, the even older, Overthrowing the Tyranny of the Shared Database just to illustrate how old this problem is and that a version control system is a well-established best practice in database development.

With that out of the way, if the developers need to share a common sandbox schema within a single database, you won't be able to manage this.

If the shared database itself is the sandbox but you can have multiple schemas, it's a bit convoluted, but doable. You can create a single schema for each developer that they own. This will allow your developers to create/alter/drop their own objects. Make sure they aren't in the db_owner database role so they can't create/alter/drop objects from other users' schemas. Finally, place each developer in the db_datareader and db_datawriter roles so they can read/write data to tables/views in the schemas of other developers. You probably won't want this schema separation to continue through development, test and production, so it would be best if you didn't implement this at all at the development level.

Ideally, each user would have their own sandbox database and you would have the database definition scripts in source control then deploy all changes from there so developers can pull the latest whenever they need it and you can push whatever they check in to other environments. The important thing is that the source control version of the database is canonical and the only to get your changes into the test/production database is by placing your changes into the version control system and they only way to get the current version of the database is from the same system.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bbunn avatar image
bbunn answered

Much appreciated @KenJ and I completely agree with individual development environments (all I've ever used previously). Unfortunately, for the time being, I'm stuck with this setup for now.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I know it's not always a matter of just saying "we now do database source control." :). Best of luck finding a workable solution with the new environment!

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.