Managing permissions for multiple users to Create and Drop tables

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Managing permissions for multiple users to Create and Drop tables

Dave Hughes

We have one database with several schemas.  We have several groups of developers that have the need to be able to collaborate including creating and dropping tables.   

I noticed it became difficult to manage because when one developer creates a table, he is now the owner.  All the other developers need to be given permissions to select from (or drop) those tables he created.  I was looking for a way to where all members of a group can select from everybody’s tables, drop them if needed, etc.  Where they can all work collaboratively. 

I found this site that had a suggestion:
https://blog.hagander.net/setting-owner-at-create-table-237/
You basically create a “common_role” as the table owner and grant all the users access to tables owned by common_role.  However, for this to work automatically, you need to create an event trigger so that each time a developer creates a table, it alters the table owner after the table is created.

I was wonder how other DBA’s handle this permission issue for collaborative environments?  Any best practices or advise?