Hace un tiempo, me vi en la necesidad de MATAR SESIONES NO PERMITIDAS en ORACLE. Despues de googlear un rato y de haber realizado la respectiva consulta por estos lares, pues al parecer no a muchos se les a ocurrido una solucion similar.
Con el afan de dejarme entender, voy a contextualizar el problema que da sustento a la solucion que ahora deseo compartir.
PROBLEMA: Es bien comun para un DBA (Data Base Administrator) que de repente las claves entre usuarios se ha hecho publica, en cuyos casos la solucion inmediata por normativa es poner aleatoriamente o periodicamente un CAMBIO DE CLAVE A USUARIOS en la Base de Datos. Sin embargo, la situacion se complica cuando dichas cuentas de usuario son de uso PRIVATIVO en algun software en el cual se vuelve engorroso la actualizacion de ese tipo de cambios.
ALTERNATIVA: Asignar cuentas con cierto nivel de acceso a los usuarios y denegar acceso con otras cuentas que no sean las asignadas para el roll que obstentan.
SOLUCION: El siguiente ejemplo controla los accesos a una DB. Identificando unicamente que el acceso ganado sea atravez del ya conocido TOAD. (seria de mejorarlo si queremos controlar accesos no permitidos por usuarios externos a la empresa).
Primeramente crearemos una simple tabla donde llevaremos registrados los intentos de LOGON no permitidos.
CREATE TABLE MIESQUEMA.DBA_CONTROL_LOGIN
(
USERNAME VARCHAR2(4000 BYTE),
OSUSER VARCHAR2(30 BYTE),
SID NUMBER,
SERIAL# NUMBER,
LOCKWAIT VARCHAR2(16 BYTE),
STATUS VARCHAR2(8 BYTE),
MODULE VARCHAR2(48 BYTE),
MACHINE VARCHAR2(64 BYTE),
PROGRAM VARCHAR2(48 BYTE),
LOGON_TIME VARCHAR2(20 BYTE)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE INDEX MIESQUEMA.CONTROL_LOGIN_IDX ON MIESQUEMA.DBA_CONTROL_LOGIN
(OSUSER)
LOGGING
TABLESPACE BW_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
DROP PUBLIC SYNONYM DBA_CONTROL_LOGIN;
CREATE PUBLIC SYNONYM DBA_CONTROL_LOGIN FOR MIESQUEMA.DBA_CONTROL_LOGIN;
CREATE OR REPLACE procedure SOFTWORKS.DBA_POLICE_LOGIN is
pUsername dba_control_login.username%type;
pOsuser dba_control_login.OSUSER%type;
pSid dba_control_login.sid%type;
pSerial# dba_control_login.serial#%type;
pLockwait dba_control_login.lockwait%type;
pStatus dba_control_login.Status%type;
pModule dba_control_login.module%type;
pMachine dba_control_login.machine%type;
pProgram dba_control_login.program%type;
pLogon_time dba_control_login.logon_time%type;
ERR INT :=0;
CURSOR C1 Is
SELECT LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s
where UPPER(module) like '%TOAD%' AND s.status<>'KILLED'
and LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') IN(':USUARIO1')
and LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') NOT
IN(':USUARIO2')
CONNECT BY PRIOR s.sid = s.blocking_session;
BEGIN
for x in C1 loop
pusername :=x.username;
posuser :=x.osuser;
psid :=x.sid;
pserial# :=x.serial#;
plockwait :=x.lockwait;
pstatus :=x.status;
pmodule :=x.module;
pmachine :=x.machine;
pprogram :=x.program;
plogon_time :=x.logon_time;
EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION
'||''''||pSid||','||pSerial#||'''';
ERR := ERR+1;
COMMIT;
EXECUTE IMMEDIATE 'INSERT INTO SOFTWORKS.DBA_CONTROL_LOGIN VALUES ('
||''''||pusername||''''||','
||''''||posuser||''''||','
||''''||psid||''''||','
||''''||pserial#||''''||','
||''''||plockwait||''''||','
||''''||pstatus||''''||','
||''''||pmodule||''''||','
||''''||pmachine||''''||','
||''''||pprogram||''''||','
||''''||plogon_time||''''||')';
COMMIT;
end loop;
END;
/
luego creamos un procedimiento que controle la accion que necesitamos el LOGON.
CREATE OR REPLACE procedure MIESQUEMA.DBA_POLICE_LOGIN is
pUsername dba_control_login.username%type;
pOsuser dba_control_login.OSUSER%type;
pSid dba_control_login.sid%type;
pSerial# dba_control_login.serial#%type;
pLockwait dba_control_login.lockwait%type;
pStatus dba_control_login.Status%type;
pModule dba_control_login.module%type;
pMachine dba_control_login.machine%type;
pProgram dba_control_login.program%type;
pLogon_time dba_control_login.logon_time%type;
ERR INT :=0;
CURSOR C1 Is
SELECT LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s
where UPPER(module) like '%TOAD%' AND s.status<>'KILLED'
and LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') IN('USUARIOABLOQUEAR')
CONNECT BY PRIOR s.sid = s.blocking_session;
BEGIN
for x in C1 loop
pusername :=x.username;
posuser :=x.osuser;
psid :=x.sid;
pserial# :=x.serial#;
plockwait :=x.lockwait;
pstatus :=x.status;
pmodule :=x.module;
pmachine :=x.machine;
pprogram :=x.program;
plogon_time :=x.logon_time;
EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION
'||''''||pSid||','||pSerial#||'''';
ERR := ERR+1;
COMMIT;
EXECUTE IMMEDIATE 'INSERT INTO MIESQUEMA.DBA_CONTROL_LOGIN VALUES ('
||''''||pusername||''''||','
||''''||posuser||''''||','
||''''||psid||''''||','
||''''||pserial#||''''||','
||''''||plockwait||''''||','
||''''||pstatus||''''||','
||''''||pmodule||''''||','
||''''||pmachine||''''||','
||''''||pprogram||''''||','
||''''||plogon_time||''''||')';
COMMIT;
end loop;
END;
/
Ahora solo nos falta crear un JOB que este ejecutando el procedimiento anteior, periodicamente para que cumpla su funcion de VIGIA en nuestra base de datos.
BEGIN
SYS.DBMS_JOB.REMOVE(45);
COMMIT;
END;
/
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'BEGIN MIESQUEMA.DBA_POLICE_LOGIN; END;'
,next_date => to_date('05-08-2009 12:18:55','dd/mm/yyyy hh24:mi:ss')
,interval => 'sysdate + (1 / 24 / 60 / 2)' -- SE CORREA CADA 60 SEGUNDOS
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/
Listo... con eso... me curo en salud de MATAR SESIONES no autorizadas con el TOAD a las bases de datos ORACLE.
