1.场景:采用zhangsan账户登录库,提示账户被锁无法登陆

[oracle@dbstandby07 ~]$ sqlplus  zhangsan/pass123321

SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 10 15:07:46 2024

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-28000: the account is locked

解决办法：采用系统账户登录oracle库

[oracle@dbprimary07 ~]$  sqlplus  /  as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 10 15:15:01 2024

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
##执行下面的sql 解除锁,由于忘记原来登录账户的密码，于是重新设置新的密码

SQL>  ALTER USER zhangsan ACCOUNT UNLOCK;

User altered.
SQL> ALTER USER zhangsan IDENTIFIED BY pass123321;

#验证结果

[oracle@dbprimary07 etc]$ sqlplus  zhangsan/pass123321

SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 10 15:16:31 2024
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
#查看当前登录用户:
SQL> show user;
USER is "ZHANGSAN"

SQL> set line 200 pages 200;
#查看当前登录用户下的表:
SQL> select * from employees;

EMPLOYEE_ID FIRST_NAME
----------- ------------------------------------------------------------------------------------------------------------------------------------------------------
LAST_NAME                                                                                                                                              HIRE_DATE           SALARY
------------------------------------------------------------------------------------------------------------------------------------------------------ --------------- ----------
          1 John
Doe                                                                                                                                                    01-JAN-21                1

SQL> 

也可以采用类似MySQL命令行免交互方式查看Oracle11g中的信息

sqlplus zhangsan/pass123321 <<EOF     
SET PAGESIZE 0 FEEDBACK OFF HEADING OFF ECHO OFF
show user;              
EXIT;
EOF

[oracle@dbprimary07 etc]$ sqlplus zhangsan/pass123321 <<EOF     
SET PAGESIZE 0 FEEDBACK OFF HEADING OFF ECHO OFF
show user;              
EXIT;
EOF


SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 10 16:03:33 2024

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

*SQL> SQL> USER is "ZHANGSAN"*
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@dbprimary07 etc]$ 

2.补充个关于oracle11g的密码策略知识点

Oracle 11g中账户密码默认不会被锁定。但是，如果尝试登录失败次数超过了数据库的默认限制，账户可能会被锁定。这个限制可以通过查询PROFILE来确定。 可以通过以下步骤查询账户密码失败次数和账户是否被锁定： 登录到Oracle数据库。 查询DBA_PROFILES视图来找到账户的FAILED_LOGIN_ATTEMPTS和PASSWORD_LOCK_TIME参数。

SELECT * FROM DBA_PROFILES WHERE PROFILE = 'DEFAULT' AND RESOURCE_NAME = 'FAILED_LOGIN_ATTEMPTS';
SELECT * FROM DBA_PROFILES WHERE PROFILE = 'DEFAULT' AND RESOURCE_NAME = 'PASSWORD_LOCK_TIME';

SQL> set line 200

SQL> SELECT * FROM DBA_PROFILES WHERE PROFILE = 'DEFAULT' AND RESOURCE_NAME = 'FAILED_LOGIN_ATTEMPTS';

PROFILE                                                                                    RESOURCE_NAME
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------
RESOURCE_TYPE            LIMIT
------------------------ ------------------------------------------------------------------------------------------------------------------------
DEFAULT                                                                                    FAILED_LOGIN_ATTEMPTS
PASSWORD                 10


SQL> SELECT * FROM DBA_PROFILES WHERE PROFILE = 'DEFAULT' AND RESOURCE_NAME = 'PASSWORD_LOCK_TIME';

PROFILE                                                                                    RESOURCE_NAME
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------
RESOURCE_TYPE            LIMIT
------------------------ ------------------------------------------------------------------------------------------------------------------------
DEFAULT                                                                                    PASSWORD_LOCK_TIME
PASSWORD                 1

#FAILED_LOGIN_ATTEMPTS 显示了在账户被锁定之前允许的失败登录尝试次数默认为10次。

#PASSWORD_LOCK_TIME 显示了账户被锁定的时间默认为1天（天数）。

如果你想要修改这些参数，你可以使用ALTER PROFILE命令

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 10;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LOCK_TIME 1;

特别提示： Oracle 11g 默认会将 DEFAULT 的 PROFILE 设置登录失败尝试次数（10 次）。这样在无意或恶意的连续使用错误密码连接时，导致数据库用户被锁住，影响业务。因此需要将登录失败尝试次数设为不限制

alter profile "DEFAULT" limit PASSWORD_GRACE_TIME UNLIMITED;
alter profile "DEFAULT" limit PASSWORD_LIFE_TIME UNLIMITED;
alter profile "DEFAULT" limit PASSWORD_LOCK_TIME UNLIMITED;
alter profile "DEFAULT" limit FAILED_LOGIN_ATTEMPTS UNLIMITED;