Proxy User in Oracle 10g

First we’ll create two database Schema users(cpdata, mpdata) and One Big Application User (app_user called Proxy User). The app_user will be used by a web application to connect to the database. Usually the application user, in this case cpdata and mpdata, will be authenticated by the web application through app_user.

Create User cpdata and mpdata as a Sys or System user which contain schema objects.

create user cpdata identified by cpdata;
grant create session, create table to cpdata;             
alter user cpdata quota unlimited on users;              

create user mpdata identified by mpdata;
grant create session, create table to mpdata;             
alter user mpdata quota unlimited on users;

Create Table on cpdata and mpdata schema

create table cpdata.test1
(
        Test1  varchar2(10),
        Test2  varchar2(10)
);      

create table mpdata.test2
(
        Test1  varchar2(10),
        Test2  varchar2(10)
);

Create User app_user, for a Proxy User.

create user app_user identified by app_user;
grant create session to app_user;

Grant Proxy Authentication

alter user cpdata grant connect through app_user;
alter user mpdata grant connect through app_user;

Now Connect cpdata and mpdata user using Proxy Authentication and access there schema objects

SQL> connect app_user[cpdata]/app_user@<SID>
Connected.
SQL> select user from dual;

USER
------------------------------
CPDATA

SQL> desc test1;
Name                                      Null?    Type
----------------------------------------- -------- ------------------
TEST1                                              VARCHAR2(10)
TEST2                                              VARCHAR2(10)

SQL> connect app_user[mpdata]/app_user@<SID>
Connected.
SQL> select user from dual;

USER
------------------------------
MPDATA

SQL> desc test2;
Name                                      Null?    Type
----------------------------------------- -------- ------------------
TEST1                                              VARCHAR2(10)
TEST2                                              VARCHAR2(10)

SQL> select sys_context('USERENV','PROXY_USER') from dual;

SYS_CONTEXT('USERENV','PROXY_USER')
-------------------------------------------------------------
APP_USER

More :

http://www.oracle.com/technology/products/ias/toplink/doc/1013/main/_html/dblgcfg008.htm

http://www.oracle.com/technology/tech/java/oc4j/1013/how_to/how-to-ds-proxy/doc/how-to-ds-proxy.html

http://www.dba-oracle.com/t_proxy_connect_authentication.htm