The only way for Dogbert to log in as himself and to be able to alter the Wally schema would be if Dogbert was given the (generally radically overpowerful) ANY
privileges (CREATE ANY TABLE
, ALTER ANY TABLE
, etc.). That gives Dogbert the ability to alter any table in any schema, though, not just the Wally schema so granting those privileges is generally rife with security issues.
Generally, it would make more sense for Dogbert to work on a local copy of the tables in question, create scripts that issue whatever DDL and DML is needed, and then send those scripts to the DBA who applies them to the Wally schema (either by logging in to the Wally schema directly or by running as a DBA user that has the CREATE ANY TABLE
, ALTER ANY TABLE
, etc. privileges).
If your only concern is giving out the password, you can let Dogbert log in as Wally using Dogbert's password. This would let Dogbert run with all of Wally's privileges, however.
ALTER USER wally
GRANT CONNECT THROUGH dogbert;
Then, from SQL*Plus
CONNECT dogbert[wally]/<<dogbert's password>>
will log Dogbert in as Wally. Not every tool, though, is going to support proxy users, though.