Sounds like he may be able to do what he wants with Enterprise Security. User "TOM" could be set up in the LDAP directory, and mapped to "TOM_SECURE" in the database. The user in the database would be a "CREATE USER ... IDENTIFIED GLOBALLY ..." type of user and therefore would not have a password - the connection to the database would be authenticated via a secure (SSL) connection to the LDAP server (we use Oracle Internet Directory - OID). That has the added advantage that each user's password is stored only in one place regardless of the number of databases accessed. In the scenario presented in the original question, each user would have two passwords for every database, all potentially expiring and changing at different intervals based on those databases' password expiration policies. The disadvantage is that the SSL connection between each database and the LDAP server requires a certificate, but Oracle provides the utilties that you can use to create a self-signed certificate for your own certificate authority (CA) which can then issue the certificates for the databases (for free - you can also use those utilities to issue free certificates for web servers, which works well for internal applications where you have enough control over the clients which are connecting via https to import the CA certificate into their browser).

Or much easier, create a password protected non-default role.

CREATE ROLE TOM_SECURED IDENTIFIED BY password_secured;
GRANT ...table privs... TO TOM_SECURED;
GRANT TOM_SECURED TO TOM;

CONN TOM/password_of_tom;

in PL/SQL:
SET ROLE TOM_SECURED IDENTIFIED BY password_secured;

TOM would not be able to access the tables until he entered the password for TOM_SECURED.

Hi
I look for the same solution.In oracle forms we use one user with only connect privileges and in the PRE_FORM trigger change to user Y who has all the tables.
In C/S ,the session will be same for all the forms but, if you use that same logic in the plsql it is not the case even if it is possible.
Ex. user X has connect privilege and has a so called procedure my_proc with change user option(assumption only).
Now the security issue is , anyone can login using sqlplus and connect X/password and execute the procedure my_proc will get all the access to the user Y.
Anyway it is not going to serve the purpose. Please share if you have other thought other than this which will be a great help.

instead of using roles, If he has specific stored procedure he want's to run with a different user can't he just use db link?

I have used this mechanism for a different purpose, to run statistics on ETL source database's indexes, but I believe it can help him as well.

execute immediate 'begin dbms_stats.gather_index_stats@stg.source_dblink(OWNNAME => ''' || p_schema_name || ''',INDNAME => ''' || p_index_name || ''',ESTIMATE_PERCENT => 20); end;';

i ran dbms_stats.gather_index_stats through source_db link at stg schema that connects with user app to a different database with app's privileges.

so you can create a db link with tom_secured in the same database then run a stored procedure with that user. the only problem is that once the procedure / query ended the session will be gone.

What more is that the tom unsecured user must have privileges for the db link so maybe it's not really top security level but it can still protect you, but anyhow if you wanted to switch while running so it's unsecured base line...

Another thing you may want to try is to run external procedure from the database (old jobs or easy with scheduler) that reconnects to the database and do whatever. though still you'll need to transform parameters to it by scheduler arguments or database. Another plus is that you will be able to chose running synchronized / synchronized.

Enjoy
Jona