Wednesday, August 27, 2008

Oracle 10g proxy users

If you don't have the password of an account, but you need to connect to test or extract DDL, Oracle 10g allows you to connect as a proxy (instead of resetting the password). This also allows you to have one schema, but allow many named users to connect, meaning no need for grants, roles, or synonyms.

This also means that one named user can be a proxy for many different application schemas depending on the connect string.

# allow oradba to connect to bi_staging as a proxy
alter user bi_staging grant connect through oradba;

# connect to the bi_staging account using the oradba password
sqlplus oradba[bi_staging]/oradba_password

# check the environment
show user
select user from dual;
select sys_context('USERENV','PROXY_USER') from dual;