Authorization and user roles in Oracle Apex?

antonpug picture antonpug · Oct 26, 2011 · Viewed 38.2k times · Source

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

Tony Andrews picture Tony Andrews · Oct 26, 2011

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. enter image description here

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

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