提升Oracle用户密码安全性的策略

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' ;

Rem

Rem $Header: rdbms/admin/utlpwdmg1.sql /st_rdbms_11.2.0/1 2013/01/31 01:34:11 skayoor Exp $

Rem

Rem utlpwdmg.sql

Rem

Rem Copyright (c) 2006, 2013, Oracle and / or its affiliates.

Rem All rights reserved.

Rem

Rem NAME

Rem  utlpwdmg.sql - script for Default Password Resource Limits

Rem

Rem DESCRIPTION

Rem  This is a script for enabling the password management features

Rem  by setting the default password resource limits.

Rem

Rem NOTES

Rem  This file contains a function for minimum checking of password

Rem  complexity. This is more of a sample function that the customer

Rem  can use to develop the function for actual complexity checks that the

Rem  customer wants to make on the new password .

Rem

Rem MODIFIED (MM/DD/YY)

Rem skayoor  01/17/13 - Backport skayoor_bug-14671375 from main

Rem asurpur  05/30/06 - fix - 5246666 beef up password complexity check

Rem nireland 08/31/00 - Improve check for username= password . #1390553

Rem nireland 06/28/00 - Fix null old password test. #1341892

Rem asurpur  04/17/97 - Fix for bug479763

Rem asurpur  12/12/96 - Changing the name of password_verify_function

Rem asurpur  05/30/96 - New script for default password management

Rem asurpur  05/30/96 - Created

Rem

-- 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 script

CREATE 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 LIMIT

PASSWORD_LIFE_TIME 180

PASSWORD_VERIFY_FUNCTION verify_function_11G_WJZYY;

--执行脚本创建校验函数

@?/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;

--密码与用户名一样，修改失败：

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 failed

ORA-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 failed

ORA-20005: Password same as or similar to user name

--密码与用户名不一致，修改成功：

SYS@jyzhao1 > alter user jingyu identified by alfred;

User altered.

--查询数据库时区

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$ NULL

SYS@jyzhao1 >