Protecting a page using an authorization scheme

In some cases, you might want to restrict access to certain parts of your application to certain users. For example, not everyone should have access to the Form Page where you can alter the salaries. In such a case, you can protect your page using an authorization scheme.

Getting ready

Before starting with the recipe you should have a table created in the database with usernames and roles. Let's say you have the following table APP_USERS:

ID       NUMBER(5)
USERNAME VARCHAR2(50)
PASSWORD VARCHAR2(50)
ROLE     VARCHAR2(10)

The table contains two rows. One is a user with admin privileges (role is ADMIN). The other one is a user with the no extra privileges (role is DEFAULT).

How to do it...

1. Go to Shared Components.

2. Click on the Authorization Schemes link. You can find it in the Security section.

3. Next, you might see the authorization schemes that have already been created. If no authorization schemes have been created in the past, you see an empty page. Click on the Create button.

4. Next, select From Scratch and click on Next.

5. Next, enter a name for this authorization scheme and enter the conditions in the Authorization Scheme section.

   In our case, we name the scheme AUTH_ADMIN. The scheme type could be a PL/SQL function returning Boolean or an EXISTS SQL query. You have to create this function or query by yourself. The code could be included in the Expression 1 textarea but you can also make a call to a function stored in the database. In our case, we put the code in here and we choose for the EXISTS SQL query. You see the query as follows:

   select 1
   from   app_users 
   where  username = :APP_USER 
   and    role = 'ADMIN'
   [9672_01_11.txt]

   The query returns 1 if the username is the current user (:APP_USER) and the user has admin privileges.

6. Also, enter the error message APEX should display if the query returns no rows. Click on the Create button.

7. In the Evaluation Point section, select Once per page view to validate the authorization scheme. This will make APEX check the authorization for every call that is made to pages (or other components) using this scheme.

8. The other option is Once per session. This is much more efficient, because the check is only done once (at the start of the session). But when it's possible that the session state changes or there is anything else that is not consistent during the session, this option is not reliable.

9. The authorization scheme is now ready. Now, page access must be restricted by this authorization scheme.

10. Go to the page that requires authorization and click on the Edit icon (the pencil) in the Page section.

11. Go to the Security section and select the Authorization Scheme we just created (AUTH_ADMIN) in the Authorization Scheme list element.

That's it. The page now requires authorization to be accessed. Run the page to see how it works. Also see what happens if you do an update on the APP_USERS table:

update app_users
set    role     = 'DEFAULT'
where  username = ...;

Or

update app_users
set    role     = 'ADMIN'
where  username = ...;
[9672_01_12.txt]

Don't forget the commit.

How it works...

Every time you navigate to this page, APEX executes the query in the Authorization Scheme. If the query returns one row, everything is fine and the user is authorized to view this page. If the query returns no rows, the user is not authorized to view the page and the error message is displayed.

There's more...

You don't have to go to the Shared Components to create an Authorization Scheme. It can also be done when you are on a page. In the Security section on the right-hand side of the screen, click on the Add icon. The Create Authorization Scheme wizard will be started.