You allude to a trigger that could help. That would probably be a login trigger that would check the application name used to connect to the database at each login and disconnect anyone that does not connect from the desired application. That may work, but there's a specific feature that can be employed to do a similar thing.
One feature that seems to fit this pretty well is to use secure application roles. These are roles created like this:
CREATE ROLE app_role IDENTIFIED USING app_owner.some_package;
You write some_package in this case and call a procedure in that package from a login trigger. The procedure would do the desired checks and if they pass, it would call DBMS_SESSION.SET_ROLE to set the app_role in that session. If the checks don't pass, the role isn't set. Because of the definition of the role, the only way to enable it is by running a procedure from the some_package package. If all your application privileges are in that role (or maybe in several roles defined similarly), then you won't necessarily prohibit users from logging in and establishing a session using any tool they like. However, once they connect with an undesirable tool, they won't have any application privileges and will only be able to access objects with privileges granted to PUBLIC.
Dig Deeper on Oracle database security
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.