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.
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).
Go to Shared Components.
Click on the Authorization Schemes link. You can find it in the Security section.
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.
Next, select From Scratch and click on Next.
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 theEXISTSSQLquery. 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
1if the username is the current user (:APP_USER) and the user has admin privileges.Also, enter the error message APEX should display if the query returns no rows. Click on the Create button.
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.
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.
The authorization scheme is now ready. Now, page access must be restricted by this authorization scheme.
Go to the page that requires authorization and click on the Edit icon (the pencil) in the Page section.
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.