Changing own user permissions with Oracle SQL not being the DBA

Question

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 typesudo 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?)

Thanks

Answer 1

You mentioned as one of your examples as "from production to development" .. so I'll start there, and with that, assume that your different "environments" are, in fact, different instances/databases as well.

Always "PULL" data ... rather than "PUSH". So in other words, login to where you the data will be INSERTED. That id should have INSERT access to the target tables. That same id should ONLY HAVE READ access to the source (ie production). You can set that up via a db link or such.

You then

INSERT into <local table>
  select * from <remote table>@dblink;

and you can't go wrong, since you don't have access.

This works for any two environments ... if you setup those db links using READ ONLY ids, and you do the INSERT locally with a user with local INSERT privs .. you're safe.