Error in Syntax - Oracle APEX - apex_authorization.is_authorized function

Question

I am developing in Apex, with custom authentication and using the apex builtin acl (access control list).

My custom authentication is working fine. Regards the authorization:

1 - Create a new user and add a role is working fine. 2 - Now I want to create a process in the login page to get the role of the user and set if it is authorized or not. 3 - I am using the code below but I get the error below.

Can you help me understand what is wrong with this syntax?

I am starting to feel desperate on this.

declare 

l_is_admin BOOLEAN :=false;

function "APEX_AUTHORIZATION.IS_AUTHORIZED"(
    p_authorization_name IN VARCHAR2 )
    RETURN BOOLEAN;

begin

l_is_admin := apex_acl.has_user_role (
    p_application_id => 100,
    p_user_name => :P9999_USERNAME,
    p_role_static_id => ('ADMINISTRATOR')
);

if l_is_admin then
apex_authorization.is_authorized (
    p_authorization_name => 'Administration Rights' )
return true;
end if;

end;

this the error: ORA-06550: line 20, column 1: PLS-00103: Encountered the symbol "RETURN" when expecting one of the following: := . ( % ;

If using the builtin roles and authorization schemes, without the code above, even though I have a user that is an administrator, when I try to open a page with the Administration Rights, it shows the error: User is not an Administrator.

This user is present in the tables: APEX_APPL_ACL_USERS, APEX_APPL_ACL_USER_ROLES

Why doesn't this work?

Answer 1

Bunch of syntax errors prevent that code from compiling, such as

• declare is superfluous alltogether; variable should be declared within function code
• missing semi-colons (as statement terminators)
• function must return something; you're returning true only, but - what if l_is_admin is false? Function won't return anything.
• although this is not an error, I'd suggest you to forget about double quotes when working with Oracle

I'm not saying that this code will work as you want it to, but it should - at least - compile (if calls of Apex built-ins is correct).

function APEX_AUTHORIZATION.IS_AUTHORIZED 
  (p_authorization_name IN VARCHAR2)
RETURN BOOLEAN
is
  l_is_admin BOOLEAN := false;
begin
  l_is_admin := apex_acl.has_user_role 
  ( p_application_id => 100,
    p_user_name => :P9999_USERNAME,
    p_role_static_id => ('ADMINISTRATOR')
   );

  if l_is_admin then
     apex_authorization.is_authorized 
       (p_authorization_name => 'Administration Rights');
     return true;
  else
     return false;
  end if;
end;

Answer 2

This is not how authorization works. Authorization should not be as part of the login page (see below) . Authorization schemes are defined as a component in the shared component section and are then evaluated by page/region/button...

A login page handles authentication only: The user gets access to the application. What the user can do in the application is then configured in each page and it's corresponding component. You can make navigation menu entries that are only visible to administrators by setting the correct authorization scheme on it. Same for pages/regions/buttons/page processes, etc, etc.

From the code you have posted it looks like you are using the APEX built-in ACL. In that case, the authorization schemes should already be created when the application was generated. Out of the box you will get "Administration Rights", "Contribution Rights" and "Reader Rights". So... no authorization scheme needs to be created.

The way to determine what a user can see is to apply an authorization scheme to a page/navigation menu entry/region etc. If the auth scheme yields true for a user then the user will see the component, else it will be hidden. If a user tries to access a page that has an auth scheme which is false for the user, he will get an error message.

Additional comments:

• As to why there authorization should not be in the login page: when authorization is done through an external mechanism like social signon or a corporate SSO Server. In those cases there is no login page in the app.
• APEX_AUTHORIZATION.IS_AUTHORIZED is an existing APEX API (doc). Don't create a function with that name yourself. This API can be used if the authorization needs to be evaluated as part of a pl/sql block (in a page process or in a package). This should be the exception - usually the declarative "Authorization Scheme" attribute of the APEX components do the trick.
• A practice that is sometimes used (although I don't recomment this) is to use an application item for authorization purposes. For example create an application item IS_ADMIN, create an application computation on it with process point after authentication of type "Expression" with source

APEX_AUTHORIZATION.IS_AUTHORIZED (
    p_authorization_name => 'Administration Rights' )

I'm not a fan of hiding authorization logic in application items - it tends to make code harder to read and maintain since business logic and authorization get mixed very easily this way.

Answer 3

SOLVED. The problem was in the Security Atributes on the Source for Role or Group Schemes. It was the wrong selection. The right selection is Access Control User Roles Asssignments.