O gato morto é explicado no note ID 284344.1 - DBA_USERS.ACCOUNT_STATUS shows LOCKED after FAILED_LOGIN_ATTEMPTS Is Breached:
Expected behaviour is 1. Oracle release is <= 11.1.0.7. DBA_USERS.ACCOUNT_STATUS = LOCKED(TIMED) whenever the number of failed login attempts is > FAILED_LOGIN_ATTEMPTS 2. Oracle release is >= 11.2 and PASSWORD_LOCK_TIME = unlimited: DBA_USERS.ACCOUNT_STATUS = LOCKED whenever the number of failed login attempts is > FAILED_LOGIN_ATTEMPTS 3. Oracle release is >= 11.2 and PASSWORD_LOCK_TIME = <some fix value> DBA_USERS.ACCOUNT_STATUS = LOCKED(TIMED) whenever the number of failed login attempts is > FAILED_LOGIN_ATTEMPTS Note that 10.2.0.5 displays the same behavior as 11.2, because the fix that changed the behavior in 11.2 was introduced in 10.2.0.5. O banco do Chiappa deve ser 10.2.0.5, o do Paulo é 10.2.0.4. 2012/9/11 Ivan Ricardo Schuster <ivanr...@gmail.com>: > Tem algum gato morto aí. Antes realmente tinha testado em um banco > 11.2.0.2, criando um profile novo e só definindo o > FAILED_LOGIN_ATTEMPTS, mas usando o profile default em um banco > 10.2.0.4, também tenho "LOCKED(TIMED)": > > SQL> select * from dba_profiles where profile='DEFAULT'; > > PROFILE RESOURCE_NAME RESOURCE LIMIT > ------------------------------ -------------------------------- > -------- ---------------------------------------- > DEFAULT COMPOSITE_LIMIT KERNEL > UNLIMITED > DEFAULT SESSIONS_PER_USER KERNEL > UNLIMITED > DEFAULT CPU_PER_SESSION KERNEL > UNLIMITED > DEFAULT CPU_PER_CALL KERNEL > UNLIMITED > DEFAULT LOGICAL_READS_PER_SESSION KERNEL > UNLIMITED > DEFAULT LOGICAL_READS_PER_CALL KERNEL > UNLIMITED > DEFAULT IDLE_TIME KERNEL > UNLIMITED > DEFAULT CONNECT_TIME KERNEL > UNLIMITED > DEFAULT PRIVATE_SGA KERNEL > UNLIMITED > DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 > DEFAULT PASSWORD_LIFE_TIME PASSWORD > UNLIMITED > DEFAULT PASSWORD_REUSE_TIME PASSWORD > UNLIMITED > DEFAULT PASSWORD_REUSE_MAX PASSWORD > UNLIMITED > DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL > DEFAULT PASSWORD_LOCK_TIME PASSWORD > UNLIMITED > DEFAULT PASSWORD_GRACE_TIME PASSWORD > UNLIMITED > > SQL> alter user ivan profile default; > > User altered. > > SQL> alter user ivan account unlock; > > User altered. > > SQL> conn ivan/1 > ERROR: > ORA-01017: invalid username/password; logon denied > > > Warning: You are no longer connected to ORACLE. > SQL> conn ivan/1 > ERROR: > ORA-01017: invalid username/password; logon denied > > > SQL> conn ivan/1 > ERROR: > ORA-01017: invalid username/password; logon denied > > > SQL> conn ivan/1 > ERROR: > ORA-01017: invalid username/password; logon denied > > > SQL> conn ivan/1 > ERROR: > ORA-01017: invalid username/password; logon denied > > > SQL> conn ivan/1 > ERROR: > ORA-01017: invalid username/password; logon denied > > > SQL> conn ivan/1 > ERROR: > ORA-01017: invalid username/password; logon denied > > > SQL> conn ivan/1 > ERROR: > ORA-01017: invalid username/password; logon denied > > > SQL> conn ivan/1 > ERROR: > ORA-01017: invalid username/password; logon denied > > > SQL> conn ivan/1 > ERROR: > ORA-01017: invalid username/password; logon denied > > > SQL> conn ivan/1 > ERROR: > ORA-28000: the account is locked > > > SQL> conn / as sysdba > Connected. > SQL> select ACCOUNT_STATUS from dba_users where username='IVAN'; > > ACCOUNT_STATUS > -------------------------------- > LOCKED(TIMED) > > SQL> select banner from v$version; > > BANNER > ---------------------------------------------------------------- > Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod > PL/SQL Release 10.2.0.4.0 - Production > CORE 10.2.0.4.0 Production > TNS for Linux: Version 10.2.0.4.0 - Production > NLSRTL Version 10.2.0.4.0 - Production > > > > > > 2012/9/11 J. Laurindo Chiappa <jlchia...@yahoo.com.br>: >> Colega, se REALMENTE vc conferiu no database, consultando a DBA_PROFILES e >> a DBA_USERS (não se fiando em defaults e nem em documentação e/ou na palavra >> de analistas), e realmente os usuários estão no profile DEFAULT ** e ** o >> profile default realmente está Inalterado, então vc NÂO deveria ter esse >> status de LOCK (TIMED) , veja o teste : >> >> => com tudo Realmente default (é o caso deste database de testes) , um >> account só vai ficar bloqueado por ACCOUNT LOCK ou por senha errada por 10 >> vezes, veja : >> >> SYSTEM@O10GR2::SQL>select * from dba_profiles; >> >> PROFILE RESOURCE_NAME RESOURCE >> LIMIT >> ------------------------------ -------------------------------- -------- >> ------------ >> DEFAULT COMPOSITE_LIMIT KERNEL >> UNLIMITED >> DEFAULT SESSIONS_PER_USER KERNEL >> UNLIMITED >> DEFAULT CPU_PER_SESSION KERNEL >> UNLIMITED >> DEFAULT CPU_PER_CALL KERNEL >> UNLIMITED >> DEFAULT LOGICAL_READS_PER_SESSION KERNEL >> UNLIMITED >> DEFAULT LOGICAL_READS_PER_CALL KERNEL >> UNLIMITED >> DEFAULT IDLE_TIME KERNEL >> UNLIMITED >> DEFAULT CONNECT_TIME KERNEL >> UNLIMITED >> DEFAULT PRIVATE_SGA KERNEL >> UNLIMITED >> DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 >> DEFAULT PASSWORD_LIFE_TIME PASSWORD >> UNLIMITED >> DEFAULT PASSWORD_REUSE_TIME PASSWORD >> UNLIMITED >> DEFAULT PASSWORD_REUSE_MAX PASSWORD >> UNLIMITED >> DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL >> DEFAULT PASSWORD_LOCK_TIME PASSWORD >> UNLIMITED >> DEFAULT PASSWORD_GRACE_TIME PASSWORD >> UNLIMITED >> WKSYS_PROF COMPOSITE_LIMIT KERNEL >> DEFAULT >> WKSYS_PROF SESSIONS_PER_USER KERNEL >> DEFAULT >> WKSYS_PROF CPU_PER_SESSION KERNEL >> DEFAULT >> WKSYS_PROF CPU_PER_CALL KERNEL >> DEFAULT >> WKSYS_PROF LOGICAL_READS_PER_SESSION KERNEL >> DEFAULT >> WKSYS_PROF LOGICAL_READS_PER_CALL KERNEL >> DEFAULT >> WKSYS_PROF IDLE_TIME KERNEL >> DEFAULT >> WKSYS_PROF CONNECT_TIME KERNEL >> DEFAULT >> WKSYS_PROF PRIVATE_SGA KERNEL >> DEFAULT >> WKSYS_PROF FAILED_LOGIN_ATTEMPTS PASSWORD >> UNLIMITED >> WKSYS_PROF PASSWORD_LIFE_TIME PASSWORD >> DEFAULT >> WKSYS_PROF PASSWORD_REUSE_TIME PASSWORD >> DEFAULT >> WKSYS_PROF PASSWORD_REUSE_MAX PASSWORD >> DEFAULT >> WKSYS_PROF PASSWORD_VERIFY_FUNCTION PASSWORD >> DEFAULT >> WKSYS_PROF PASSWORD_LOCK_TIME PASSWORD >> DEFAULT >> WKSYS_PROF PASSWORD_GRACE_TIME PASSWORD >> DEFAULT >> >> 32 linhas selecionadas. >> >> => vou fazer USER_TESTE e SCOTT bloquearem : >> >> SYSTEM@O10GR2::SQL>alter user user_teste account lock; >> >> Usuario alterado. >> >> SYSTEM@O10GR2::SQL>connect scott/1 >> ERROR: >> ORA-01017: senha/nome do usuario invalido; logon negado >> >> >> Advertencia: Voce n?o esta mais conectado ao ORACLE. >> SYSTEM@O10GR2::SQL>connect scott/1 >> ERROR: >> ORA-01017: senha/nome do usuario invalido; logon negado >> >> >> SYSTEM@O10GR2::SQL>connect scott/1 >> ERROR: >> ORA-01017: senha/nome do usuario invalido; logon negado >> >> >> SYSTEM@O10GR2::SQL>connect scott/1 >> ERROR: >> ORA-01017: senha/nome do usuario invalido; logon negado >> >> >> SYSTEM@O10GR2::SQL>connect scott/1 >> ERROR: >> ORA-01017: senha/nome do usuario invalido; logon negado >> >> >> SYSTEM@O10GR2::SQL>connect scott/1 >> ERROR: >> ORA-01017: senha/nome do usuario invalido; logon negado >> >> >> SYSTEM@O10GR2::SQL>connect scott/1 >> ERROR: >> ORA-01017: senha/nome do usuario invalido; logon negado >> >> >> SYSTEM@O10GR2::SQL>connect scott/1 >> ERROR: >> ORA-01017: senha/nome do usuario invalido; logon negado >> >> >> SYSTEM@O10GR2::SQL>connect scott/1 >> ERROR: >> ORA-01017: senha/nome do usuario invalido; logon negado >> >> >> SYSTEM@O10GR2::SQL>connect scott/1 >> ERROR: >> ORA-01017: senha/nome do usuario invalido; logon negado >> >> >> SYSTEM@O10GR2::SQL>connect scott/1 >> ERROR: >> ORA-28000: a conta esta bloqueada >> >> >> SYSTEM@O10GR2::SQL>conn system/oracle >> Conectado. >> >> ==> Eis o importante : note que AMBOS estão REALMENTE no profile DEFAULT, >> acima nós vimos que o default NÃO FOI ALTERADO, ambos estão como LOCKED (e >> não LOCKED (TIME), NÃO possuem EXPIRY_TIME (pois estão LOCKED apenas, não >> foram expirados) e que USER_TESTE (que foi lockado diretamente) ** possui ** >> LOCK_DATE, enquanto SCOTT (que bloqueou por regra) não contém LOCK_DATE ..... >> >> SYSTEM@O10GR2::SQL>select * from dba_users; >> >> USERNAME ACCOUNT_STATUS LOCK_DATE >> EXPIRY_DATE PROFILE >> ---------------- -------------------------------- ------------------- >> ------------------- ------------ >> SYSTEM OPEN >> DEFAULT >> SYS OPEN >> DEFAULT >> MGMT_VIEW OPEN >> DEFAULT >> SYSMAN OPEN >> DEFAULT >> DBSNMP OPEN >> DEFAULT >> HR OPEN >> DEFAULT >> OE OPEN >> DEFAULT >> SH OPEN >> DEFAULT >> IX OPEN >> DEFAULT >> PM OPEN >> DEFAULT >> BI OPEN >> DEFAULT >> TESTE11G OPEN >> DEFAULT >> OPS$JCHIAPPA OPEN >> DEFAULT >> USER_TESTE LOCKED 11/09/2012 13:48:54 >> DEFAULT >> SCOTT LOCKED >> DEFAULT >> OUTLN EXPIRED & LOCKED 01/03/2012 14:45:14 >> 01/03/2012 14:45:14 DEFAULT >> WKSYS EXPIRED & LOCKED 01/03/2012 14:45:14 >> 01/03/2012 14:45:14 WKSYS_PROF >> OLAPSYS EXPIRED & LOCKED 01/03/2012 14:45:14 >> 01/03/2012 14:45:14 DEFAULT >> ORDPLUGINS EXPIRED & LOCKED 01/03/2012 14:45:14 >> 01/03/2012 14:45:14 DEFAULT >> WKPROXY EXPIRED & LOCKED 01/03/2012 14:45:14 >> 01/03/2012 14:45:14 DEFAULT >> XDB EXPIRED & LOCKED 01/03/2012 14:45:14 >> 01/03/2012 14:45:14 DEFAULT >> ANONYMOUS EXPIRED & LOCKED 01/03/2012 14:45:14 >> 01/03/2012 14:45:14 DEFAULT >> CTXSYS EXPIRED & LOCKED 01/03/2012 14:45:14 >> 01/03/2012 14:45:14 DEFAULT >> WK_TEST EXPIRED & LOCKED 01/03/2012 14:45:14 >> 01/03/2012 14:45:14 DEFAULT >> WMSYS EXPIRED & LOCKED 01/03/2012 14:45:14 >> 01/03/2012 14:45:14 DEFAULT >> DMSYS EXPIRED & LOCKED 01/03/2012 14:45:14 >> 01/03/2012 14:45:14 DEFAULT >> EXFSYS EXPIRED & LOCKED 01/03/2012 14:45:14 >> 01/03/2012 14:45:14 DEFAULT >> ORDSYS EXPIRED & LOCKED 01/03/2012 14:45:14 >> 01/03/2012 14:45:14 DEFAULT >> MDSYS EXPIRED & LOCKED 01/03/2012 14:45:14 >> 01/03/2012 14:45:14 DEFAULT >> DIP EXPIRED & LOCKED 01/03/2012 14:12:48 >> DEFAULT >> MDDATA EXPIRED & LOCKED 01/03/2012 14:45:14 >> 01/03/2012 14:45:14 DEFAULT >> TSMSYS EXPIRED & LOCKED 01/03/2012 14:45:14 >> 01/03/2012 14:45:14 DEFAULT >> ORACLE_OCM EXPIRED & LOCKED 01/03/2012 14:45:14 >> 01/03/2012 14:45:14 DEFAULT >> >> 34 linhas selecionadas. >> >> SYSTEM@O10GR2::SQL> >> >> ====> SE o database em questão tá apresentando status diferentes, PLZ veja >> os seus profiles E as colunas da DBA_USER , pois algo Não Está batendo aí >> .... >> >> []s >> >> Chiappa >> >> >> --- Em oracle_br@yahoogrupos.com.br, Paulo Couto <paulopcto2002@...> escreveu >>> >>> Amigos, algumas vezes nestes últimos meses alguns usuários reclamam que não >>> acessam uma aplicação e quando vou verificar a conta no banco do dito >>> sistema está bloqueada com o status de LOCKED (TIMED). >>> Todas as contas estão usando o profile default e apenas o parametro "FAILED >>> LOGIN ATTEMPTS" está setado para 10, o restante está como UNLIMITED. >>> Gostaria de saber como descobrir a causa destes bloqueios e o que causa >>> este tipo de bloqueio. >>> >>> Estamos utilizando o Oracle 10g patch 10.2.0.4 em windows server 2003. >>> >>> Att, >>> >>> Paulo. >>> >>> [As partes desta mensagem que não continham texto foram removidas] >>> >> >> >> >> >> ------------------------------------ >> >> -------------------------------------------------------------------------------------------------------------------------- >>>Atenção! As mensagens do grupo ORACLE_BR são de acesso público e de inteira >>>responsabilidade de seus remetentes. >> Acesse: http://www.mail-archive.com/oracle_br@yahoogrupos.com.br/ >> -------------------------------------------------------------------------------------------------------------------------- >>>Apostilas » Dicas e Exemplos » Função » Mundo Oracle » Package » Procedure » >>>Scripts » Tutoriais - O GRUPO ORACLE_BR TEM SEU PROPRIO ESPAÇO! VISITE: >>>http://www.oraclebr.com.br/ >> ------------------------------------------------------------------------------------------------------------------------ >> Links do Yahoo! Grupos >> >> > > > > -- > Ivan Ricardo Schuster > OCP 10g/11g > OCE RAC 10g/Linux -- Ivan Ricardo Schuster OCP 10g/11g OCE RAC 10g/Linux
