Tip: How to improve the security of your Oracle user passwords

Environment: Oracle 11.2.0.4
customer Demand: The main background is that there are many business user names in the database, and because some users lack security awareness, and even directly set their own password as the same as the user name, the current customer expected password settings not too simple, at least not with the user name consistent or similar.

• 1. Official Solutions
• 2. Abridged version of the solution
• 3. Test validation Scenarios
• 4. Last logon time of the user

1. Official Solutions

In fact, Oracle provides a very useful security check function to improve the complexity of user passwords. The "1.8. Database Password Security check function" section of the previous article, "Oracle 11g Security Hardening", has an exact solution, with the following core content:

select limit from dba_profiles where profile=‘DEFAULT‘ and resource_name=‘PASSWORD_VERIFY_FUNCTION‘;prompt =============================prompt == 8.数据库密码安全性校验函数 prompt =============================prompt 执行创建安全性校验函数的脚本@?/rdbms/admin/utlpwdmg.sql select limit from dba_profiles where profile=‘DEFAULT‘ and resource_name=‘PASSWORD_VERIFY_FUNCTION‘;

2. Abridged version of the solution

The above-mentioned security check function is too stringent for the inspection, and the customer needs only one, does not allow the password and user name exactly the same or too similar to do. So I'm going to find this requirement from this script and get rid of all the other temporary unwanted parts. In this way, you get the following script for the truncated version:

Remrem $Header: RDBMS/ADMIN/UTLPWDMG1.SQL/ST_RDBMS_11.2.0/1 2013/01/31 01:34:11 skayoor Exp $RemRem Utlpwdmg.sqlremrem Copyright (c) 2006, Oracle and/or its affiliates. Rem All rights reserved. Remrem namerem utlpwdmg.sql-script for Default Password Resource Limitsremrem Descriptionrem The is a s Cript for enabling the password management Featuresrem by setting the default password resource limits. Remrem Notesrem This file contains a function for minimum checking of passwordrem complexity. This is more of a sample function, the Customerrem can use to develop the function for actual complexity checks T Hat the Rem customer wants to make on the new password. Remrem MODIFIED (mm/dd/yy) Rem Skayoor 01/17/13-backport skayoor_bug-14671375 from Mainrem Asurpur 05/  30/06-fix-5246666 Beef up Password complexity check Rem nireland 08/31/00-improve check for Username=password. #1390553Rem Nireland 06/28/00 -Fix null old password test. #1341892Rem Asurpur 04/17/97-fix for Bug479763rem Asurpur 12/12/96-changing the name of Password_verify_  Functionrem Asurpur 05/30/96-new script for default password Managementrem Asurpur 05/30/96-createdrem-- This script sets the default password resource parameters--This script needs to is run to enable the password features.-  -however the default resource parameters can be changed based – on the need.--A default password complexity function is Also provided.--this function makes the minimum complexity checks like--the minimum length of the password, password no T same as the--username, etc. The user may enhance this function according to--the need.--this function must is created in SYS schema.--connect sys/& Lt;password> as SYSDBA before running the scriptcreate OR REPLACE FUNCTION verify_function_11g_wjzyy (username varchar2   , password varchar2, old_password varchar2) RETURN Boolean is n boolean; M inTeger;   differ integer;   IsDigit Boolean;   Ischar Boolean;   Ispunct Boolean;   Db_name VARCHAR2 (40);   Digitarray varchar2 (20);   Punctarray VARCHAR2 (25);   Chararray VARCHAR2 (52);   I_char VARCHAR2 (10);   Simple_password VARCHAR2 (10); Reverse_user VARCHAR2 (32);   BEGIN digitarray:= ' 0123456789 ';   chararray:= ' abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz ';     --Check If the password is same as the username or username (1-100) if nls_lower (password) = Nls_lower (username) Then   Raise_application_error ( -20002, ' Password same as or similar to user ');   END IF;      For i in 1..100 LOOP i_char: = To_char (i); If Nls_lower (username) | |      I_char = nls_lower (password) then raise_application_error ( -20005, ' password same as or similar to User name ');    END IF;       END LOOP; --everything is fine;      return TRUE; RETURN (TRUE); End;/grant EXECUTE on Verify_function_11g_wjzyy to public;--this script alters the default parameters for Password Manage ment--ThIs means, the users on the system has Password management--enabled and set to the following values unless anothe R Profile was--created with parameter values set to different value or UNLIMITED--was created and assigned to the USER.A Lter profile DEFAULT limitpassword_life_time 180password_verify_function verify_function_11g_wjzyy;

We will use this script in accordance with the previous Oracle naming method, name it utlpwdmg1.sql, and put it under the same path.
In this way, we can create this check function by executing this script:

3. Test validation Scenarios

Test the truncated version of the above script and verify that the feature is implemented:

--执行脚本创建校验函数@?/rdbms/admin/utlpwdmg1.sql--确认执行成功select limit from dba_profiles where profile=‘DEFAULT‘ and resource_name=‘PASSWORD_VERIFY_FUNCTION‘;--将PASSWORD_LIFE_TIME修改为30（选做）ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 30;--查询dba_profiles内容select * from dba_profiles order by 1;--查询用户状态和过期时间select USERNAME, PASSWORD, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE from dba_users;

The test user password cannot be the same or similar to the user name, otherwise the modification fails:

--密码与用户名一样，修改失败：[email protected] >alter user jingyu identified by jingyu;alter user jingyu identified by jingyu*ERROR at line 1:ORA-28003: password verification for the specified password failedORA-20002: Password same as or similar to user--密码与用户名相似，修改失败：[email protected] >alter user jingyu identified by jingyu1;alter user jingyu identified by jingyu1*ERROR at line 1:ORA-28003: password verification for the specified password failedORA-20005: Password same as or similar to user name--密码与用户名不一致，修改成功：[email protected] >alter user jingyu identified by alfred;User altered.

4. Last logon time of the user

11g auditing is turned on by default, and you can find the most recent login time from the aud$ table:

--查询数据库时区select property_value from database_properties where property_name=‘DBTIMEZONE‘;--查询aud$表select MAX(to_char(a.ntimestamp#, ‘YYYY-MM-DD HH24:MI:SS‘)) last_login,       u.username  from sys.aud$ a, dba_users u where a.USERID(+) = u.username   and u.user_id > 90 group by u.username ORDER BY 1;

Examples of results:

[email protected] >select MAX(to_char(a.ntimestamp#, ‘YYYY-MM-DD HH24:MI:SS‘)) last_login,  2         u.username  3    from sys.aud$ a, dba_users u  4   where a.USERID(+) = u.username  5     and u.user_id > 90  6   group by u.username  7   ORDER BY 1;LAST_LOGIN          USERNAME------------------- ------------------------------2018-04-17 07:16:46 JINGYU                    TESTTESTTEST                    XS$NULL[email protected] >

The result of the above query Last_login is empty user, is not logged in the audit of the user's login information.

Tip: How to improve the security of your Oracle user passwords