Is it possible to configure Oracle’s CONNECT THROUGH based on roles


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?

Best Answer

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.