Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
  • 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.
Industry Leaders: 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

564 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