APEX

Hi

We are migrating an Oracle form application. The Form Application's authorisation is built purely dependent on Oracle database role. This is due to Oracle Form logs into database by using each individual database user. The business wants use the same approach (database role) as authorisation scheme in new APEX application.

In the new Apex application, we still use database user as Authentication approach for user to log into the application. The version is Apex 5.1 with ORDS 3 and Apache Tomcat web server. We understand APEX logs to database use APEX_PUBLIC_USER instead of individual user.

Try to use figure below to explain:

By Oracle Database role:

Tom and Mike

      Can select "HR table 1" , "HR table 2" and "HR table 3"     
       Can't update "HR table 1" , "HR table 2" and "HR table 3"  
       Can't view "Finance Table 1"

      Can't update "Finance Table 1"

George

      Can select "HR table 1" , "HR table 2" and "HR table 3"

      Can update "HR table 1" , "HR table 2" and "HR table 3"

      Can't view "Finance Table 1"

      Can't update "Finance Table 1"

Steve

      Can select "HR table 1" , "HR table 2" and "HR table 3"

      Can't update "HR table 1" , "HR table 2" and "HR table 3"

      Can view "Finance Table 1"

      Can update "Finance Table 1"

What we are being asked is to find a solution to rely on database role granted to each database user to control their page access in APEX - assuming region to table is one to one mapping as a starting position (and we know this won't stand in a real system)

We have been trying to come up with an solution. So far we don't think this can be done easily. First challenge is to programmatic link a page or region to a table. We don't think APEX offers anything out-of-box for us to know which table a region is displaying.

Can you please help?

Regards

Guang