Home » RDBMS Server » Security » Using Oracle Proxy User feature
Using Oracle Proxy User feature [message #222304] Fri, 02 March 2007 13:29 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #222382 is a reply to message #222304] Sat, 03 March 2007 13:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
What we have is a failure to communicate.
PLEASE answer the questions.
1) Where on http://tahiti.oracle.com is this "proxy user feature" documented?
You keep using this phrase. I have never heard of it & don't understand what this means.


If you are planning on depending upon the USER function, then every application user will require their own USERNAME.
2) Will every application user have their own unique USERNAME?

Re: Using Oracle Proxy User feature [message #222384 is a reply to message #222381] Sat, 03 March 2007 14:21 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator

>>- It is expected that these 20-25 users only access the data through the application and will not have SQLPLUS access -
How exactly can you restrict these folks from using sqlplus or anyother tool? There is NO PROVEN method for that(unless you can physically control all the machines users can access

We may using different terminology here.
To set things straight concept wise, please go through this thread.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:21575905259251
Re: Using Oracle Proxy User feature [message #222595 is a reply to message #222304] Mon, 05 March 2007 10:10 Go to previous messageGo to next message
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).

Re: Using Oracle Proxy User feature [message #222599 is a reply to message #222595] Mon, 05 March 2007 10:21 Go to previous message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
There are many facilities available in database like VPD (virtual private database)/FGAC.
http://download-east.oracle.com/docs/cd/B14117_01/network.101/b10773/apdvntro.htm
Public synonyms are hard on performance.
Depending on your actual need, you can design something. VPD is quite complex but gives the best results.
The above URL outlines a simple procedure which you may need to tweak to fit you.

Previous Topic: audit database access
Next Topic: Is there a role defined for the following actions?
Goto Forum:
  


Current Time: Sat Dec 07 05:08:25 CST 2024