ORA-28000 the account is locked error Simulation

Source: Internet
Author: User

ORA-28000 the account is locked error Simulation

The error message is as follows:
OCI-Call Error SQL code 28000, the account is locked
SQL>! Oerr ora 28000.
28000,000 00, "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
Note:
FAILED_LOGIN_ATTEMPTS = 10 the number of failed login attempts is 10. After 10 attempts, the user will be locked.
PASSWORD_LOCK_TIME = 15 after the specified number of logon attempts is 10, the user will be locked for 15 days.
Target: Change the account hjj from OPEN to LOCKED.
The following code simulates the 28000 error.
1. view the current environment
SQL> select username, account_status, profile from dba_users where username = 'hjj ';

USERNAME ACCOUNT_STATUS PROFILE
--------------------------------------------------------------------------------------------
HJJ OPEN DEFAULT
The account hjj is OPEN
SQL> select * from dba_profiles;

PROFILE RESOURCE_NAME RESOURCE LIMIT
--------------------------------------------------------------------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED
The default logon attempt is 10 times, and the account is locked.
To test the function, change FAILED_LOGIN_ATTEMPTS to 3.
SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS 3;

Profile altered.
SQL> select * from dba_profiles where resource_name in ('failed _ LOGIN_ATTEMPTS ') and profile = 'default ';

PROFILE RESOURCE_NAME RESOURCE LIMIT
--------------------------------------------------------------------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 3
2. login Test
Intentionally incorrect password three times to check whether the account hjj will be locked.
[Oracle @ ora10g ~] $ Sqlplus hjj/hjj

SQL * Plus: Release 10.2.0.5.0-Production on Mon Nov 3 21:36:00 2014

Copyright (c) 1982,201 0, Oracle. All Rights Reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL * Plus
[Oracle @ ora10g ~] $ Sqlplus hjj/hjj

SQL * Plus: Release 10.2.0.5.0-Production on Mon Nov 3 21:36:04 2014

Copyright (c) 1982,201 0, Oracle. All Rights Reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL * Plus
[Oracle @ ora10g ~] $
[Oracle @ ora10g ~] $ Sqlplus hjj/hjj

SQL * Plus: Release 10.2.0.5.0-Production on Mon Nov 3 21:36:08 2014

Copyright (c) 1982,201 0, Oracle. All Rights Reserved.

ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL * Plus
Check the account status again
SQL> select username, account_status, profile from dba_users where username = 'hjj ';

USERNAME ACCOUNT_STATUS PROFILE
--------------------------------------------------------------------------------------------
HJJ LOCKED DEFAULT
The account is locked.
3. You can log on to a common account only when the database is in the OPEN state, and the sys user can also use OS authentication when the database is closed.
Test whether the account hjj is locked after three failed attempts to log on to the database. Unlock the user first.
SQL> alter user hjj account unlock;

User altered.

SQL> select username, account_status, profile from dba_users where username = 'hjj ';

USERNAME ACCOUNT_STATUS PROFILE
--------------------------------------------------------------------------------------------
HJJ OPEN DEFAULT
[Oracle @ ora10g ~] $ Sqlplus/as sysdba

SQL * Plus: Release 10.2.0.5.0-Production on Mon Nov 3 21:42:43 2014

Copyright (c) 1982,201 0, Oracle. All Rights Reserved.


Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
[Oracle @ ora10g ~] $ Sqlplus hjj/hjj

SQL * Plus: Release 10.2.0.5.0-Production on Mon Nov 3 21:44:18 2014

Copyright (c) 1982,201 0, Oracle. All Rights Reserved.

ERROR:
A ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL * Plus
[Oracle @ ora10g ~] $ Sqlplus hjj/hjj

SQL * Plus: Release 10.2.0.5.0-Production on Mon Nov 3 21:44:22 2014

Copyright (c) 1982,201 0, Oracle. All Rights Reserved.

ERROR:
A ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL * Plus
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL * Plus
[Oracle @ ora10g ~] $ Sqlplus hjj/hjj

SQL * Plus: Release 10.2.0.5.0-Production on Mon Nov 3 21:44:24 2014

Copyright (c) 1982,201 0, Oracle. All Rights Reserved.

ERROR:
A ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL * Plus
[Oracle @ ora10g ~] $ Sqlplus/as sysdba

