Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 6 down vote accepted

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.

share|improve this answer
    
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? –  CodeSlave Jul 11 '12 at 18:09
    
Ahh... I got it... I had wally and dogbert reversed when I did it my real life DB. Thanks. –  CodeSlave Jul 11 '12 at 19:45
add comment

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.

share|improve this answer
2  
Not true. You can grant CONNECT THROUGH. –  Phil Jul 11 '12 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 '12 at 16:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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