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

Database security has always been the focus of people's attention. we know that if the database of an enterprise or institution is attacked by hackers, and these databases hold very important data, such as banks, communications and other databases, the consequences will be unimaginable. Oracle database uses a variety of means to ensure the security of the database, such as passwords, roles, permissions, and so on, today we come to elaborate on the password of oracle, of course, what we are going to talk about in detail today is not how powerful the security password mechanism of oracle is, etc., on the contrary, we need to explain how to make the password valid again without changing the password after the expiration of the oracle password.

Before the introduction, let's talk about a case in which a customer database does security reinforcement and modifies part of the security mechanism of password for profile. The most important point is that the parameter PASSWORD_LIFE_TIME (this parameter sets the password expiration time) is set, and when this parameter is set, the customer does not specify a good manual password periodic management policy according to the set security mechanism. With the expiration of the time set by the PASSWORD_LIFE_TIME parameter, the database will locked the user, causing the business to fail to connect normally. Theoretically, since the password has expired, resetting the password is the only means, but to a certain extent, resetting the password means a large number of middleware needs to be modified, which is still an inevitable risk for people who are not familiar with the business logic. After checking, it is found that the customer has not set PASSWORD_REUSE_TIME (this parameter is set to the same password reuse time). Since this parameter is not set, we can consider using a temporary password as the intermediate password and further reset the original password through the intermediate password. But at this time another problem arises, the customer does not know the user password of the business. This has caused trouble for the solution of the problem to a certain extent. This section uses a more ingenious way to reset the password of oracle.

N concept popularization

In the case of a detailed description of the contents of this section, we need to popularize some small knowledge points. Oracle manages the password validity and other issues through profile files. And default a profile file of default, in oracle 9i and previous versions, oracle is UNLIMITED for the default parameter value of default profile file, in 10g version, the default value of FAILED_LOGIN_ATTEMPTS is set to 10 times, that is to say, after entering the wrong password for 10 times in a row, oracle will lock the user until the user is unlocked. Since 11g, oracle's management policy for password files has increased a lot, many of which have been set up before, and the corresponding values have been defined in 11g. Although this new feature adds the security mechanism of oracle passwords, it also has an impact on our management to a certain extent. First of all, let's explain the password section of oracle's profile. (the default profile is taken from the oracle11g environment)

SQL > select * from dba_profiles where profile='DEFAULT' and RESOURCE_NAME like 'PASSWORD_%'

PROFILE RESOURCE_NAME RESOURCE LIMIT

DEFAULT PASSWORD_LIFE_TIME PASSWORD 180

DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED

DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED

DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL

DEFAULT PASSWORD_LOCK_TIME PASSWORD 1

DEFAULT PASSWORD_GRACE_TIME PASSWORD 7

Explain the above parameter values in detail:

PASSWORD_LIFE_TIME 180-the life cycle of the password. After this period, the password may expire automatically, depending on whether PASSWORD_GRACE_TIME is set.

PASSWORD_GRACE_TIME 7-then the PASSWORD_LIFE_TIME feature, if the PASSWORD_LIFE_TIME deadline has expired, then the setting of PASSWORD_GRACE_TIME is a grace (grace or extension) of the password life cycle, the number of days after the password expires, during which time if we log in to the system, there will be a prompt that the system will expire within a few days

PASSWORD_REUSE_TIME UNLIMITED-this feature limits the number of days for which passwords cannot be reused. The default value is UNLIMITED.

PASSWORD_REUSE_MAX UNLIMITED-this feature is specific to PASSWORD_REUSE_TIME, indicating that in order to reuse the current password within the time specified by the parameter PASSWORD_REUSE_TIME, at least the number of times the password needs to be changed (of course, the modified password must be different from the current password, because, after all, there are restrictions on the PASSWORD_REUSE_TIME feature)

FAILED_LOGIN_ATTEMPTS 10-this is easy to understand. If you don't know the password, the number of times you try to log in, after which the account is automatically locked.

PASSWORD_LOCK_TIME 1-followed by the FAILED_LOGIN_ATTEMPTS parameter, the time when the password is automatically locked, after which the system automatically unlocks the account the next time you log in

These are some of the parameters explained by oracle for password management in profile.