SQL * Plus: Release 10.2.0.5.0-Production on Mon Nov 3 21:44:33 2014

Copyright (c) 1982,201 0, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 503316480 bytes
Fixed Size 1274548 bytes
Variable Size 327159116 bytes
Database Buffers 171966464 bytes
Redo Buffers 2916352 bytes
Database mounted.
Database opened.
SQL> select username, account_status, profile from dba_users where username = 'hjj ';
USERNAME ACCOUNT_STATUS PROFILE
--------------------------------------------------------------------------------------------
HJJ OPEN DEFAULT
This proves that the database is not in the OPEN state and normal users cannot log on to the database. When Logging On, you must go to dba_users to check whether the user exists. If yes, you can determine whether the user name and password are correct; if the user cannot be found, the system prompts that the user does not exist.
4. How Does oracle record user login times?
The base table user $ in dba_users records the number of user logins. Let's look at the creation syntax of the user $ table.
[Oracle @ ora10g db_1] $ cd rdbms/admin/
[Oracle @ ora10g admin] $ ls-ltr SQL. bsq
-Rw-r -- 1 oracle oinstall 445473 Apr 2 2010 SQL. bsq
Create table user $
(User # number not null,
Name varchar2 ("M_IDEN") not null,
Type # number not null,
Password varchar2 ("M_IDEN "),
Datats # number not null,
Tempts # number not null,
Ctime date not null,
Ptime date,
Exptime date,
Ltime date,
Resource $ number not null,
Audit $ varchar2 ("S_OPFL "),
Defrole number not null,

Defgrp # number,
Defgrp_seq # number,
Astatus number default 0 not null,

Lcount number default 0 not null, --- number of failed login attempts.
Defschclass varchar2 ("M_IDEN "),
Ext_username varchar2 ("M_VCSZ "),
Spare1 number,
Spare2 number,
Spare3 number,
Spare4 varchar2 (1000 ),
Spare5 varchar2 (1000 ),
Spare6 date
)
SQL> select user #, name, ASTATUS, LCOUNT from user $ where name = 'hjj ';

USER # NAME ASTATUS LCOUNT
------------------------------------------------------------
55 HJJ 0 0
LCOUNT indicates the number of user logins (failures and successes)
[Oracle @ ora10g ~] $ Sqlplus hjj/hjj

SQL * Plus: Release 10.2.0.5.0-Production on Mon Nov 3 22:01:09 2014

Copyright (c) 1982,201 0, Oracle. All Rights Reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL * Plus
SQL>/

USER # NAME ASTATUS LCOUNT
------------------------------------------------------------
55 HJJ 0 1 -- login once
[Oracle @ ora10g ~] $ Sqlplus hjj/oracle

SQL * Plus: Release 10.2.0.5.0-Production on Mon Nov 3 22:03:35 2014

Copyright (c) 1982,201 0, Oracle. All Rights Reserved.

Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>/

USER # NAME ASTATUS LCOUNT
------------------------------------------------------------
55 HJJ 0 0
Once the logon succeeds within the range specified by FAILED_LOGIN_ATTEMPTS, LCOUNT is reset to 0.
If login fails three times, observe the value and status of LCOUNT.
SQL>/

USER # NAME ASTATUS LCOUNT
------------------------------------------------------------
55 HJJ 0 1

SQL>/

USER # NAME ASTATUS LCOUNT
------------------------------------------------------------
55 HJJ 0 2

SQL>/

USER # NAME ASTATUS LCOUNT
------------------------------------------------------------
55 HJJ 8 3
SQL> select username, account_status, profile from dba_users where username = 'hjj ';
USERNAME ACCOUNT_STATUS PROFILE
--------------------------------------------------------------------------------------------
HJJ LOCKED DEFAULT
Log On again with the correct password
[Oracle @ ora10g admin] $ sqlplus hjj/oracle

SQL * Plus: Release 10.2.0.5.0-Production on Mon Nov 3 22:10:17 2014

Copyright (c) 1982,201 0, Oracle. All Rights Reserved.

ERROR:
ORA-28000: the account is locked

5. Summary
Common users need to access the oracle view during login, at user $. lcount records the number of failed user logins. If lcount> = profile. FAILED_LOGIN_ATTEMPTS, the account is automatically locked, and the lock time is determined by PASSWORD_LOCK_TIME. Configure the parameters in profile for security consideration.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.