Using Oracle Proxy User feature

[rkrishna]

Using Oracle 10G - Enterprise edition - 10.2.0.1.0
I am trying to use the proxy user feature in order to have all our security done through Oracle. The requirement is this:

- Java based application to be used by many users (around 20-25)
- All of them use the application whose tables are owned by a single schema (APPS_OWNER)
- APPS_OWNER has around 100 tables and a number of packages
- Since we need the ability to figure out who created or updated each record in each table
Java application has code to populate who created and updated the records in each table - using the "USER" variable (example: Select USER FROM DUAL)
- It is expected that these 20-25 users only access the data through the application and will not have SQLPLUS access for now..
- Ideally we want them to be Database users so that we can have the proper audit and also, provide some limited SQLPLUS or other tool access
later on to run some basic reports

- Can we use the proxy user feature to achieve this? If so, what do we need to do? Reading through the manuals I can conclude the
following:

CREATE USER APPS_OWNER.....
Give all privileges to APPS_OWNER to create tables/indexes etc.
CREATE ROLE APP_USER_ROLE;
CONNECT as APPS_OWNER
create table....table1...table100
GRANT SELECT, INSERT, UPDATE, DELETE ON table1...table100 to APP_USER_ROLE;
CREATE PUBLIC SYNONYM table1..table100 for table1..table100 (create public synonyms)

Then
CREATE apps_user1... apps_user25
GRANT CREATE SESSION TO apps_user1..apps_user25
ALTER USER APPS_USER1 grant connect through APPS_OWNER WITH APP_USER_ROLE;
.... same for app_user2..app_user25.

Q1) Would our approach work?
Q2) Is the Role -> Grant -> public synonym approach correct? - Has the overhead of doing this for every table/procedure/sequence
we create.

Basically, we want all security and authentication to be done on the database side with the visibility of who is logged on and updating which records.

Appreciate any help in this regard.

Ramesh - [e-mail]