As many other I got challenged to set up a database architecture. Not only database objects related, describing the different environments was also asked and there was an urgent need for a decent security setup to support rollouts in general.
First thing on my agenda was to move the development team away from Public as well as the usage of the one commonly known DBA user, once more from all environments. For that I create a role “Developer” and made each developer a member. The role would receive as many possible rights in Development environment and would lose more and more privileges and authority rights when moving closer to Production. I figured that would make my life as overseeing DBA easier and, as I gave every developer in the Development environment the DBADM authority, it did … just until the moment we came across the fact that one developer needed to alter a database object created by another person.
The more logical choice – since the change concerned a view – was to use the command “CREATE or REPLACE VIEW” as it is the most generic way the compose the command: “CREATE” the view when it does not yet exist and “REPLACE” the currently existing view should it already be there. The “REPLACE” would also keep the already defined GRANTed and REVOKEd privileges to and from other database users. It does sound logical, right? Who has to care about ownership when replacing database objects? For sure not a DBADM? … well … I got proven wrong on this point. The command “CREATE or REPLACE VIEW” works perfectly on views that you do own and it fails when you don’t. For a while it seemed we would have to drop the view and recreate it. Should we choose this path, we had to make a list of all other database objects using this view, assembling all privileges given concerning this view, and so on. This is not a message one would bring developers. Another option would be to TRANSFER OWNERSHIP, but as one cannot transfer the ownership of an element to himself a third person should be involved to make this solution to work.
Some poking around finally got me to another solution which was accepted by the team:
SET SESSION AUTHORIZATION
What it does in short? It gives you the authority to replace another one’s objects.
This how we had to set it up for us to work properly:
1. Define all the participants based on their user name or assemble them all in one group
2. Perform the necessary granting. Not everyone can just start replacing someone-else’s objects. The granting is done on a user or group basis.
GRANT SETSESSIONUSER ON
3. In each session, you have to set the session authority whenever you want to alter a database object that is not your own. You can repeat this statement for every object you need to alter.
SET SESSION AUTHORIZATION
As I created a role to group all the developers, it is kind of strange I cannot use the role to grant the SETSESSION to. If there is a good reason for it, I do not know it yet. I was still too baffled that I couldn’t replace objects that weren’t mine even though I’m granted DBADM.
Thanks to Kurt S. and Erwin L. to pointing me in the right direction!