Shulou(Shulou.com)06/01 Report--

In Oracle 11g, in order to improve security, Oracle introduced a new feature of "password delayed authentication". The effect of this feature is that if the user enters the wrong password to try to log in, as the number of login errors increases, so does the authentication time before each login, thus mitigating password attempt attacks that may be repeated against the database.

However, for a normal system, due to the change of password, there may be some missing clients that try again and again, resulting in a long wait for Library Cache Lock in the database, which is very common.

If you encounter this type of problem, you can eliminate this effect by turning off this feature through Event 28401. The following command sets the modification in the parameter file:

ALTER SYSTEM SET EVENT =

'28401 TRACE NAME CONTEXT FOREVER, LEVEL 1' SCOPE = SPFILE

A very typical AWR report with such problems is as follows. First of all, in TOP 5, you may see significant Library Cache Lock waits. If you use sql to check the wait time, username is listed as empty. The following example comes from the reality of version 11.2.0.3.0:

In such cases, the following metrics are displayed in the time model-Time Model, where connection management call elapsed time occupies the main DB Time, and this wait directly indicates that it occurs when a database connection is established:

Such problems are common and certain in Oracle 11g, and the corresponding records can be found on MOS: High 'library cache lock' Wait Time Due to Invalid Login Attempts (1309738.1) in addition, Oracle 11g enables password case verification. If you upgrade from Oracle 10g, you need to pay special attention to this change. This feature can be controlled by initializing the parameter SEC_CASE_SENSITIVE_LOGON.

Here's a case: take other people's cases off the Internet.

1. The source of the problem encountered a problem before. After changing the user name and password, it was found that the login was hanged with the new password, and then the oa system of the whole company was completely paralyzed. For details, see the previous record.

I recently learned about password delay, a new feature of oracle11g, and realized that the problem was caused by password delay.

The general situation is: starting from oracle11g, if the user enters the wrong password to log in, the time waiting for authentication before each login will increase with the increase of the number of login errors. The original intention is to protect the database from consuming too many db resources when it is maliciously logged in, which leads to database server problems due to excessive consumption. But this also leads to problems, if you log in too much with the wrong password. It will affect the normal login of the user, that is, there is a delay in password verification so that you have to wait a long time to enter the correct password to log in. The experience for users is that the database hang resides (in fact, it is completely normal for you to use other users to operate the database)

2, the case demonstrates that the Oracle version is 11g branch 11.2.0.1.0:

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as timdba@A_VM128

SQL >

Set the time display:

SQL > set time on

07:41:57 SQL > conn timdba/timgood

Connected.

07:42:48 SQL > conn timdba/t;# starts trying to log in with the wrong password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

07:42:49 SQL > conn timdba/t; # it takes 1 second to log in incorrectly for the first time

ERROR:

ORA-01017: invalid username/password; logon denied

07:42:51 SQL > conn timdba/t; # the second wrong login takes 2 seconds

ERROR:

ORA-01017: invalid username/password; logon denied

07:42:52 SQL > conn timdba/t; # the third wrong login takes 1 second

ERROR:

ORA-01017: invalid username/password; logon denied

07:42:54 SQL > conn timdba/t; # the fourth incorrect login takes 2 seconds

ERROR:

ORA-01017: invalid username/password; logon denied

07:42:57 SQL > conn timdba/t; # the fifth incorrect login takes 3 seconds

ERROR:

ORA-01017: invalid username/password; logon denied

07:43:02 SQL > conn timdba/t; # the sixth wrong login takes 5 seconds

ERROR:

ORA-01017: invalid username/password; logon denied

07:43:07 SQL > conn timdba/t; # it takes 5 seconds to log in incorrectly for the seventh time

ERROR:

ORA-01017: invalid username/password; logon denied

07:43:13 SQL > conn timdba/t; # the 8th wrong login took 6 seconds

ERROR:

ORA-01017: invalid username/password; logon denied

07:43:20 SQL > conn timdba/t;# the 9th wrong login takes 7 seconds

ERROR:

ORA-01017: invalid username/password; logon denied

07:43:28 SQL >

07:43:29 SQL > conn timdba/timgood

Connected.

07:43:40 SQL >

You can see that for the fourth time and the fifth time, the wrong login verification time is getting longer and longer. Basically, the delay is one second more each time, and even if the correct password is entered later, it will be delayed by more than ten seconds.

During the test, once the correct password is entered and the verification is successful, the error delay will be cleared by 0, and the number will be recalculated from 0:

08:15:30 SQL > conn timdba/t

ERROR:

ORA-01017: invalid username/password; logon denied

08:15:34 SQL > conn timdba/timgood

Connected.

08:15:37 SQL > conn timdba/t

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

08:15:39 SQL > conn timdba/t

ERROR:

ORA-01017: invalid username/password; logon denied

08:15:40 SQL >

We further spread the thinking, this is only a single session to do the test, if it is an online environment, thousands of sessions come over, if the passwords are wrong, and delay together, according to an operation delay of one second, the delay is basically 1000 seconds, that is, half an hour where your login interface is stuck, so the experience for the customer is to enter the correct password, as a result, click the login button, and it gets stuck. Life and death do not move, the server is down, which means that the application system hang lives.

3, the new feature is that any new feature of the double-edged sword Oracle can bring performance improvement and further security guarantee, but after all, oracle is only a software software, if it is software, there will be bug, and even be exploited and attacked by others.

Oracle did not provide a complete on-screen password delay method in several small versions after 11g's release, but oracle has powerful other auxiliary features that can be dealt with by setting the event event.

4. By setting the Event screen password delay, it is usually sufficient to set 28401. If you encounter other special circumstances, you can also set it again. Next, set EVENTS 28401 to achieve masked password delay verification:

ALTER SYSTEM SET EVENT = '28401 TRACE NAMECONTEXT FOREVER, LEVEL 1' SCOPE = SPFILE

Alter system set event= "10949 TRACENAME CONTEXT FOREVER:28401 trace name context forever, level 1" scope=spfile

SQL > set time on

08:56:22 SQL > ALTER SYSTEM SET EVENT = '28401 TRACE NAME CONTEXT FOREVER, LEVEL 1' SCOPE = SPFILE

System altered.

08:56:27 SQL > create pfile from spfile

File created.

08:56:29 SQL >

Then restarting the oracle database takes effect.

08:56:44 SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

08:57:05 SQL > startup

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2217952 bytes

Variable Size 545261600 bytes

Database Buffers 281018368 bytes

Redo Buffers 6606848 bytes

Database mounted.

Database opened.

08:57:46 SQL >

Verify the wrong password again and delay verification, and you can see that there is almost no delay:

08:58:28 SQL > conn timdba/timgood

Connected.

08:58:33 SQL > conn timdba/t

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

08:58:37 SQL > conn timdba/t

ERROR:

ORA-01017: invalid username/password; logon denied

08:58:38 SQL > conn timdba/t

ERROR:

ORA-01017: invalid username/password; logon denied

08:58:39 SQL > conn timdba/t

ERROR:

ORA-01017: invalid username/password; logon denied

08:58:39 SQL > conn timdba/t

ERROR:

ORA-01017: invalid username/password; logon denied

08:58:40 SQL > conn timdba/t

ERROR:

ORA-01017: invalid username/password; logon denied

08:58:41 SQL > conn timdba/t

ERROR:

ORA-01017: invalid username/password; logon denied

08:58:42 SQL > conn timdba/t

ERROR:

ORA-01017: invalid username/password; logon denied

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.