PREGUNTAS
¿Quién bloquea mi cuenta?
¿Quién bloqueó una cuenta de usuario?
¿Cómo rastrear el bloqueo de la cuenta?
¿Quién especifica una contraseña incorrecta durante el inicio de sesión?
Identificar un proceso que bloquea una cuenta de Oracle mediante el uso de una contraseña incorrecta.
SINTOMAS
Un usuario se queja de que su cuenta se bloquea cada 5-10 minutos. Pidió averiguar por qué sucede y quién lo bloquea.
PORQUE?
La situación puede ocurrir en caso de que alguien (script o aplicación) esté tratando de conectarse a una base de datos con una contraseña incorrecta, bloqueando así una cuenta. De forma predeterminada, el perfil PREDETERMINADO permite 10 intentos fallidos de inicio de sesión antes de que se bloquee una cuenta. Debido a varios intentos fallidos de inicio de sesión, una cuenta cambia su estado a «LOCKED (TIMED)». Una cuenta está bloqueada debido al parámetro de perfil FAILED_LOGIN_ATTEMPTS.
CASO DE PRUEBA
Así que cree un usuario para este caso de prueba y recopile detalles
CREATE USER TESTCASE IDENTIFIED BY TESTCASE;
GRANT CONNECT TO TESTCASE;
SET LINES 200
SET PAGES 999
COL USERNAME FOR A10
COL ACCOUNT_STATUS FOR A15
COL CREATED FOR A10
COL LOCK_DATE FOR A10
COL EXPIRY_DATE FOR A10
COL PROFILE FOR A11
SELECT USERNAME,ACCOUNT_STATUS,CREATED,LOCK_DATE,EXPIRY_DATE,PROFILE FROM DBA_USERS WHERE USERNAME='TESTCASE';
USERNAME ACCOUNT_STATUS CREATED LOCK_DATE EXPIRY_DAT PROFILE
---------- --------------- ---------- ---------- ---------- -----------
TESTCASE OPEN 18-DEC-17 16-JUN-18 DEFAULT
COL RESOURCE_NAME FOR A25
COL RESOURCE_TYPE FOR A10
COL LIMIT FOR A10
SELECT * FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME IN ('FAILED_LOGIN_ATTEMPTS','PASSWORD_LOCK_TIME');
PROFILE RESOURCE_NAME RESOURCE_T LIMIT COMMON
----------- ------------------------- ---------- ---------- ---------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 NO
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 NO
Según la documentación oficial
FAILED_LOGIN_ATTEMPTS especifica el número de intentos fallidos consecutivos de iniciar sesión en la cuenta de usuario antes de que se bloquee la cuenta. Si omite esta cláusula, el valor predeterminado es 10 veces.
PASSWORD_LOCK_TIME especifica el número de días que se bloqueará una cuenta después del número especificado de intentos fallidos de inicio de sesión consecutivos. Si omite esta cláusula, el valor predeterminado es 1 día.
Siempre que la pista de auditoría esté activada.
COL NAME FOR A15 COL VALUE FOR A10 SELECT NAME,DISPLAY_VALUE VALUE FROM V$PARAMETER WHERE NAME='audit_trail'; NAME VALUE --------------- ---------- audit_trail DB
Si la auditoría está deshabilitada, habilítela con el siguiente comando y reinicie la base de datos
ALTER SYSTEM SET AUDIT_TRAIL='DB' SCOPE=SPFILE;
Habilite la auditoría para intentos de inicio de sesión fallidos y luego consulte la vista DBA_AUDIT_SESSION
AUDIT SESSION WHENEVER NOT SUCCESSFUL; Audit succeeded. SELECT * FROM DBA_AUDIT_SESSION; no rows selected
Simulemos la situación. Aquí está el script bash simple para bloquear la cuenta al conectarse con una contraseña incorrecta.
for i in {1..11}; do echo wrong_password | sqlplus TESTCASE 2>&1 1>/dev/null; done
Ahora descubre usuarios bloqueados
SET LINES 200 SET PAGES 999 COL USERNAME FOR A10 COL ACCOUNT_STATUS FOR A15 COL CREATED FOR A10 COL LOCK_DATE FOR A10 COL EXPIRY_DATE FOR A10 COL PROFILE FOR A11 SELECT USERNAME,ACCOUNT_STATUS,CREATED,LOCK_DATE,EXPIRY_DATE,PROFILE FROM DBA_USERS WHERE USERNAME='TESTCASE'; USERNAME ACCOUNT_STATUS CREATED LOCK_DATE EXPIRY_DAT PROFILE ---------- --------------- ---------- ---------- ---------- ----------- TESTCASE LOCKED(TIMED) 18-DEC-17 18-DEC-17 16-JUN-18 DEFAULT SQL> connect TESTCASE/TESTCASE ERROR: ORA-28000: the account is locked Warning: You are no longer connected to ORACLE.
Consultar tabla para obtener detalles
SET LINES 300
SET PAGES 999
COL INUM FOR 9999
COL OS_USERNAME FOR A10
COL USERNAME FOR A10
COL USERHOST FOR A30
COL ACTION_NAME FOR A10
COL OS_PROCESS FOR A10
COL TIMESTAMP FOR A20
SELECT INSTANCE_NUMBER INUM,OS_USERNAME,USERNAME,USERHOST,TO_CHAR(EXTENDED_TIMESTAMP,'DD-MON-YYYY HH24:MI:SS') TIMESTAMP,
ACTION_NAME,OS_PROCESS,RETURNCODE FROM DBA_AUDIT_SESSION WHERE RETURNCODE IN (1017, 28000) ORDER BY EXTENDED_TIMESTAMP;
INUM OS_USERNAM USERNAME USERHOST TIMESTAMP ACTION_NAM OS_PROCESS RETURNCODE
----- ---------- ---------- --------------- -------------------- ---------- ---------- ----------
0 oracle TESTCASE dbandtech 18-DEC-2017 13:25:23 LOGON 11533 1017
0 oracle TESTCASE dbandtech 18-DEC-2017 13:25:24 LOGON 11540 1017
0 oracle TESTCASE dbandtech 18-DEC-2017 13:25:25 LOGON 11547 1017
0 oracle TESTCASE dbandtech 18-DEC-2017 13:25:26 LOGON 11554 1017
0 oracle TESTCASE dbandtech 18-DEC-2017 13:25:27 LOGON 11561 1017
0 oracle TESTCASE dbandtech 18-DEC-2017 13:25:28 LOGON 11568 1017
0 oracle TESTCASE dbandtech 18-DEC-2017 13:25:29 LOGON 11575 1017
0 oracle TESTCASE dbandtech 18-DEC-2017 13:25:31 LOGON 11582 1017
0 oracle TESTCASE dbandtech 18-DEC-2017 13:25:32 LOGON 11589 1017
0 oracle TESTCASE dbandtech 18-DEC-2017 13:25:33 LOGON 11596 1017
0 oracle TESTCASE dbandtech 18-DEC-2017 13:25:33 LOGON 11603 28000
11 rows selected.
COL SYSTIMESTAMP FOR A40
SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
----------------------------------------
18-DEC-17 01.26.42.485294 PM +03:00
La vista DBA_AUDIT_SESSION contiene todos los intentos fallidos de inicio de sesión realizados por el script. La columna RETURNCODE en la vista corresponde al error ORA- generado en los inicios de sesión fallidos. Es suficiente información para averiguar quién o qué causó el bloqueo de la cuenta. Aquí, el usuario TESTCASE intentó sin éxito conectarse a la base de datos con una contraseña incorrecta 10 veces desde el servidor dbandtech.
oracle@dbandtech: oerr ora 1017 01017, 00000, "invalid username/password; logon denied" // *Cause: // *Action: oracle@dbandtech: oerr ora 28000 28000, 00000, "the account is locked" // *Cause: The user has entered wrong password consequently for maximum // number of times specified by the user's profile parameter // FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account // *Action: Wait for PASSWORD_LOCK_TIME or contact DBA
Para desbloquear la cuenta, puede esperar 1 día según el valor del perfil PASSWORD_LOCK_TIME, o simplemente desbloquearla con el siguiente comando
ALTER USER TESTCASE ACCOUNT UNLOCK; connect TESTCASE/TESTCASE Connected. SHO USER USER is "TESTCASE" SET LINES 200 SET PAGES 999 COL USERNAME FOR A10 COL ACCOUNT_STATUS FOR A15 COL CREATED FOR A10 COL LOCK_DATE FOR A10 COL EXPIRY_DATE FOR A10 COL PROFILE FOR A11 SELECT USERNAME,ACCOUNT_STATUS,CREATED,LOCK_DATE,EXPIRY_DATE,PROFILE FROM DBA_USERS WHERE USERNAME='TESTCASE'; USERNAME ACCOUNT_STATUS CREATED LOCK_DATE EXPIRY_DAT PROFILE ---------- --------------- ---------- ---------- ---------- ----------- TESTCASE OPEN 18-DEC-17 16-JUN-18 DEFAULT
Para deshabilitar la auditoría, emita el siguiente comando
NOAUDIT SESSION WHENEVER NOT SUCCESSFUL;
