1) I need to configure a additional database which has usernames and when I try to login to winchill , after entering credentials, My request has to go the additional Database( like oracle) which has usernames ( In some users table) and some business logic need to validate the entered username against the usernames present in the newly created db (users table). if the user exists, it need to redirect to windchill, otherwise It need to display a not authorized message. The whole process should be completed before touching the windchill application or Windchill DS.
2) I need to know how to break the flow to add the additional authentication.
With your requirement for "The whole process should be completed before touching the windchill application or Windchill DS" in 1) it sounds like 2) needs to happen at the Apache level. Maybe mod_authn_dbd - Apache HTTP Server Version 2.4 is an option?
Querying an application database from web server is not a secure enterprise application deployment practice. Any potential attack on web server could compromise the application database and more over the LDAP is more read friendly than database.
That being said, from your description what you are looking is just to validate the username not the password. The password would still be stored in Windchill DS and be used to authenticate the user - Is it correct? If you are not planning to store passwords in database, the purpose of the database table is just to store valid set of users? To me this is what a LDAP group does, you can create a LDAP group filter which will ensure that only selected set of users will have access to application.
Another clean way is to use AuthGroupFile directive , periodically populate a file in Apache conf directory with a valid list of users. This can be done by a custom tool which periodically queries the database and populate the file. If you need more info, I can share details.
If this doesn't work for you, you can opt for a Single Sign On application - there are many open source free-wares out there which could do a db query,validate the user and redirect it to Windchill's apache. Careful here, you should make sure this works with Creo and Workgroup managers.
You can also setup reverse proxy and authenticate against multiple providers.
Thanks you for your valueble inputs , can you share the information regarding the AuthGroupFile directive and the custom tool which queries the DB. This should help us. Thanks in advance.