CODEWITHSUNDEEP
oracle-apex
Andrii Havrylyak
Andrii Havrylyak

Reputation: 675

Make a User Role in apex oracle

I created applications in Oracle Apex, I want 2 types of user to be me. The first is the Admin (I can watch and edit), the second is the User (only watch). But I can’t find instructions anywhere - how can I do this? I created 2 roles in Application Access Control (Admin and User). My authentication scheme in the Authentication Scheme looks:

function ischecked (
   p_username IN VARCHAR2,
   p_password IN VARCHAR2)
RETURN BOOLEAN IS
r SEC_USERS_LIST%ROWTYPE;
BEGIN
select * into r  from SEC_USERS_LIST where upper(LOGIN) = upper(p_username);
   RETURN p_password = r.password;
END;

Upvotes: 1

Views: 3663

Answers (2)

Koen Lostrie
Koen Lostrie

Reputation: 18565

What version of apex are you on ? If you are on a recent version (I believe from 18.1 onwards), then you can use the built-in "access control" feature. This will create 3 authorization schemes for you (Administrator, Contributor, Reader) and the necessary screens to assign users to roles. It only takes minutes to implement and the functionality is enough for most implementations.

  • For a new application: check "Access Control" in the application wizard (that screen with all the options and the "Create Application" button). This will create 3 authorization schemes for you (Administrator, Contributor, Reader).
  • For an existing application: Create new page, check "Feature" as page type, select "access control". I do suggest you do this in a copy of your app and not your "master" app so you can play around with it a bit and see what the different options do.

Upvotes: 0

Thomas Tschernich
Thomas Tschernich

Reputation: 1282

For this requirement, you usually use 2 different authorization schemes. Example:

  1. Add a role column to your users table

    alter table SEC_USERS_LIST add role (number default 10);

    For example, 10 = normal User, 20 = admin

  2. Use a global variable to save a userid.

    Extend your authentication function in a way that it saves the primary key of your user. You will need it later. Like this: 

    function ischecked (
   p_username IN VARCHAR2,
   p_password IN VARCHAR2)
RETURN BOOLEAN IS
r SEC_USERS_LIST%ROWTYPE;
BEGIN
select * into r  from SEC_USERS_LIST where upper(LOGIN) = upper(p_username);
   :G_USERID := r.user_id; 
   RETURN p_password = r.password;
END;

  3. Use two different authorization functions to determine whether someone is an admin or merely a normal user

    enter image description here

    The autorization function for a normal user isUser looks similar:

    select 1
from SEC_USERS_LIST
where user_id = :G_USERID
and role >= 10

  4. Use your authorization functions

    You can now use your authorization functions wherever you like. If you put isAdmin on a navigation menu link or a page region item, a normal user won't see it. If you put isAdmin as a page authorization and a normal user will try to access it, he will receive an access denied message. If you put isAdmin on a process or dynamic action, then it won't get executed for a normal user.

The other authorization function isUser can get used as a application authorization schema at the Security Attributes. In this way, only people with at least one of these roles would be able to access your application.

Upvotes: 1

Related Questions