How to limit rows a user sees after logging to an APEX application
Hi,
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!!
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!!
Shaju Kumbalath
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
ASKER
Hi Shajukg, can you give me an example of what the sql query would look like?
ASKER
I think I got the sql (just tried this, and it's working):
select * from SALE_TABLE
where :APP_USER LIKE MGR;
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.
select * from SALE_TABLE
where :APP_USER LIKE MGR;
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
OR
where AVP = :APP_USER
OR
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.
Ideas?
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
OR
where AVP = :APP_USER
OR
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.
Ideas?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Shajukg,
When I try this, I get ORA-00936: missing expression. I've tried it in several variations, each time same error.
When I try this, I get ORA-00936: missing expression. I've tried it in several variations, each time same error.
ASKER
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.
select
"ID",
"MGR",
"ACCT",
"COMMENTS",
"GVP",
"AVP"
from DATA_TBL
Where "MGR" = rtrim(:APP_USER,'@EMAIL.COM')
or "AVP" = rtrim(:APP_USER,'@EMAIL.COM')
or "GVP" = rtrim(:APP_USER,'@EMAIL.COM')