I am a newbie to Oracle APEX and I am currently transforming an application developed in Oracle forms to Oracle Apex. But in the Oracle form application, the security levels were handled based on the login user because each application users have a separate database account and they use their database credentials for authentication as well as for authorization. Furthermore, in some procedures and functions, we have used pl/sql keyword 'user' to get current users and authorize different transactions based on that instead of passing the login username as a parameter for each procedure and function.
In the APEX application, I was able to set the Oracle Database user schema for authentication purposes but in APEX it always keeps the same public user (In my case APEX_PUBLIC_USER) throughout the transaction even though I loged in with different user credentials. As a result, the application is unable to differentiate each user. Also, I hesitate to pass the login user like v('APP_USER') as a parameter to every pl/sql code as the 'user' keyword has been used in many places. I know that keeping a database user session throughout the transaction is not possible in web applications like in Oracle Forms applications. However, I appreciate it if anybody could suggest a workaround to overcome this issue.
Not sure if this is what you want to hear, but I would not try to solve this and instead now take the time to redesign the application so it is easier to manage and customise later. This might be a good time to drop the technical debt that was built up in the forms application and make the code future-proof. Taking over the complex, forms based architecture will most likely make any future development more complex (read costly).