Oracle APEX Authorization and Custom Authorization

In this article we are going to discuss about Oracle APEX Authorization and Custom Authorization. Oracle Application Express (APEX) has two mechanisms for providing system security to be implemented on its application. The two mechanisms are authentication and authorization. Authentication mechanism already discussed on previous article which is implemented on login page. Authorization is used to create deeper security control. It might implemented on tab, page or region. The combination usage of authentication and authorization will create an application with confidence security.

On Oracle APEX we can create two kinds of authorization. We can create authorization with Access Control List (ACL) or with custom authorization.

Access Control List (ACL)

Access Control List is a page that we can create inside of application. Main functions of the Access Control List is controlling the user login behaviour. There are 4 kinds of Access Control List on Oracle APEX which are Full Access, Restricted Access, Public Read Only and Administrative Access Only. 

Here are kinds of Access Control List properties:

1. Full Access, access control list not used

All users can access the application.

2. Restricted Access. Only users defined in the access list list are allowed.

Only users defined in the ACL can view pages within the application.

3. Public Read Only. Edit and administrative privileges controlled by access control list.

Public can view the application page.

4. Administrative access only

Only users with administrator privileges can see the application.

Here are the steps for implementing Access Control List on your application:

1. Log in to workspace WS_PABD with ADMIN_DATABASE user (assumed that Workspace and user already created)

2. CREATE new app -> e.g. class with ID 113

3. CREATE PAGE -> select Access Control then RUN the page

4. Set the Application Administration End by pressing the Set Application Mode button on the top right of page.

5. In Access Control List, Add some Users based on Oracle Application User Schema, see the following illustration

End with Apply Changes

6. Back to Page Definition app 113 - Class. Click on Edit Application Properties

7. Click on Security tab follow by click on Authorization sub tab

-> change the drop down Authorization Scheme to access control - administrator

End by pressing the Apply Changes button

8. RUN the 113-class app

a) Login with ADMIN_DATABASE user, the login process will successfully done

b) Login with LATIHAN1 user, the login process will unsuccessfully done.

c) do the same with user EXERCISE2 and EXERCISE 3, see the results.

9. Go back to step 5, change Authorization Scheme for ADMIN_DATABASE user to edit or view. Repeat step 8, see the results

  

CUSTOM AUTHORIZATION

With custom authorization, login process will by against predefined table records. In this article we will use tlogin table that have some attributes (USERNAME, PASSWORD, STATUS).

Here are the steps for implementing custom authorization:

1. In the Security area click on Shared Component then click on Create button, choose from scratch 

2. Name it with authorization_admin

3. In the Authorization Scheme select PL/SQL Function Returning Boolean, then fill in the following code in PL/SQL Function Body

declare  result of varchar2 (8);begin  select status into result from tloginwhere upper (username) = upper (: app_user);   if nvl (result, 'x') = 'ADMIN' thenreturn true;  end if;   return false;end;

Fill in the error message

<h1> <blink> You're Not an Admin !!! </ blink> </ h1>

Finish the process by clicking on Create button.

Back to Page Definition, click the Edit Application Properties button (top right) then click the Security tab.

In the Authorization sub-tab select authorization_admin in the Authorization Scheme field and click on Apply Changes button.

Run the application, now the main page will able to be viewed after login process determined based on the username and password listed in the TLOGIN table and only the ADMIN status that able to login and view the page correctly.

Thank You - Bobsis