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

Environment: Oracle 11.2.0.4

Customer requirements: 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 password to the same as the user name, customers expect the password setting not to be too simple. The starting code is the same or similar to the user name.

1. Official solution

In fact, Oracle provides a very useful security check function to increase the complexity of users' passwords. This in the previous article "Oracle 11g security reinforcement" in the "1.8. database password security check function" chapter already has the exact solution, the core content is as follows:

Select limit from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_VERIFY_FUNCTION';prompt = = prompt = = 8. The database password security check function prompt = = prompt executes the script @? / rdbms/admin/utlpwdmg.sql select limit from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_VERIFY_FUNCTION';2 that creates the security check function. Abridged version solution

The above built-in security check function is too strict for checking, but the customer's current requirement is only one, and the password is not allowed to be exactly the same or too similar to the user name. So I found this requirement in this script and removed all the other parts that I didn't need for the time being. In this way, you get the following abridged version of the script:

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, 2013, Oracle and/or its affiliates. Rem All rights reserved. RemRem NAMERem utlpwdmg.sql-script for Default Password Resource LimitsRemRem DESCRIPTIONRem This is a script 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 that the customerRem can use to develop the function for actual complexity checks that the Rem customer wants to make on the new password.RemRem MODIFIED (MM/DD/YY) Rem skayoor 01 Accord 17 Improve check for username=password 13-Backport skayoor_bug-14671375 from mainRem asurpur 05 Universe 30 Improve check for username=password 06-fix-5246666 beef up password complexity check Rem nireland 08 Greater Improve check for username=password. # 1390553Rem nireland 06Compact 28Compact 00-Fix null old password test. # 1341892Rem asurpur 04 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 17 97-Fix for bug479763Rem asurpur 12-12-96-Changing the name of password_verify_functionRem asurpur 05 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 30 password not same as the-- username 96-Newscript for default password managementRem asurpur 05 30 Charley 96-CreatedRem-- This script sets the default password resource parameters-- This script needs to be 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 not same as the-- username Etc. The user may enhance this function according to-- the need.-- This function must be created in SYS schema.-- connect sys/ 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 username'); 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 Management-- This means that all the users on the system have Password Management-- enabled and set to the following values unless another profile is-- created with parameter values set to different value or UNLIMITED-- is created and assigned to the user.ALTER PROFILE DEFAULT LIMITPASSWORD_LIFE_TIME 180PASSWORD_VERIFY_FUNCTION verify_function_11G_WJZYY

Let's put this script, following the previous naming of Oracle, as utlpwdmg1.sql, and put it in the same path.

In this way, we can create the check function by executing the script:

3. Test and verification scheme

Test the abridged version of the script above and verify that the function is implemented:

-- execute script to create check function @? / rdbms/admin/utlpwdmg1.sql-- confirm successful execution select limit from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_VERIFY_FUNCTION';-- modify PASSWORD_LIFE_TIME to 30 (optional) ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 30 poster-query dba_profiles content select * from dba_profiles order by 1 -- query user status and expiration time 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 will fail:

-- password is the same as user name. Failed to modify: SYS@jyzhao1 > 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-- password is similar to user name. Failed to modify: SYS@jyzhao1 > 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-- password is not consistent with user name. Modified successfully: SYS@jyzhao1 > alter user jingyu identified by alfred;User altered.4. The last login time of the user

11g audit is enabled by default, and the last login time of the user can be found in the aud$ table:

-- query database time zone select property_value from database_properties where property_name='DBTIMEZONE';-- query aud$ table 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

Example of the result:

SYS@jyzhao1 > 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$NULLSYS@jyzhao1 >

If the above query result LAST_LOGIN is empty, the login information of the user is not recorded in the audit.

Summary

The above is the strategy to improve the password security of Oracle users introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply you in time. Thank you very much for your support to the website!

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.