We have an application in our organization that is based on Oracle Forms 11g (on Oracle Fusion Middleware/WLS). Users have access to the application through accounts created at the database (i.e. they exist in dba_users
). Each user is assigned a specific role based on their area of work and they use the user id to log on to the application. The user may have read/write access to certain functionality in the application and for this their roles have the following permissions-
EXECUTE ANY LIBRARY
SELECT ANY SEQUENCE
EXECUTE ANY TYPE
EXECUTE ANY PROCEDURE
UPDATE ANY TABLE
SELECT ANY TABLE
DELETE ANY TABLE
EXECUTE ANY INDEXTYPE
INSERT ANY TABLE
Now we have some users requesting for SQL Client access (i.e. TNS settings) so that they can connect to the DB and perform queries for their research. These business users are ofcourse knowledgeable in SQL but we want to restrict their access based on the type of client they use to log on to the database. Any client other than the application itself, should restrict them to "read only".
Is there a way to achieve this?
Best Answer
You can check it with a database trigger, for example:
You can also check other conditions like IP-Address or the machine name.
SELECT
privileges on tables and views you have to grant to the user or ROLE in the "classic" way. This trigger only prevents to logon to the database with certain tools.Note, a user with System Privilege
ADMINISTER DATABASE TRIGGER
(for exampleDBA
role, orSYS
of course) never get the exception, i.e. they can logon to the database in any case and you cannot block this by the trigger.Another note: This trigger is insecure! For example you can simply make a copy of your local
sqlplus.exe
and name ityour_application_name.exe
. Then this trigger would allow to use it.With JDBC it is even a simple property you can set, see https://stackoverflow.com/questions/42027389/programatically-set-vsession-program-property