I'd like to see a few examples on how to create a custom authentication method for Apex.

I have this Forms 6i application which will soon be upgraded to 11g. This app have this authentication method:

- Users authenticate at the application level.
- The application maps the app user to the actual database user.
- The application holds metadata and application context used for views, and all data is accessed through views after application context is set.

There is a custom login where they must supply application username and password. Also which enterprise or organization they will be working in the current session. Once current password and expiricy date is confirmed, another database session is used mapping the application user to the database user. This is transparent to the application user.

another database session is used mapping the application user to the database user

I'm not totally sure on what you mean by 'mapping application user to database user'.
Does this mean that a user has login credentials which are stored in your table, but also that users may connect with a different database user based on such information?
And 'another database session is used' matters how exactly within this context?

For example, if all that actually has to happen is:
- user provides credentials
- credentials are checked against a table

then setting up authentication shouldn't be too hard.

I'm not very versed in the authentication universe though, but maybe sufficiently clarifying things may help.

We internally create a new session using the NEW_FORM builtin. When opening this new forms/ database session we know which is corelated value. For example:

- Application user (APPUSER1) authenticates giving a valid password. This value is checked against a database table in our app.

- Internally we know APP_USER maps to DATBASE_USER1. Son we open this new Form / database connection with this user (on the database).

- Of course in Oracle Forms we make an initial connection used only to authenticate the application user.

Not sure how to do that on Apex..... My idea is to create a custom login procedure and sort of reuse the authetication funtion we already have. But not sure if this is the best way to go. So i was hoping some feedback from the community.

Well, as i said before: checking the user credentials against a table should be fairly easy to implement.
However, it's this "mapping" you're on about. An apex application has a parsing schema, and the user that makes changes in the database is not a user's database user. Rather, it is the apex user doing this (apex_public_user).
So you authenticate, and that's it. Database sessions are managed by apex and is pools connections. Remember that there is no guarantee that you will be using the same database session throughout the use of the application.

If you want to set context however, you can do that through application properties > security, and set initialization PLSQL code.

Documentation wrote:
Use this attribute to enter a PL/SQL block that sets a context for the database session associated with the current "show page" or "accept page" request. The block you enter here is executed at a very early point during the page request, immediately after the APP_USER value is established. The value of APP_USER (using :APP_USER or v('APP_USER')) may be used within the block. Values of other items in session state may be referenced as well, but any such items must have been established in session state before the initiation of the current page request. Consider the following example:

It sets the value of USERPRIV in the context named CTX_USER_QRY to the value returned by the function my_function in package my_package. The function is passed the current value of APP_USER as an input argument. Presumably, the named context would be used in a VPD policy ( created within the application's parsing schema) to effect the generation of predicates appropriate to the authenticated user.

Virtual Private Database, also know as Fine-Grained Access Control or FGAC, is an Oracle database feature that provides an application programming interface (API) that enables developers to assign security policies to database tables and views. Using PL/SQL, developers can create security policies with stored procedures and bind the procedures to a table or view by means of a call to an RDBMS package. Such policies are based on the content of application data stored within the database, or based on context variables provided by Oracle database. In this way, VPD permits access security mechanisms to be removed from applications, and to be situated closer to particular schemas.

The code entered here need not pertain to VPD/FGAC and may not be related to security at all. Any code that needs to be executed at the earliest point in a page request can be placed here. For example, the following code sets the database session time zone for every page request: