Authorization and user roles in Oracle Apex?

Question

So Apex has "workspaces", which let you create users of three types - all of which are internal to the organization in nature. Also, there seems to be no way for a developer of an individual site on Apex to have "users" just for his site.

Am I missing something?

I need to be able to have external (business) users to be able to get access to just some features of the site, for example, accounting can only see pages A and B while executives can see A,B, and C.

I need to have ability to have several groups of people with difference degrees of access.

Can this only be done by creating workspaces/groups? Or can that be done internally on just my site?

Answer 1

I know this is quite old, but Google brought me here so I'll try to bring in my five cents. Tom's answer helped me a lot, but I wanted to make use of the APEX's Group Grants. For example I have user groups: Basic User, Advanced User and Admin User. Higher user level should bring access also to lower lever apps/pages. If I use APEX_UTIL.CURRENT_USER_IN_GROUP I need to check all groups that should have access. If I add another user group, I would have to add that group to Authorization Scheme on every app. And also all users need to be in all of the groups that they have access to.

My solution was to check also the Group Grants in APEX. First I added user to Admin User Group. After that I added Basic User and Advanced User Groups to Admin User Group. This isn't enough and APEX won't give access to Admin User for Apps that are for Basic Users and APEX_UTIL.CURRENT_USER_IN_GROUP doesn't know about those either. So I did some adjustments to the PL/SQL and my Authorization Scheme is as follows:

DECLARE
    isInGroup BOOLEAN;
    userID    NUMBER(30);
    authGroup VARCHAR2(40);
    
    BEGIN
        authGroup := 'Basic User';                  -- User group to look for
        isInGroup := false;
        userID    := APEX_UTIL.GET_CURRENT_USER_ID;
    
        FOR a IN (
            SELECT wf.group_name AS group FROM wwv_flow_group_users wf WHERE wf.user_id = userID
            UNION
            SELECT gg.group_name FROM wwv_flow_group_users gu 
            RIGHT JOIN apex_workspace_group_groups gg ON gg.grantee_name = gu.group_name
            WHERE user_id = userID) 
        LOOP
            IF a.group = authGroup THEN
                isInGroup := true;
            END IF;
        END LOOP;
    
    RETURN isInGroup;
    END;

First SELECT before UNION gets the name of all groups the current user is a member of, in my case only Admin Users. Second SELECT after UNION is a list of all groups that the groups in the first select give access to. In my case Basic User and Advanced User. This whole bunch is iterated in the LOOP and if one of the groups equals authGroup defined in the beginning, return true.

This way I can add new users to only their appropriate group and they get access also to lower lever apps.

Answer 2

Authorization is a process of determining whether an authenticated/identified person is permitted to access a resource or do an operation. It is based on set of privileges or roles assigned to the user. For Example, In Oracle database, Administrator have privilege to schedule jobs, while an user cannot. How is Authorization different from Authentication? Often Authentication and Authorization work together. In other words, Authorization follows Authentication. Authentication determines Who are you? Authorization determines What you are allowed to do?

Answer 3

Although APEX has a built-in user management concept called "Groups" I must confess I have never used it, and a quick perusal of the documentation doesn't make it clear to me how you use these to control access (but see Tom's answer here for that).

You will probably need to create user/role tables within your database and use these in conjunction with APEX Authorization Schemes to control access to pages. A single Authorization Scheme of type "PL/SQL Function returning Boolean" could be created with the function body:

return my_auth_pkg.is_authorized (p_user    => :app_user,
                                  p_app_id  => :app_id
                                  p_page_id => :app_page_id);

You would then implement the package to look up the user's privileges and decide whether to return TRUE or FALSE for the application and page id.

Alternatively you could just perform the SQL to check for access directly in the Authorization Scheme:

(NB "user_roles" and "role_pages" are names I made up, to represent your tables)

Answer 4

I just wish to expand on Tony's answer, which by itself is correct. I just want to show you another way, which i think would be easier on a total beginner and omits the creation of tables.

If your application uses Apex as authentication scheme, then your users are managed through the administration of the workspace itself. You can create, edit and delete users, but you can also define groups, and link users to groups. It is possible for you to create several "end user" type of users, and define a couple of groups, like "Executives".

When you have created your group, go to the user you wish to assign this group to, and add the group to the groups of that user

Once you have that set up, you still need the authorization schemes. The fact remains you need some pl/sql knowledge here, but it is possible to keep the coding to a minimum, thanks to some handy api-work. The current_user_in_group does what it says: it checks for the current user if he has said group assigned. With some expanding using some simple IF-structures, you can ramp it up a bit!

Not that i'd totally recommend this method, i find it a bit tedious myself, and you need someone to go into APEX to actually maintain users and their groups, but it could well be that this is acceptable in your environment. You could use it to start out with however. You can very easily switch out authentication schemes, and with altering your authorization schemes so they comply with the new auth scheme, you can easily and quickly adjust this afterward. It depends on your priorities and goals of course.