use_password in a after log on on database trigger

Hi, we would like to ensure that use_password is executed for the domain index owner for each of the Oracle users and are using the statement
execute jchem_core_pkg.use_password('name of index owner, 'encryptedpassword');
to connect.

This statement works fine directly in SQL scripts or in procedures.

We would like to ensure that this statement is executed for each user and implemented it therefore in a procedure called in the "after log on database" trigger.

It works fine as long as we don't restart the JChem Server. As soon as we restart the JChem server - nobody can logon because of this statement.
If we remove it - everything is fine (but we can't ensure that use_password is executed automatically after a Jchem server restart).

What cause the problem with the log on trigger?
Do you have other ideas how to enable a "automatic" use_password action after restarting the JChem server.

Thanks for your response, Edith

 

Hi,

We've tried a similar after logon trigger on the database.

First we started JChem server, and the trigger worked fine, we could search id the database, etc.

Then we stopped JChem server. We could of course not search on the tables, and after logging off, we couldn't log on again. If the users want to use other functions than Jchem, we would recommend handling the exception thrown by use_password and this way at least they can log in to the db.

After restarting the JChem server, we logged on again successfully, and could use JChem Cartridge functions again.

As I compare this to your observation, I see that after restarting, you still can't log in.If that's so, can you send the error message the users receive at login?

If this is solved, the general solution may be killing the users session after restarting JChem server, but that's a very aggressive solution.

We suggest running the JChem server all the time, and don't stop it unless it is absolutely necessary. So we are very much interested in that do you plan to shut down regularly the server, or is it only a precaution for some extreme case (e.g. harware failure)?

Thank you,

Tamas

Hi Tamas,

We've found another solution. We realized that we are running in an endless loop if we don't deactivate this trigger for all "non-user" users like SYS, SYSTEM. And it seems to work now (was implemented Friday on a testmachine)

Regarding your question

"We suggest running the JChem server all the time, and don't stop it unless it is absolutely necessary. So we are very much interested in that do you plan to shut down regularly the server, or is it only a precaution for some extreme case (e.g. harware failure)?"

The environments have to be set up in general in a way that no manual interaction should be necessary. We have a lot of machines and there is now way to execute manual steps if a machine has to be rebooted or a database has to be restarted e.g. due to updates / support issues etc.

If you need more details please let me know.

Best regards Edith

Hi,

Thank you for your details, we understand this is an important issue for you to do automatically.

Regards,

Tamas