Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » APEX Page Based Authorization Scheme (APEX, 3.1.2.00.02 - Linux)
APEX Page Based Authorization Scheme [message #402314] Fri, 08 May 2009 13:12 Go to next message
greendba
Messages: 22
Registered: September 2008
Location: Toronto
Junior Member
Hi there,
I want to create page (or even tab) based authorization scheme in APEX.

Essentially, I've created a table with usernames and page numbers
If the page number column is populated with 0 or 1 then the user can read or write... if the page number column is null then the user has no access.

I've tried using a NOT Exists and PL/SQL authorization scheme. I'm using :APP_PAGE_ID to reference the current page number.

SELECT 1 FROM PERMISSIONS TABLE 
WHERE
USERNAME=v('USER') and :APP_PAGE_ID = 0


The problem here I think is the passing of :APP_PAGE_ID as a column reference.

Can anyone assist?

Thanks,
Eric
Re: APEX Page Based Authorization Scheme [message #402371 is a reply to message #402314] Sat, 09 May 2009 10:22 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
Two things, i am not clear
1. do you want to replicate the authorization scheme proceedure exists in oracle xe apex in some other platform?

2.do you want create another table as authorization and want to use as controling traffic in oracle Apex itself?

even if you freeze one among the above options, i do not have readymade answer, but my search will be shifted from macro to micro responses of the problem.
yours
dr.s.raghunathan
Re: APEX Page Based Authorization Scheme [message #402852 is a reply to message #402371] Tue, 12 May 2009 16:24 Go to previous messageGo to next message
greendba
Messages: 22
Registered: September 2008
Location: Toronto
Junior Member
Option 2 sounds like the best description of what I would like to do.

Create a table that the application references for access to certain pages.

Cheers,
Eric
Re: APEX Page Based Authorization Scheme [message #403012 is a reply to message #402314] Wed, 13 May 2009 08:10 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
So the idea here is that for each of the pages in your application you have one column in the table?
(NB! below you use the code SELECT 1 FROM PERMISSIONS TABLE. I guess you meant permissions_table?)

If you have one column per page and you want to find the name of it by using :APP_PAGE_ID, you will have to do this trough dynamic SQL. The way you've done it in your query you're always checking if the user is visiting page 0. :APP_PAGE_ID can't be used to reference a column name. It's a variable in the way you're using it. :APP_PAGE_ID = 0 would never be true in your query.
Re: APEX Page Based Authorization Scheme [message #403439 is a reply to message #403012] Fri, 15 May 2009 11:40 Go to previous messageGo to next message
greendba
Messages: 22
Registered: September 2008
Location: Toronto
Junior Member
When you say Dynamic SQL I'm guessing you mean PL/SQL
(I'm rather new to PL/SQL)

Are you suggesting I assign the bind variable to a declared variable?

I'll give this a shot and see how it works out...


Re: APEX Page Based Authorization Scheme [message #403461 is a reply to message #403439] Fri, 15 May 2009 13:48 Go to previous message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Well, yes you'll have to call a dynamic SQL query, using pl/sql. Let's say you have a table with this definition:
create table permissions_table(
  username varchar2(10),
  access_p1 number,
  access_p2 number,
  access_p3 number);


You can then create an autorization of the type "PL/SQL function returning boolean". So in the sourcecode of the authorization you can use
execute immediate 'select access_p' || :APP_PAGE_ID || ' from permissions_table where username = :1' using :APP_USER;

Look up execute immediate if you're unsure about how to use it. Test the value you get returned from the query, and base your authorization on that.

You shouldn't use just numbers as column names. How should oracle interpret 1? Is it a number or is it a column name?

Of course, the problem with your solution is that for every page you add the authorization to you have to make sure that you have a column for it in the table.
Previous Topic: need to refresh page on browser back button
Next Topic: Close the PopUp page with column link
Goto Forum:
  


Current Time: Fri Mar 29 05:59:31 CDT 2024