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?
You can check it with a database trigger, for example:
CREATE ROLE ROLE_POWER_USER NOT IDENTIFIED; CREATE OR REPLACE TRIGGER LOG_T_LOGON AFTER LOGON ON DATABASE DECLARE osUser VARCHAR2(30); machine VARCHAR2(100); prog VARCHAR2(100) ip VARCHAR2(15); BEGIN IF ora_login_user IS NULL THEN RETURN; END IF; SELECT OSUSER, MACHINE, PROGRAM, ora_client_ip_address INTO osUser, machine, prog, ip FROM V$SESSION WHERE SID = SYS_CONTEXT('USERENV', 'SID'); IF NOT DBMS_SESSION.IS_ROLE_ENABLED('ROLE_POWER_USER') THEN IF LOWER(prog) <> 'your_application_name.exe' THEN RAISE_APPLICATION_ERROR(-20000, 'Logon denied: You must use only the official client application'); END IF; ELSE IF LOWER(prog) NOT IN ('sqlplus.exe', 'toad.exe') THEN RAISE_APPLICATION_ERROR(-20000, 'Logon denied: You must use only SQL*Plus or TOAD for you private queries'); END IF; END IF; -- Successful login, continue as normal END; /
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 example
DBA role, or
SYS 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 it
your_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