Friday, January 22, 2010

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!

8 comments :

  1. This is nice one Damir. I was thinking if I can do the same thing with product_user_profile table like here but it can only disable the command when the user is already in, you can still disable the certain command though.

    ReplyDelete
  2. Coskan,
    Thank you very much for nice words.
    I'm aware of that tool. But it doesn't prevent many things ... SQL*Plus can be easy renamed to ORACLE.EXE (what really exists on Windows!) and then it is helpless!
    Analyzing statement level is not easy and I do avoid it in any way.
    Suggestion is that mine topic should be use as a way to adopt to particular environment with additional changes.
    Another way is to use dedicated listener with hard codded IP addresses that can use it, which will allow sqlplus connections and this is really then bullet prof concept.
    Regards,
    Damir Vadas

    ReplyDelete
  3. Hi all,

    I just added some changes to trigger definition. This is nice example how to handle exceptions in sensitive places as this trigger is!

    Damir Vadas

    ReplyDelete
  4. Hi Damir,

    this is great solution for enforcing additional security for production database. I will certainly save it memorize it somewhere :)

    Nice post...

    Enjoy,
    Marko

    ReplyDelete
  5. A frend of mine remind me that here is nice point to place:
    sys.dbms_system.ksdwrt(2,' -- '||to_char(sysdate)||' -- ');
    sys.dbms_system.ksdwrt(2,'"'||v_prog||'"');
    which write program name into alert log file. This is exclusively for collecting program names that attack database and analyze them lately.
    ;-)
    Damir

    ReplyDelete
  6. if i changes my program name of toad to any other name we can enter into production db
    its not catching in module also what can we do in this situation

    my email id
    rkkpotty@gmail.com

    ReplyDelete
  7. @RKKPOTTI,

    Right. But...
    Mine example was catching programs that are not allowed. Think about in this way.
    1) Record all programs that come to your database (login lilnk with insert to table with one filed prg_name, on which is unique key) and catch exceptions ORA-00001
    2) After some time you have a collection of all programs that are available to login to your database
    3) Put them all in checking and now you have narrowed set - you are allowing programs not checking if it is a problematic one-this is huge different.
    If this doesn't suit you then:
    1) add role POWER_SQL_ROLE to users that can use additional programs (not in list from previous example). In this way if user has no role "POWER_SQL_ROLE" then only limited list of programs an be used.
    2) To be more restricted create aditional listener with fixed IP addreses for "POWER_SQL_ROLE" users than can use powerfull login.

    Hope this is OK as small improvement.

    Mine topic was to show basic usage and there will be always improvements and configuration exceptions...

    Damir Vadas
    http://damir-vadas.blogspot.com/

    ReplyDelete
  8. I came across your posting by chance, this issue has been discussed over and over again, any user can just go and rename the executable name , and that is it

    ex: copy sqlplus.exe mytool.exe

    now run mytool.exe , and i am in

    ReplyDelete

Zagreb u srcu!

Copyright © 2009-2014 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign