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
Related Q&A from Dan Norris
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.