1

I have an Oracle DB (10.2), and I have a consultant (Dogbert) that I want to do some work on it. In particular they need to alter the table structures of tables in a schema (we'll call it Wally) that is used by a lot of my users.

I don't want to give Dogbert the password to the account to the Wally schema. So I've created a temporary account for Dogbert.

How can I give Dogbert's account permission to alter the structure of Wally's schema? I know I can certainly give Dogbert insert/update/delete/select but that doesn't affect the structure.

Improve this question

2 Answers 2

Reset to default
6

You can use CONNECT THROUGH to allow Dogbert to connect as Wally:

alter user wally grant connect through dogbert;
grant create session to dogbert;

To connect in sqlplus:

sqlplus dogbert[wally]/scottadams@DATABASE

Some tools may not support these proxy logins.

Improve this answer
2
  • That looks like what I want to do, but I get the following error when I try that: ORA-28150: proxy not authorized to connect as client. I can connect without the [wally] as dogbert. Any ideas?
    – BIBD
    Jul 11, 2012 at 18:09
  • Ahh... I got it... I had wally and dogbert reversed when I did it my real life DB. Thanks.
    – BIBD
    Jul 11, 2012 at 19:45
2

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.

Improve this answer
2
  • 2
    Not true. You can grant CONNECT THROUGH.
    – Philᵀᴹ
    Jul 11, 2012 at 16:22
  • @Phil - Excellent point-- I read the question initially as trying to avoid giving Dogbert all of Wally's privileges but it was just talking about the password. Updated my answer.
    – Justin Cave
    Jul 11, 2012 at 16:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.