OraFAQ Forum: Application Express, ORDS & MOD_PLSQL » USER vs APEX_PUBLIC

Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » USER vs APEX_PUBLIC_USER (apex 4 or below)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

USER vs APEX_PUBLIC_USER [message #487245]

Fri, 24 December 2010 05:21

axxi
Messages: 3
Registered: December 2010

Junior Member

Hi All,

I have an existing application and want to give the users an extra APEX access possibility onto their existing Oracle-forms.
Now I have views who filter data depending on the actual user who is logged on.
So in my where-clause of a view : WHERE mytab_colname_username = USER
This works fine in forms, they show only the data for the actual user.
Now in APEX the view returns NO DATA , because the USER variable contains APEX_PUBLIC_USER as value.
I know I can solve this problem by changing all my views with a IN() or NVL() piece ex: WHERE mytab_colname_username = NVL(v('APP_USER'),USER)
But I was wondering is there in apex-authorization or so a way that the system variable USER contains the user that is logged on in APEX and not the APEX_PUBLIC_USER as value so I dont have to change anything on my existing application?

Thanks already for your time!

GR.
Axxi

Report message to a moderator

Re: USER vs APEX_PUBLIC_USER [message #487281 is a reply to message #487245]

Fri, 24 December 2010 09:53

BBMamun
Messages: 94
Registered: February 2010
Location: Dhaka, Bangladesh

Member

Hi, You can study APEX_UTIL package in Oracle Application Express APIs in Oracle apex Documentation in the unzipped folder of Apex. You should check CREATE_USER Procedure,CREATE_USER_GROUP Procedure, CURRENT_USER_IN_GROUP Function, GET_CURRENT_USER_ID Function,GET_USERNAME Function. You should create your application with Apex Authentication, and handle users with above functions and procedures, populate your mytab_username field with apex computaion or process with &APP_USER. substitution variable. Then in your reports apply the where clause as required. I have maintained user authorization/authenticaion, data access restriction and page items rendering depending on user ID with the above functions and procedures. You try this, it will work.

Regards

Hasan Al Mamun
programmer
Bangladesh Bank
Dhaka, Bangladesh

Report message to a moderator

Re: USER vs APEX_PUBLIC_USER [message #487479 is a reply to message #487281]

Tue, 28 December 2010 03:27

axxi
Messages: 3
Registered: December 2010

Junior Member

Hi,

First thanks for your reply!

Yes, I can do all this but this solves not my problem.

I Log on in Oracle/Froms and through Apex with the same username : UserName_1.
In my Apex-application I run following sql : SELECT * FROM view_name_1;
The source of the view is something like this : SELECT * FROM table_name_1 WHERE tab_col_name1 = USER;
And here is the problem the Oracle variable "USER", when I access the database through Oracle/forms it contains the value "UserName1" but when I access the database through Apex it contains the value "APEX_PUBLIC_USER".
This means that the view who is in the database will give a different result depending on the access method.

Now my question is : is there a way to solve this whithout changing the database side, so without changing all the views and pl/sql source where the variable "USER" is used!
Is there a swith or option that does NOT set APEX_PUBLIC_USER in the USER variable but keeps the name of the user that is logged on?

(Note: This is an existing application Oracle/Forms - database application (really big app.), who gets an extra access through Apex, this is not a new database development! So changing the database-side is a last thing to do because of possible bugs)

Gr,
Axxi.

Report message to a moderator

Re: USER vs APEX_PUBLIC_USER [message #487657 is a reply to message #487479]

Wed, 29 December 2010 07:04

BBMamun
Messages: 94
Registered: February 2010
Location: Dhaka, Bangladesh

Member

Hi, May be you have developed your apex application with Apex authentication scheme. In Oracle Apex any user session is connected to database with APEX_PUBLIC_USER database user. In Forms if you use the variable USER, the connected database user is populated. But that's not the case in Apex. I have not handled the senario as you described. But I think you can solve your problem if you make your authentication with Oracle database authentication in your oracle apex application.

Regards

Hasan Al Mamun

Report message to a moderator