| Using Oracle Proxy User feature [message #222304] |
Fri, 02 March 2007 13:29  |
rkrishna
Messages: 9
Registered: March 2007
Location: Silicon Valley, Californi...
|
Junior Member |
|
|
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 (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 -
- 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 - rkrishna@yahoo.com
|
|
|
|
| Re: Using Oracle Proxy User feature [message #222330 is a reply to message #222304] |
Fri, 02 March 2007 21:34  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
> Can we use the proxy user feature to achieve this?
Where on http://tahiti.oracle.com is this "proxy user feature" documented?
I can't think of a single application where every application user actually had their own Oracle username.
I would NEVER try to build an application where every application user required their own username.
>Basically, we want all security and authentication to be done on the database side with the visibility of who is logged on
Admirable goal which can be achieved via a different approach.
You mentioned Java.
Will this be a web based application?
If not, how will the clients interface to the Oracle database?
[Updated on: Fri, 02 March 2007 21:40] by Moderator Report message to a moderator |
|
|
|
| Re: Using Oracle Proxy User feature [message #222381 is a reply to message #222330] |
Sat, 03 March 2007 13:03 |
rkrishna
Messages: 9
Registered: March 2007
Location: Silicon Valley, Californi...
|
Junior Member |
|
|
yes, a web based application using a JBOSS apps server. built using JSF and Hibernate.
We don't want to maintain list of users, rather have the users use an Oracle login to access the application. That is the easiest way to ensure the DB enforces the security. I was just looking for more tips on the Oracle Proxy user feature.
Anyway, thanks for your response.
- Ramesh
|
|
|
|
|
|
|
|
| Re: Using Oracle Proxy User feature [message #222595 is a reply to message #222304] |
Mon, 05 March 2007 10:10 |
rkrishna
Messages: 9
Registered: March 2007
Location: Silicon Valley, Californi...
|
Junior Member |
|
|
Thanks Mahesh. That note from asktom was what made me research on the proxy users feature. What that note does not tell me is how to have one single applications owner and make all the application users connect through the applications owner user. Short of creating synonyms and grants, I can't think of a solution wherein my logged in DB users can access the tables (using the application , of course).
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
