ImplementationAs 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 clientSometimes 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!
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!