Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » USER vs APEX_PUBLIC_USER (apex 4 or below)
USER vs APEX_PUBLIC_USER [message #487245] Fri, 24 December 2010 05:21 Go to next message
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
Re: USER vs APEX_PUBLIC_USER [message #487281 is a reply to message #487245] Fri, 24 December 2010 09:53 Go to previous messageGo to next message
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
Re: USER vs APEX_PUBLIC_USER [message #487479 is a reply to message #487281] Tue, 28 December 2010 03:27 Go to previous messageGo to next message
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.
Re: USER vs APEX_PUBLIC_USER [message #487657 is a reply to message #487479] Wed, 29 December 2010 07:04 Go to previous messageGo to next message
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
Re: USER vs APEX_PUBLIC_USER [message #487936 is a reply to message #487657] Mon, 03 January 2011 10:52 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
nvl(v('APP_USER'),USER)

Related useful article:
http://jes.blogs.shellprompt.net/2007/05/21/be-careful-of-v/


Regards
Ved

[Updated on: Mon, 03 January 2011 10:59]

Report message to a moderator

Re: USER vs APEX_PUBLIC_USER [message #489263 is a reply to message #487936] Fri, 14 January 2011 02:27 Go to previous message
axxi
Messages: 3
Registered: December 2010
Junior Member
Thanks Ved!
Previous Topic: End date greater than start date, but values can be null - how to code this
Next Topic: oehr_sample_objects zip is corrupted
Goto Forum:
  


Current Time: Fri Aug 07 13:51:39 CDT 2020