環境：Oracle 11.2.0.4
客戶需求：主要背景是數據庫中有很多業務用戶名，且由于部分用戶缺乏安全意識，甚至直接將自己的密碼設置為和用戶名一樣，目前客戶期望密碼設置不要過于簡單，最起碼別和用戶名一致或相似就好。
實際上Oracle提供有一個非常好用的安全校驗函數，來提升用戶密碼的復雜性。這個在之前的文章《Oracle 11g 安全加固》中的“1.8.數據庫密碼安全性校驗函數”章節就已經有了確切的解決方案,核心內容如下：
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';
上面這個自帶的安全性校驗函數對檢查過于嚴苛，而客戶目前的需求就只有一個，不允許密碼和用戶名完全一樣或過于相似就可以了。于是乎，我就從這個腳本中找到這項需求，把其他暫時不需要的部分全部去掉。這樣，就得到了如下的刪減版腳本：
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/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 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/<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 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;
我們將這個腳本，遵守之前Oracle的命名方式，將其命名為utlpwdmg1.sql，放在同樣的路徑下。
這樣，我們執行這個腳本就可以創建這個校驗函數：
將上面的刪減版腳本進行測試并驗證功能是否實現：
--執行腳本創建校驗函數@?/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;
測試用戶密碼不能與用戶名相同或者相似，否則會修改失?。?/p>
--密碼與用戶名一樣，修改失?。篠YS@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--密碼與用戶名相似，修改失敗：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--密碼與用戶名不一致，修改成功：SYS@jyzhao1 >alter user jingyu identified by alfred;User altered.
11g默認開啟了審計，從aud$表中可以查到用戶最近登錄的時間：
--查詢數據庫時區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;
結果示例：
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 >
上述查詢結果LAST_LOGIN為空的用戶，就是在審計中沒有記錄到該用戶的登錄信息。
總結
以上所述是小編給大家介紹的提升Oracle用戶密碼安全性的策略，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對VeVb武林網網站的支持！
新聞熱點
疑難解答