Next, let's explain the status of user locking in oracle.

SQL > select username,account_status,profile from dba_users

USERNAME ACCOUNT_STATUS PROFILE

-

SYSTEM OPEN DEFAULT

SYS OPEN DEFAULT

TEST3 OPEN DEFAULT

SCOTT OPEN DEFAULT

TEST2 EXPIRED (GRACE) PROFILE2

TEST EXPIRED (GRACE) DEFAULT

MGMT_VIEW EXPIRED & LOCKED DEFAULT

ORACLE database users have a variety of states and can view the view USER_ASTATUS_MAP.

SQL > select * from user_astatus_map

STATUS# STATUS

0 OPEN

1 EXPIRED

2 EXPIRED (GRACE)

4 LOCKED (TIMED)

8 LOCKED

5 EXPIRED & LOCKED (TIMED)

6 EXPIRED (GRACE) & LOCKED (TIMED)

9 EXPIRED & LOCKED

10 EXPIRED (GRACE) & LOCKED

You can see that oracle provides a total of nine states, and the nine states can be divided into two categories: 1. Basic state; 2. Combination status.

The first five are basic states: 0 OPEN, 1 EXPIRED, 2 EXPIRED (GRACE), 4 LOCKED (TIMED), 8 LOCKED.

The last four are basic states: 5 EXPIRED & LOCKED (TIMED), 6 EXPIRED (GRACE) & LOCKED (TIMED), 9 EXPIRED & LOCKED, 10 EXPIRED (GRACE) & LOCKED.

Two combinations of the latter four states can be obtained through the status number STATUS#. For our normal management, we only need to master the first five. The problems encountered by the above customers are due to the password invalidation caused by the setting of profile.

Skillfully solve the expiration of password

In the above customer cases, the security reinforcement measures are good, but they do not objectively consider that later password maintenance is a potential problem, and PASSWORD_LIFE_TIME parameters in oracle11G will also cause problems for the above customers to a large extent. If DBA is not clear about this feature, it is easy to cause password locking, and how to solve this problem becomes a big problem.

In 10g or 11g environment, if the password parameter of profiles is set, it will cause the password to expire within a specified period of time, lock and so on. At this time, if we continue to connect, if the status changes to EXPIRED or EXPIRED (GRACE), then when we connect, we will be prompted to reset the new password, and the session cannot connect to the database. At this time, if we know the user's password, DBA only needs to manually intervene and reset the password.

In the 10G environment, we take a closer look at the view of dba_users, and the corresponding PASSWORD field is actually the hash value of the password we set. When our password expires or the user is locked, we can skillfully circumvent this feature through this field.

View user information (10G version)

SQL > select username,account_status,password from dba_users where username like 'TEST%'

USERNAME ACCOUNT_STATUS PASSWORD

TEST2 OPEN 3C0731F39486287E

TEST1 OPEN C04FB3810DDE34AE

We can see that the above password is encrypted and displayed as an unordered string of hash values. Starting from 11G, in order to highlight password security, oracle will no longer display the column password in dba_users.

View user information (11G version)

SQL > select username,account_status,password from dba_users where username like'% TEST%'

USERNAME ACCOUNT_STATUS PASSWORD

TEST OPEN

TESTYING3 OPEN

TEST2 EXPIRED

TEST3 OPEN

6 rows selected.

As you can see, starting with 11G, oracle hides the password column.

Note: the enhancement of Oracle11g in user security is not only the hiding of passwords, but also includes

1. Password is case sensitive, initialization parameter sec_case_sensitive_logon

two。 Password complexity check, create the complexity check function verify_function_11G through utlpwdmg.sql file

3. Hash encryption algorithm with higher strength

When our user password expires and is locked, logging in again will result in an error: the user is locked

The following users:

SQL > select username,account_status,password,profile from dba_users where username='MDSYS'

USERNAME ACCOUNT_STATUS PASSWORD PROFIL

MDSYS EXPIRED & LOCKED 72979A94BAD2AF80 DEFAULT

SQL > select username,account_status,password,profile from dba_users where username='TEST1'

USERNAME ACCOUNT_STATUS PASSWORD PROFILE

