I have been making changes to a couple of schemas of different environments (they belong to the same application). Sometimes I'll want to copy data from one environment to the other, and sometimes I'll want, for instance, to copy information from production to a development environment. When doing this kind of stuff, it always scares me to think of what would happen were I to copy or merge or whatever in the wrong direction, then destroying the production database.
I'm currently connecting to the schemas as a regular user with read/write permissions (I'm not the DBA here).
The ideal would be to have some way of being able to decrease in a fine grained way my own permissions (or having some system akin to having to type
sudo in Linux. It'd be cool that any kind of write action to DEV would make me have to write my password).
I imagine this is a recurrent situation. How do people generally solve this kind of issues? I could of course ask the DBA to create different users with different kinds of permissions, but that gets tiresome, as their permissions end-up not being fine-grained and there are like 5 or 6 different environments where I'd have to create new users to. Maybe he could grant me rights to create new users (being those users always limited to have in the best case having exactly the same rights as my original user?)