How to limit rows a user sees after logging to an APEX application

Posted on 2010-01-10
Medium Priority
Last Modified: 2013-12-07

I have a simple application built in Oracle's APEX.  There is one main table where all columns and rows are shown as a report, however I want to limit the rows each user sees.

One of the fields is the sales manager's e-mail address.  They also login using their e-mail address as their user ID.

I want to restrict the rows each user sees in this report, based on their user ID.  I want the process to take place before any data is displayed in any report, form or chart (so each user will only see the rows where they are indicated as the sales manager).  There is a parameter in APEX that can be used in a query to identify the user, but I'm not sure of the code syntax or where it would be placed.

In plain English, the query would be something like:

  Display the rows where the User ID of the logged in user equals the Managers E-mail Address of each row

Pretty simple, don't know where in the application to put this?

On the login page?
On each page of the application as a "before header" process?

Any help is appreciated.  THANKS!!
Question by:sjsimpson2000
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26281211
What we normally do is Capture the role of the user while login and store it in a global variable, and design the query of the form such that it takes role as an argument and filter data

Author Comment

ID: 26282324
Hi Shajukg, can you give me an example of what the sql query would look like?  

Author Comment

ID: 26282370
I think I got the sql (just tried this, and it's working):

select * from SALE_TABLE

Now I just need to determine where in the application to put it.... trying a few things to see if I can figure it out.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 11

Accepted Solution

it-rex earned 1000 total points
ID: 26283576
you can virtual private database for that
VPD which will mask columns for you ;and within you can use
SYS_CONTEXT(userenv, string')  which will derive all user variables for you then you can filetr them

this is a very good doc


Author Comment

ID: 26288035
Thanks, it-rex, however I don't want to mask columns, but instead restrict rows.  VPD would probably work for that too (but I have to admit that it's a bit over my head).  I'm comfortable with a SQL query, or a PL/SQL block, but that's about it.

And, the requirement has since been complicated.  Not only do I need to restrict rows based on the "MGR" value, but there are 2 other columns I need to test for as well "AVP" and "GVP".

So, I need to do:

Select all rows
From <table>
where MGR = :APP_USER
where AVP = :APP_USER
where GVP= :APP_USER

so each user sees only rows where they are identified as in one of these 3 columns.

Does make sense?

I've tried several PL/SQL statements (using IF's, CASE, WHERE, etc in a variety of combinations) with no luck.

LVL 15

Assisted Solution

by:Shaju Kumbalath
Shaju Kumbalath earned 1000 total points
ID: 26290343
select  * From <table>
where MGR = :APP_USER
where AVP = :APP_USER
where GVP= :APP_USER

which retreives  data retrieves  only those records appuser email or role in one of above mentioned 3 columns

Author Comment

ID: 26295662
Hi Shajukg,

When I try this, I get ORA-00936: missing expression.  I've tried it in several variations, each time same error.

Author Comment

ID: 26296135
OK, I think I got it.  This is working now.  I'll close this out after I've had a chance to test a little more.
 from   DATA_TBL
Where "MGR" = rtrim(:APP_USER,'@EMAIL.COM')
 or "AVP" = rtrim(:APP_USER,'@EMAIL.COM')
 or "GVP" = rtrim(:APP_USER,'@EMAIL.COM')

Open in new window


Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question