TEST1 LOCKED C04FB3810DDE34AE DEFAULT

Note that LOCKED and EXPIRED & LOCKED are two different concepts. For LOCKED status, it is due to the number of consecutive misentered passwords that reach the number of times specified by FAILED_LOGIN_ATTEMPTS. For this kind of failure, we only need to simply unlock the user, as follows:

SQL > alter user test1 account unlock

User altered.

SQL > select username,account_status,password,profile from dba_users where username='TEST1'

USERNAME ACCOUNT_STATUS PASSWORD PROFILE

TEST1 OPEN C04FB3810DDE34AE DEFAULT

However, for the EXPIRED & LOCKED state, this is due to the locking caused by the expiration of the user's password caused by the PASSWORD_LIFE_TIME parameter. A single unlock command cannot solve this problem, and the password modification problem caused by the PASSWORD_LIFE_TIME parameter is also involved here. As follows:

SQL > select username,account_status,password,profile from dba_users where username='MDSYS'

USERNAME ACCOUNT_STATUS PASSWORD PROFILE

MDSYS EXPIRED & LOCKED 72979A94BAD2AF80 DEFAULT

SQL > conn mdsys/mdsys

ERROR:

ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.

Unlock the user:

SQL > conn / as sysdba

Connected.

SQL > alter user dmsys account unlock

User altered.

SQL > conn dmsys/dmsys

ERROR:

ORA-28001: the password has expired

Changing password for dmsys

New password:

Prompt to enter a new password

At this point, we check the user status:

SQL > select username,account_status,password,profile from dba_users where username='MDSYS'

USERNAME ACCOUNT_STATUS PASSWORD PROFILE

--

MDSYS EXPIRED 72979A94BAD2AF80 DEFAULT

We can see from the above experiment that although we unlock the user, the user's status only changes from EXPIRED & LOCKED to EXPIRED, and there is no normal OPEN, and the user is prompted for a new password from the new connection.

There is a problem here. You can imagine that when we are prompted for a new password, we must enter the original password of the production user, otherwise it will cause the password of the business middleware to be inconsistent with the modified password. If we do not know the original password at this time, it is bound to cause some trouble. At this point we need the password field in the dba_users view. Although the Password field has been encrypted by oracle's hash operation (the oracle password is encrypted by the combination of user name and password), we don't need to know what the password is, we just need to use the hash value of this field to successfully unlock the user.

For a user to assign a new password, I believe we all know very well:

Alter user username identified by password

Then we can use the hash value of password to cleverly unlock, as follows:

SQL > alter user dmsys identified by values' BFBA5A553FD9E28A'

User altered.

SQL > select username,account_status,password,profile from dba_users where username='MDSYS'

USERNAME ACCOUNT_STATUS PASSWORD PROFILE

--

MDSYS OPEN 72979A94BAD2AF80 DEFAULT

SQL > conn dmsys/dmsys

Connected.

SQL >

As you can see, although we don't know the user's password, we can reset the password through the hash value of password. In 11G, in order to improve security performance, oracle does not display the values in DBA_USERS.password and defaults to empty. As follows:

SQL > select username,account_status,password from dba_users

USERNAME ACCOUNT_STATUS PASSWORD

SYS OPEN

WMSYS OPEN

TESTYING3 OPEN

TESTYING OPEN

In 11G environment, we can get this value by querying in the USER$ base table, as follows:

SQL > select USER#,name,PASSWORD from user$ where name like 'TEST%'

USER# NAME PASSWORD

85 TEST 48724AE7C369325F

86 TEST2 3C0731F39486287E

87 TEST3 47B23A1E17F2D107

6 rows selected.

Using the same commands and methods, we can unlock user locks caused by expired passwords.

Technical conclusion

Through the above methods, we can cleverly unlock the user lock caused by the expiration of the password without knowing the user name and password, although we unlock the user through the hash value in the above method, however, no matter in terms of security or the continuous and stable operation of the database, we recommend that users adopt a secure and reasonable password management mechanism. To put an end to all possible hidden dangers is what a DBA must do to ensure the security of the database while maintaining the normal and stable operation of the database.

Original blog address: http://blog.itpub.net/23732248/

Original author: Ying Feng (frank-ying)

-

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.