Oracle Apex - Administration and Users

Question

I am creating a Oracle Apex application.

My question:

The application has two sides; Administration and Users. The purpose of administration is to assign users to groups.

Users should only be able to access information - groups they have been assigned.

For example; Mr A. is assigned to group A. Mr B is assigned to group B. Mr C is assigned to group A, B and C.

Users should only be allowed access to areas they are assigned to and not administrative controls. Only viewing privileges.

Is this possible? I'm finding Oracle Apex difficult to get my head-around!

Answer 1

You have a number of requirements, each of which can be built in Apex, but using different features:

1. Administrators may assign users to groups.

2. Only Administrators may access administrative controls.

3. Users may view privileges.

4. Users may only see information for their group.

I would use Apex Authorisation Schemes to take care of #1, #2 and #3. For example, create an authorization scheme called "Administrator" which checks whether the current user is an administrator, then apply this scheme to any pages, regions, buttons, items, etc. that should only be accessible to administrators.

For #4, there are a few solutions I can think of:

1. Predicates - make sure each query in the application checks whether the data is viewable by the current user according to their group.

2. Views - encapsulate the security predicates in a view on each table, so that you don't have to repeat this code throughout the application.

3. Oracle VPD or Row Level Security (requires Enterprise licence) - this hides the security predicates behind the SQL level.

I've used all 3 options above for different projects; the 3rd one was for a fairly large application with quite complex authority-checking rules. RLS made this project much simpler and easier to build and verify; however RLS may be overkill in some cases.