4

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?

Improve this question
7
  • 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
    Commented Apr 23, 2012 at 8:44
  • 1
    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
    Commented Apr 23, 2012 at 9:22
  • Then why not just grant them the CLIENT_ROLE ? you're letting them have these privileges anyway...
    – A.B.Cade
    Commented Apr 23, 2012 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
    Commented Apr 23, 2012 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.
    – Philᵀᴹ
    Commented Apr 23, 2012 at 12:18

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

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

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