Es bastante común que en una base de datos Oracle tengamos la necesidad de saber si se están realizando conexiones con determinados usuarios. Esto puede ser por diferentes motivos, esquemas o usuarios creados y que nunca se han utilizado, empleados desvinculados, aplicaciones que se han migrado de base de datos pero sus esquemas originales aún permanecen, etc. Pueden existir muchas y variada razones.
Para poder detectar esto, sin necesidad de recurrir a la auditoria, desde la versión 12c Oracle comenzó a registrar el último inicio de sesión exitoso.
Esta información es almacenada en la tabla SYS.USER $ y pueder ser consultada en la columna LAST_LOGIN de la vista DBA_USERS. Como ya mencioné, esta funcionalidad es independiente de la auditoria.
Cuando iniciamos sesión utilizando SQL*Plus, podemos ver esta información en el texto del banner.
[oracle@server01 ORADB12C][~]$ sqlplus test01
SQL*Plus: Release 12.1.0.2.0 Production on Sat Feb 13 06:31:51 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter password:
Last Successful login time: Sat Feb 13 2021 06:31:01 -05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Como comentaba anteriormente, la información es almacenada en la tabla SYS.USER$, específicamente en la columna SPARE6, y puede ser consultada de manera simple utilizando la vista DBA_USERS (columna LAST_LOGIN).
SQL> col username for a15
SQL> col last_login for a25
SQL> alter session set NLS_TIMESTAMP_TZ_FORMAT='DD.MM.YYYY HH24:MI:SS';
Session altered.
SQL> select username,last_login from dba_users where username='TEST01';
USERNAME LAST_LOGIN
--------------- -------------------------
TEST01 13.02.2021 06:31:54
SQL> col name for a15
SQL> col last_login for a25
SQL> alter session set NLS_TIMESTAMP_TZ_FORMAT='DD.MM.YYYY HH24:MI:SS';
SQL> select name, spare6 from user$ where name='TEST01';
NAME SPARE6
--------------- ---------
TEST01 13-FEB-21
Con esta información disponible, resulta sencillo para un DBA indagar sobre el comportamiento relacionado con el inicio de session de determinados usuarios.
Por ejemplo:
Detectar aquellos que nunca iniciaron sesión:
SQL> select username, last_login from dba_users where last_login IS NULL;
Conocer cuando fue el último login de aquellos que lo hicieron:
SQL> select username, last_login from dba_users where last_login IS NOT NULL;
Identificar los usuarios que no iniciaron sesión por una determinada cantidad de tiempo (por ejemplo los últimos 30 días):
SQL> select sysdate from dual;
SYSDATE
---------
13-FEB-21
SQL> select username, last_login from dba_users where last_login < sysdate - 30;
USERNAME LAST_LOGIN
--------------- -------------------------
HR_ADMIN 09.12.2020 09:12:34
En definitiva, se puede obtener información interesante que posteriormente deberá ser analizada para poder detectar patrones de comportamiento sobre los cuales profundizar, tomar decisiones y realizar acciones si fuera necesario.
Que acciones?, por ejemplo:
- Eliminar usuarios que ya no se conecten (por diferentes motivos).
- Eliminar esquemas que no se utilicen (ej. Esquemas de aplicaciones que se hayan migrado).
- Detectar conexiones con el owner de la aplicación que no se deberían realizar.
Estos son sólo algunos ejemplos y por supuesto existen muchos más casos y utilidades.
Algo a tener en cuenta:
Existe una opción de SQL*Plus, utilizando el parámetro –nologintime, para evitar que se muestre la información del ultimo login (“Last Successful login time: …”).
En este caso, si bien la información no es mostrada por SQL*Plus al iniciar la sesión, igualmente queda registrada y disponible en la tabla SYS.USERS$
[oracle@server01 ORADB12C][~]$ sqlplus -nologintime TEST01
SQL*Plus: Release 12.1.0.2.0 Production on Sat Feb 13 08:50:13 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics, Real Application Testing and Unified Auditing options
SQL> select username, last_login from dba_users where username='TEST01';
USERNAME LAST_LOGIN
--------------- -------------------------
TEST01 13.02.2021 08:50:15
Por último, es importante destacar que los inicios de sesión del usuario SYS y otros que utilicen passwordfile para su autenticación no son registrados en la tabla SYS.USERS$.
Dado que esta función está destinada principalmente a un uso interactivo, la limitación en la captura de esta información es, hasta cierto punto, comprensible. Registrar de esta manera el último inicio de sesión de usuarios con privilegios administrativos (SYSDBA, SYSDG, SYSBACKUP, etc.) redundaría en el registro de muchísimas conexiones internas de RMAN o Dataguard por ejemplo.
Si necesitamos garantizar la trazabilidad de sesiones de este tipo de usuarios, es mandatorio que utilicemos la auditoría de la base de datos con la opción AUDIT_SYS_OPERATIONS habilitada.
No obstante, a partir de la versión 12.2, el horario y fecha del ultimo inicio de sesión exitoso de usuarios administrativos, que utilizan password-file como método de autenticación, es capturado en la vista dinámica V$PWFILE_USERS (columna LAST_LOGIN).
Conclusión:
Esta simple mejora incorporada en Oracle 12c nos puede ayudar bastante en la detección y análisis de determinados comportamientos durante el ciclo de vida de usuarios y esquemas.
Usuarios que no son utilizados y siguen activos, esquemas (con datos) que ya no son necesarios, conexiones de usuarios que no se deberían realizar, etc. son sólo algunos casos ejemplo.
Es necesario tener presente que de ninguna manera el uso de esta funcionalidad debe reemplazar a una buena estrategia de auditoria, pero si es un buen complemento que permite, de forma ágil y sencilla, obtener información relevante para tomar acciones rápidas o comenzar una tarea más profunda de análisis en caso de ser necesario.
No hay comentarios:
Publicar un comentario