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

Starting with Oracle 11.1, logging in with an incorrect user name and password may result in significant "row cache lock" waiting at the database level.

Many users think that this is a bug, when in fact it is a database protection mechanism.

Oracle's sqlplus tool automatically disconnects after 3 incorrect passwords, but external applications can encode and constantly call login API to try the password. So without a database-level security control, it would be very dangerous.

Starting with Oracle 11.1, the database starts locking down the same user for 3 seconds after 3 incorrect password attempts before allowing the next login. This lock time will be gradually extended from 3 seconds and will continue to increase.

All session logged in with this user will wait for "row cache lock", even if he logs in with the correct password.

Many users do not understand that this is to help users avoid risks and complain about the "row cache lock" waiting they see.

So Oracle provides a method (event 28401) in the fix for Bug 7715339 to bypass this code for users to make different choices.

Event= "28401 trace name context forever, level 1" # disable logon delay

It must be noted that this is not really a bug, but a feature enhancement. Users must be aware that if this event is set, your database will be exposed to the risk of password guessing.

The fix for Bug 7715339 is included in 11.2.0.1 PSU. Patch 7715339 on 11.1.0.7 is equivalent to opening event 28401 by default.

After 11.2.0.2, Oracle modified the code to change the "row cache lock" wait to "library cache lock" wait.

To sum up:

1) on 11.1.0.X, the wrong username and password login will result in significant "row cache lock" waiting.

Users can patch 7715339 on 11.1.0.7 and bypass this security control code without setting event 28401.

2) on 11.2.0.1, the wrong username and password login will result in significant "row cache lock" waiting.

Users do not need to patch (because it is already included in 11.2.0.1), directly set event 28401, you can bypass this security control code.

3) versions above 11.2.0.2 (including 11.2.0.2), login with incorrect username and password will result in significant "library cache lock" waiting.

Users do not need to patch (because it is already included in 11.2.0.1), directly set event 28401, you can bypass this security control code.

Again, users must be aware that patching or setting this event will expose your database to the risk of password guessing.

Main topic:

There is feedback from users that even if event 28401 is set, it will be observed that the wrong username and password login causes "library cache lock" to wait. Why? To this end, we did the following tests to explain:

Start 10 processes, log in with the wrong username and password at the same time, and test that event 28401 is not set and event 28401 is set to compare. Observe and obtain the average waiting time from V$SYSTEM_EVENT many times:

Select total_waits,Time_waited_fg/total_waits

From V$SYSTEM_EVENT

Where event='library cache lock'

Event 28401 is not set:

91 1395.252747252747252747252747252747252747

98 2352.959183673469387755102040816326530612

106 2687.698113207547169811320754716981132075

116 3495.862068965517241379310344827586206897

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.