1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Custom authorization for pages ie menu

Discussion in 'Oracle Application Express (APEX)' started by biju, Mar 13, 2018.

  1. biju

    biju Active Member

    Messages:
    18
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    saudi
    Hi

    i am having a table with all the page id's and the assigned users for pages.
    when the user login the page/menu must show according to the role assigned.
    Authorization for each components are working . Please provide sample codes for pages/menu
     
  2. biju

    biju Active Member

    Messages:
    18
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    saudi
    for component level i have tested the below function and its working good.

    BEGIN
    RETURN check_privs:)APP_USER,:APP_PAGE_ID,:APP_COMPONENT_ID);
    END;

    same way i have created another function as below

    FUNCTION check_privs1 (p_user VARCHAR2, p_page_id VARCHAR2)
    RETURN BOOLEAN
    AS
    v_count NUMBER;
    BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM privs_table
    WHERE username = p_user
    AND page_id = p_page_id;

    IF v_count > 0 THEN
    RETURN TRUE;
    ELSE
    RETURN FALSE;
    END IF;
    END check_pageprivs;

    Authorization schema created with pl/sql boolean return with "Once per component"
    and tested both option with page security and Navigation --> ListDetails .

    when we run the application its showing error as below
    Error processing authorization.
    ORA-06503: PL/SQL: Function returned without value

    Technical Info (only visible for developers)
    • is_internal_error: true
    • apex_error_code: APEX.AUTHORIZATION.UNHANDLED_ERROR
    • ora_sqlcode: -6503
    • ora_sqlerrm: ORA-06503: PL/SQL: Function returned without value
    • component.type: APEX_APPLICATION_AUTHORIZATION
    • component.id: 10651043751259545
    • component.name: page_privsF
    • error_backtrace:
      ORA-06512: at "KOSO.CHECK_PRIVS1", line 32
      ORA-06512: at line 2
      ORA-06512: at line 4
      ORA-06512: at "SYS.DBMS_SQL", line 1575
      ORA-06512: at "APEX_050100.WWV_FLOW_DYNAMIC_EXEC", line 1880
      ORA-06512: at "APEX_050100.WWV_FLOW_DYNAMIC_EXEC", line 1895
      ORA-06512: at "APEX_050100.WWV_FLOW_DYNAMIC_EXEC", line 902
      ORA-06512: at "APEX_050100.WWV_FLOW_AUTHORIZATION", line 160
      ORA-06512: at "APEX_050100.WWV_FLOW_AUTHORIZATION", line 310
      ORA-06512: at "APEX_050100.WWV_FLOW_PLUGIN", line 2518
      ORA-06512: at "APEX_050100.WWV_FLOW_AUTHORIZATION", line 719

    • error_statement:
      declare function x return boolean is begin BEGIN
      RETURN check_privs1:)APP_USER, :APP_PAGE_ID);
      END;
      return null; end; begin wwv_flow.g_boolean := x; end;
    Please support to solve the issues
     
  3. biju

    biju Active Member

    Messages:
    18
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    saudi

    two users created in table --> admusermaster (steven and biju)
    roles are inserted in the privs_table
    Authorization schema is assigned in the List and buttons
    component level authorization working good.

    please support
     
    Last edited: Mar 19, 2018
  4. biju

    biju Active Member

    Messages:
    18
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    saudi
    Hi

    reminding to provide to provide a solution for how to write an authorization schema for pages as per the user login (pages need to hide for different user login)
     
  5. biju

    biju Active Member

    Messages:
    18
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    saudi
    if its hard coded it will work and what will happen if one page is can be accessed by 2 users out of 3 ie if 25 pages and 10 users exist and if each user having different combination of pages
     
  6. biju

    biju Active Member

    Messages:
    18
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    saudi
    What you have mentioned in above is what i did & getting error.the only missing is no primary key. The hard code which you did also will not work for more than one page needed for one user. Still i didnt get a solution on how to display more than one pages for one user
     
  7. biju

    biju Active Member

    Messages:
    18
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    saudi
    Thank you very much for your support in your busy time.
    Actually i didnt change any design in the back end. I have only tested by reassigning the authorization schema to different pages to know how the hard code SQL exist works for different navigation.
    Only inserted data & created additional pages.
    ( ie user1 needs access for page-1 & page-4
    User2 needs access for page-2 & page-3
    User3 needs access page-1 & page-3).
    Here for user3 which authorization schema need to assigned was not able to understand.Expecting your final support for this query.
    Not aware any other viewers have changed.

    Once again thankyou very much for your great support

    Biju
     
  8. biju

    biju Active Member

    Messages:
    18
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    saudi

    I tried to run sample application but its showing the error error.png
     
  9. biju

    biju Active Member

    Messages:
    18
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    saudi
    i have login to oracle.apex.com
    with my workspace and userid/password

    when i run the application the 88251 its showing the above posted error and the user loginscreen is not appearing to enter for the user "biju"
     
  10. biju

    biju Active Member

    Messages:
    18
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    saudi
    Restarted the laptop and now its working!!!:)

    Thank you very much for your valuable time spent for my queries.


    i have a query whether we have to write the code in the expression1 of each page Navigation ListEntry?

    I am verifying the codes now & will reply to you after testing all

    Once again thank you so much

    Biju