select limit from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_VERIFY_FUNCTION';prompt =============================prompt == 8.數(shù)據(jù)庫密碼安全性校驗(yàn)函數(shù) prompt =============================prompt 執(zhí)行創(chuàng)建安全性校驗(yàn)函數(shù)的腳本@?/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;

--執(zhí)行腳本創(chuàng)建校驗(yàn)函數(shù)@?/rdbms/admin/utlpwdmg1.sql--確認(rèn)執(zhí)行成功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內(nèi)容select * from dba_profiles order by 1;--查詢用戶狀態(tài)和過期時(shí)間select USERNAME, PASSWORD, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE from dba_users;

--密碼與用戶名一樣，修改失敗：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--密碼與用戶名相似，修改失敗：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.

--查詢數(shù)據(jù)庫時(shí)區(qū)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 >