Restrict Oracle DB Login based on SQL Client being used
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) &lt;&gt; '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 &quot;classic&quot; 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