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 don't have too much administrative experience with Oracle DBs, so forgive me if I make a fool out of myself.

In our current setup, each of our clients has a database user on our Oracle database. Most applications let them log in using this user and see only their data. However, when a support person needs to confirm an incident, he needs to impersonate the client user to get the exact same rights on the database. Currently this is being done by looking up the client's password - I don't need to explain why this is a bad idea.

Oracle has a nice "GRANT .. CONNECT THROUGH" syntax where you can give a user the ability to log in as someone else without knowing their password. The only problem is that, according to our DBAs, this permission has to be given explicitly on a per-user basis, so if we have 5 support users and 100 client users, this means we need to issue 500 commands to enable the support users to log in as any client user, which poses somewhat of a maintenance issue.

I find it hard to believe that Oracle does not support using roles for this, so you could say something akin to "let all SUPPORT_ROLE users log in as CLIENT_ROLE users". I've been looking around for a while but lacking the correct terminology I'm having a hard time figuring out whether this is really not possible.

TL;DR: Is it possible to configure Oracle's CONNECT THROUGH based on roles?

share|improve this question
1  
I would be surprised to see that it can be done. What you're trying to do is very dangerous IMHO, your letting your support people become your end users, this is not something you should do massively. More than that, this is not the common use of proxy users, usually you let the end user connect through the proxy user to achieve connection pooling etc. –  A.B.Cade Apr 23 '12 at 8:44
    
While I agree that it's a dangerous thing to do, I think it is less dangerous than the current alternative which requires our support people to be able to retrieve a client's password. –  efdee Apr 23 '12 at 9:22
    
Then why not just grant them the CLIENT_ROLE ? you're letting them have these privileges anyway... –  A.B.Cade Apr 23 '12 at 10:08
    
The security is apparently a lot more complicated than just one role. I invented those two roles just now, hoping to be able to use them when setting up the proxy mechanism. I assume every client user has settings that contradict settings of other client users, which is why we can't just assign all the client user settings to the support users. –  efdee Apr 23 '12 at 10:18
1  
It's not possible to configure CONNECT THROUGH based on roles. It's trivial to create a script that generates the 500 grants for you though. As @A.B.Cade says, it sounds like a horrible solution though. –  FreshPhilOfSO Apr 23 '12 at 12:18
show 1 more comment

migrated from stackoverflow.com Apr 23 '12 at 11:58

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 0 down vote accepted

A.B.Cade and Phil gave the answer in the comments:

CONNECT THROUGH cannot be granted using a role.

As Phil said, creating a script to do this is trivial. Be aware the database links do not work the same when used via proxy.

This situation does have risk, but given the requirements and limitations in Oracle, it is sometimes necessary. You might consider allowing the script to accept two usernames. The DBA can then run the script on demand for the particular client and support person combination that needs it. By revoking the privilege when the support session is complete there will be less the potential for abuse.

share|improve this answer
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.