Problem solve Get help with specific problems with your technologies, process and projects.

Oracle forms only accessing the database

I would like to have Oracle forms only accessing my database. I?ve heard about the POP table, and I have also read that to maintain a trigger you must maintain the application product by disconnecting the user when he tries to access the database from some other application. Do you have any suggestions?
I think you mean the PUP (Product_User_Profile) table which governs what commands are allowed from the SQL*Plus tool. Unfortunately, it is the SQL*Plus tool that is hard-coded to look for this table and follow the directives found there. No other tools (like forms, reports, ODBC-connected applications, etc.) will check the PUP table by themselves.

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.