Prevent sqlplus access to database

Preventing user access with non application tool (i.e. SQL*Plus or Toad) is one of very important task in proper security setup. Here is briefly how can one pretty easy implement straight control for that.

Implementation

As sysdba create role "SQLPLUS_ROLE":

CREATE ROLE SQLPLUS_ROLE NOT IDENTIFIED;

BEGIN
  SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
  SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
  SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (
   'SQLPLUS_ROLE', 'DEFAULT_CONSUMER_GROUP', false);
  SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/

This role must be added to every user or roles (execept ones with sysdba role) that want to access to database outside default application. Here are some examples.
First for users:

GRANT SQLPLUS_ROLE TO DAMIRV WITH ADMIN OPTION;
GRANT SQLPLUS_ROLE TO SCOTT;

and one for roles (works also for all users that own role "DBA" in this case):

GRANT SQLPLUS_ROLE TO DBA;

Then create database trigger (after logon) whose job is to check EVERY incoming connection to database:

CREATE OR REPLACE TRIGGER block_tools_TGB$ALG
  AFTER LOGON ON DATABASE
DECLARE
  v_prog sys.v_$session.program%TYPE;
  l_count pls_integer;
  
  my_forced_exception EXCEPTION;
  PRAGMA EXCEPTION_INIT(MY_FORCED_EXCEPTION, -20101);  
BEGIN
  SELECT program 
    INTO v_prog 
    FROM sys.v_$session
   WHERE      audsid = USERENV('SESSIONID')
         AND  audsid != 0  -- Don't Check SYS Connections
         AND  ROWNUM = 1;  -- Parallel processes will have the same AUDSID's
  IF  UPPER(v_prog) LIKE '%SQL%' OR        -- SQL like programs!
      UPPER(v_prog) LIKE '%TOAD%' OR       -- Toad
      UPPER(v_prog) LIKE '%BUSOBJ%' OR     -- Business Objects
      UPPER(v_prog) LIKE '%EXCEL%'         -- MS-Excel plug-in
  THEN
    SELECT COUNT (*)
      INTO l_count
      FROM dba_role_privs
     WHERE     granted_role = 'SQLPLUS_ROLE'
           AND grantee = SYS_CONTEXT ('userenv', 'session_user');    
     IF l_count=0 THEN
       RAISE my_forced_exception;
     END IF;
  END IF;
EXCEPTION
  WHEN my_forced_exception THEN 
    RAISE_APPLICATION_ERROR(-20101, 'Action not allowed. Please contact your DBA to help you!');
  WHEN OTHERS THEN
    null;
END;
/

As you can see mine restrictions are pretty exact. However test these settings on test before implementing this on production.
Placing host names or IP addresses is also very common method to restrict access. For that use sys_context values:

sys_context('USERENV','IP_ADDRESS') -- IP address
sys_context('USERENV','HOST')       -- host name of client

Sometimes those values might be hidden (ah those Linux admins) and if this is not the case for your normal environment - terminate such a session immediate!!

Here is very important to understand that this trigger must not raise any error (except wanted one) because no one can logon to database. This is why my_forced_exception is used. With mine exception I can easy ensure proper functioning of database regardless any other error that might raised in this trigger!

In praxis...

SQL> grant connect,resource to xxyy identified by qw;

Grant succeeded.

SQL> conn xxyy/qw@some_db;
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Action not allowed. Please contact your DBA to help you!
ORA-06512: at line 25


Warning: You are no longer connected to ORACLE.
SQL>

Now add role SQLPLUS_ROLE to user xxyy and try to reconnect again:

SQL> grant SQLPLUS_ROLE to xxyy;

Grant succeeded.

SQL> conn xxyy/qw;
Connected.
SQL>

Works!

Cheers!