Oracle 19C 密码策略、默认密码验证规则、密码过期EXPIRED处理

一、密码有效期、连续登录失败尝试次数、密码重用限制、修改用户profile

查询 default profile 策略

select * from dba_profiles where profile='DEFAULT' and resource_type='PASSWORD';
其中：
PASSWORD_LIFE_TIME：         密码有效期(天)，默认 180 天；超过时间密码可能会过期，取决于是否设置了 PASSWORD_GRACE_TIME ;
PASSWORD_GRACE_TIME：        密码到期之后可以继续使用的天数，在这段时间内如果登录系统，会提示密码在几天内过期，默认7天;
PASSWORD_REUSE_TIME：        密码历史记录保留时间(天)，密码在多少天之后可以被重用，默认 UNLIMITED，同 PASSWORD_REUSE_MAX 搭配使用，参考后面 PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX ;
PASSWORD_REUSE_MAX：         密码历史记录保留次数，密码在修改多少次之后可以被重用，默认 UNLIMITED，同 PASSWORD_REUSE_TIME 搭配使用，参考后面 PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX ;
FAILED_LOGIN_ATTEMPTS：      最大连续登录失败次数，超过则锁定用户，默认 10 次 ;
PASSWORD_LOCK_TIME：         超过最大连续登录失败次数后用户锁定时间(天)，默认 1 天 ;
PASSWORD_VERIFY_FUNCTION：   密码复杂度验证函数(ora12c_verify_function 、ora12c_strong_verify_function 、ora12c_stig_verify_function) ;
INACTIVE_ACCOUNT_TIME:       账户在多少天不登录后锁定，最小15(天)，最大24855(天)，默认 UNLIMITED ;
PASSWORD_ROLLOVER_TIME：     （19C后）当该参数设置为非0限制就允许更改应用程序数据库用户的密码，同时允许旧密码在PASSWORD_ROLLOVER_TIME限定的时间内保持有效，在这期间应用程序可以使用旧密码或新密码连接到数据库。当轮转切换时间(password_rollover_time)到期时，就只能用新密码连接访问了

PASSWORD_REUSE_TIME、PASSWORD_REUSE_MAX：两者之间的规律：
 (1)PASSWORD_REUSE_MAX，PASSWORD_REUSE_TIME都为UNLIMITED，密码可以随意重用，没有任何限制。
 (2)PASSWORD_REUSE_MAX，PASSWORD_REUSE_TIME均为指定值时，必须满足这两者的条件时才可以重用密码。
 (3)PASSWORD_REUSE_MAX，PASSWORD_REUSE_TIME其中一个为UNLIMITED，另一个为指定值，则密码永远不能重用。

PASSWORD_ROLLOVER_TIME资源设置说明
1. 缺省设置为0表示禁用Password Rollover
2. 单位是天，最小值为1小时，比如把限制设置为4个小时：4/24
3. 最大值不能超过60天，也不能超过当前配置文件中的MIN(PASSWORD_GRACE_TIME，PASSWORD_LIFE_TIME)
4. 粒度如果是秒，例如(1/24) +(3/1440) +(5/86400)) 表示限制为1小时加3分5秒
详细参考：https://www.modb.pro/db/617605
复制

修改 default profile 语句：

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 90
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 5
FAILED_LOGIN_ATTEMPTS 30
PASSWORD_LOCK_TIME 1;
复制

新建密码不过期 profile DBAMGMT/C##DBAMGMT(For CDB and all PDBs)

-- CDB 环境
CREATE PROFILE C##DBAMGMT LIMIT
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX  UNLIMITED
FAILED_LOGIN_ATTEMPTS 30
PASSWORD_LOCK_TIME 1
INACTIVE_ACCOUNT_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION ora12c_verify_function
CONTAINER=ALL;

-- None-CDB 环境或者 只针对 PDB/CDB
CREATE PROFILE DBAMGMT LIMIT
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX  UNLIMITED
FAILED_LOGIN_ATTEMPTS 30
PASSWORD_LOCK_TIME 1
INACTIVE_ACCOUNT_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION ora12c_verify_function;
-- 保留 PASSWORD_LOCK_TIME 用以监控 account_status

-- 命令详解
CREATE PROFILE profile
  LIMIT { resource_parameters
        | password_parameters
        }...
  [ CONTAINER = { CURRENT | ALL } ] ;

resource_parameters::=
{ { SESSIONS_PER_USER
  | CPU_PER_SESSION
  | CPU_PER_CALL
  | CONNECT_TIME
  | IDLE_TIME
  | LOGICAL_READS_PER_SESSION
  | LOGICAL_READS_PER_CALL
  | COMPOSITE_LIMIT
  }
  { integer | UNLIMITED | DEFAULT }
| PRIVATE_SGA
  { size_clause | UNLIMITED | DEFAULT }
}

password_parameters::=
{ { FAILED_LOGIN_ATTEMPTS
  | PASSWORD_LIFE_TIME
  | PASSWORD_REUSE_TIME
  | PASSWORD_REUSE_MAX
  | PASSWORD_LOCK_TIME
  | PASSWORD_GRACE_TIME
  | INACTIVE_ACCOUNT_TIME
  }
  { expr | UNLIMITED | DEFAULT }
| PASSWORD_VERIFY_FUNCTION
  { function | NULL | DEFAULT }
| ( PASSWORD_ROLLOVER_TIME { expr | DEFAULT } )
}

profile

Specify the name of the profile to be created. The name must satisfy the requirements listed in "Database Object Naming Rules". Use profiles to limit the database resources available to a user for a single call or a single session.
In a non-CDB, a profile name cannot begin with C## or c##.
In a CDB, the requirements for a profile name are as follows:
The name of a common profile must begin with characters that are a case-insensitive match to the prefix specified by the COMMON_USER_PREFIX initialization parameter. By default, the prefix is C##.
The name of a local profile must not begin with characters that are a case-insensitive match to the prefix specified by the COMMON_USER_PREFIX initialization parameter. Regardless of the value of COMMON_USER_PREFIX, the name of a local profile can never begin with C## or c##.

resource_parameters

SESSIONS_PER_USER
Specify the number of concurrent sessions to which you want to limit the user.

CPU_PER_SESSION
Specify the CPU time limit for a session, expressed in hundredth of seconds.

CPU_PER_CALL
Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.

CONNECT_TIME
Specify the total elapsed time limit for a session, expressed in minutes.

IDLE_TIME
Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.
When you set an idle timeout of X minutes, note that the session will take a couple of additional minutes to be terminated.
On the client application side, the error message shows up only the next time, when the idle client attempts to issue a new command.

LOGICAL_READS_PER_SESSION
Specify the permitted number of data blocks read in a session, including blocks read from memory and disk.

LOGICAL_READS_PER_CALL
Specify the permitted number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).

PRIVATE_SGA
Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA). Refer to size_clause for information on that clause.

Note:This limit applies only if you are using shared server architecture. The private space for a session in the SGA includes private SQL and PL/SQL areas, but not shared SQL and PL/SQL areas.

COMPOSITE_LIMIT
Specify the total resource cost for a session, expressed in service units. Oracle Database calculates the total service units as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA.

See Also:
ALTER RESOURCE COST for information on how to specify the weight for each session resource
"Setting Profile Resource Limits: Example"


password_parameters

Use the following clauses to set password parameters. Parameters that set lengths of time—that is, all the password parameters except FAILED_LOGIN_ATTEMPTS and PASSWORD_REUSE_MAX—are interpreted in number of days. For testing purposes you can specify minutes (n/1440) or even seconds (n/86400) for these parameters. You can also use a decimal value for this purpose (for example .0833 for approximately one hour). The minimum value is 1 second. The maximum value is 24855 days. For FAILED_LOGIN_ATTEMPTS and PASSWORD_REUSE_MAX, you must specify an integer.

FAILED_LOGIN_ATTEMPTS
Specify the number of consecutive failed attempts to log in to the user account before the account is locked. If you omit this clause, then the default is 10 times.

PASSWORD_LIFE_TIME
Specify the number of days the same password can be used for authentication. If you also set a value for PASSWORD_GRACE_TIME, then the password expires if it is not changed within the grace period, and further connections are rejected. If you omit this clause, then the default is 180 days.
See Also:
Oracle Database Security Guide for information on setting PASSWORD_LIFE_TIME to a low value

PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX
These two parameters must be set in conjunction with each other. PASSWORD_REUSE_TIME specifies the number of days before which a password cannot be reused. PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused. For these parameter to have any effect, you must specify a value for both of them.
-- 这两个参数必须一起设置。PASSWORD_REUSE_TIME 指定密码不能重用的天数。PASSWORD_REUSE_MAX 指定在重用当前密码之前所需的密码更改次数。要使这些参数产生效果，必须为它们指定一个值。
If you specify a value for both of these parameters, then the user cannot reuse a password until the password has been changed the number of times specified for PASSWORD_REUSE_MAX during the number of days specified for PASSWORD_REUSE_TIME.
-- 如果为这两个参数都指定了一个值，那么用户只能到密码在 PASSWORD_REUSE_TIME 指定的天数内更改了 PASSWORD_REUSE_MAX 指定的次数后，才能重用密码。
   For example, if you specify PASSWORD_REUSE_TIME to 30 and PASSWORD_REUSE_MAX to 10, then the user can reuse the password after 30 days if the password has already been changed 10 times.
   -- 例如，如果将 PASSWORD_REUSE_TIME 指定为30，将 PASSWORD_REUSE_MAX 指定为10，则如果密码已经更改了10次，则用户可以在30天后重用该密码。
If you specify a value for either of these parameters and specify UNLIMITED for the other, then the user can never reuse a password.
-- 如果为这两个参数中的任何一个设置了值，并为另一个指定 UNLIMITED，则用户永远不能重用密码。
If you specify DEFAULT for either parameter, then Oracle Database uses the value defined in the DEFAULT profile. By default, all parameters are set to UNLIMITED in the DEFAULT profile. If you have not changed the default setting of UNLIMITED in the DEFAULT profile, then the database treats the value for that parameter as UNLIMITED.
-- 如果为其中一个参数指定了 DEFAULT，则 Oracle 数据库将使用在 DEFAULT 配置文件中定义的值。默认情况下，在 DEFAULT 配置文件中所有参数都设置为 UNLIMITED。如果未更改 DEFAULT 配置文件中 UNLIMITED 的默认设置，则数据库将该参数的值视为 UNLIMITED。
If you set both of these parameters to UNLIMITED, then the database ignores both of them. This is the default if you omit both parameters.
-- 如果将这两个参数都设置为 UNLIMITED，则数据库将忽略它们。这是你不配置这两个参数的默认配置。

PASSWORD_LOCK_TIME
Specify the number of days an account will be locked after the specified number of consecutive failed login attempts. If you omit this clause, then the default is 1 day.

PASSWORD_GRACE_TIME
Specify the number of days after the grace period begins during which a warning is issued and login is allowed. If you omit this clause, then the default is 7 days.

INACTIVE_ACCOUNT_TIME
Specify the permitted number of consecutive days of no logins to the user account, after which the account will be locked. The minimum value is 15 days. The maximum value is 24855. If you omit this clause, then the default is UNLIMITED.

PASSWORD_VERIFY_FUNCTION
You can pass a PL/SQL password complexity verification script as an argument to CREATE PROFILE by specifying PASSWORD_VERIFY_FUNCTION. Oracle Database provides a default script, but you can write your own function or use third-party software instead.
For function, specify the name of the password complexity verification routine. The function must exist in the SYS schema and you must have EXECUTE privilege on the function.
Specify NULL to indicate that no password verification is performed.
If you specify expr for any of the password parameters, then the expression can be of any form except scalar subquery expression.

Restriction on Password Parameters
When you assign a profile to an external user or a global user, the password parameters do not take effect for that user.

See Also:
Setting Profile Password Limits: Example

PASSWORD_ROLLOVER_TIME

You must configure a non-zero limit for the PASSWORD_ROLLOVER_TIME user profile parameter in order to enable the gradual database password rollover. You can configure this parameter using CREATE PROFILE or ALTER PROFILE.

Use expr to specify a value for PASSWORD_ROLLOVER_TIME in days. You must specify hours as a fraction of one day. For example, if you want to set the limit to four hours, expr would be 4/24 .

The granularity of the PASSWORD_ROLLOVER_TIME limit value is one second. For example, you can have a limit of one hour plus three minutes and five seconds by providing an expr like this: ( 1/24) + ( 3/1440) + (5/86400) ) .

The default setting for PASSWORD_ROLLOVER_TIME is 0, which means that gradual password rollover is disabled.
复制

修改用户 profile 绑定

ALTER USER SYSTEM PROFILE DBAMGMT;
复制

二、默认密码验证规则

https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/configuring-authentication.html#GUID-7B21CE5C-0698-4926-8F71-A05394581DDC

3.2.6.2 How Oracle Database Checks the Complexity of Passwords

Oracle Database provides four password verification functions to check password complexity.

These functions are in the catpvf.sql PL/SQL script (located in $ORACLE_HOME/rdbms/admin). When these functions are enabled, they can check whether users are correctly creating or modifying their passwords. When enabled, password complexity checking is not enforced for user SYS; it only applies to non-SYS users. For better security of passwords, Oracle recommends that you associate the password verification function with the default profile. About Customizing Password Complexity Verification provides an example of how to accomplish this.

3.2.6.5 ora12c_verify_function Password Requirements

The ora12c_verify_function function fulfills the Department of Defense Database Security Technical Implementation Guide requirements.
This function checks for the following requirements when users create or modify passwords:

-- The password contains no fewer than 8 characters and includes at least one numeric and one alphabetic character.
-- The password is not the same as the user name or the user name reversed.
-- The password is not the same as the database name.
-- The password does not contain the word oracle (such as oracle123).
-- The password differs from the previous password by at least 3 characters.
-- The password contains at least 1 special character.
-- The following internal check is also applied:
-- The password does not contain the double-quotation character ("). However, it can be surrounded by double-quotation marks.
# 至少1字母、1数字、1特殊字符、不低于8字符、不包含username\DB name及oracle关键字、修改密码至少比当前密码差异3字符
复制

3.2.6.6 ora12c_strong_verify_function Function Password Requirements

The ora12c_strong_verify_function function is a stringent password verify function.
This function checks for the following requirements when users create or modify passwords:

-- The password contains no fewer than 9 characters.
-- The password contains at least 2 upper case letters.
-- The password contains at least 2 lower case letters.
-- The password contains at least 2 numeric characters.
-- The password contains at least 2 special characters. These special characters are as follows: ‘ ~ ! @ # $ % ^ & * ( ) _ - + = { } [ ] \ / < > , . ; ? '' : | (space) 
-- The password differs from the previous password by at least 4 characters.
-- The following internal check is also applied:
-- The password does not contain the double-quotation character ("). It can be surrounded by double-quotation marks, however.
# 至少2大写字母、2小写字母、2数字、2特殊字符、不低于9字符、修改密码至少比当前密码差异4字符
复制

3.2.6.7 ora12c_stig_verify_function Password Requirements

The ora12c_stig_verify_function function fulfills the Security Technical Implementation Guides (STIG) requirements.
This function checks for the following requirements when users create or modify passwords:

-- The password has at least 15 characters.
-- The password has at least 1 lower case character and at least 1 upper case character.
-- The password has at least 1 digit.
-- The password has at least 1 special character.
-- The password differs from the previous password by at least 8 characters.
-- The following internal check is also applied:
-- The password does not contain the double-quotation character ("). However, it can be surrounded by double-quotation marks.
复制

The ora12c_stig_verify_function function is the default handler for the ORA_STIG_PROFILE profile, which is available in a newly-created or upgraded Oracle database.

三、密码 Expired 后处理(不知道密码情况)

1、直接修改状态，刷新 shared_pool 生效

UPDATE USER$ SET ASTATUS=0 WHERE NAME = 'SCOTT'; -- sys
COMMIT;
ALTER SYSTEM FLUSH SHARED_POOL; --清空缓存池，负载高环境不应该在线执行；不执行该命令上面设置不生效(虽说查看状态会变)
-- ASTATUS 状态码：
select * from user_astatus_map;
   STATUS# STATUS
---------- --------------------------------
         0 OPEN
         1 EXPIRED
         2 EXPIRED(GRACE)
         4 LOCKED(TIMED)
         8 LOCKED
         5 EXPIRED & LOCKED(TIMED)
         6 EXPIRED(GRACE) & LOCKED(TIMED)
         9 EXPIRED & LOCKED
        10 EXPIRED(GRACE) & LOCKED
复制

2、使用密文字段更新

-- 获取密码密文方式1
select NAME,PASSWORD,SPARE4 from user$ where name='SCOTT';
NAME     PASSWORD             SPARE4
-------- -------------------- ------------------------------------------------------------------------------------------------------------------------
SCOTT                           S:6BC1DB96CE62D9468922A6DD7E0C8CA904EEBB7AE7B79C5EF257E9DEB0C3;T:DA86551199D7BC8338CBCF23C40EF2F027DCE57030008B6E0F23D992502173614CCD85A40806FDACF5CCB9A6713E05C1E8DDD81D01DFD16D6BE28374A4DD8F345B04D15DA853AA467EBD4CF5916CD6A8
-- 获取密码密文方式2
select dbms_metadata.get_ddl('USER','SCOTT') from dual;
   CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:6BC1DB96CE62D9468922A6DD7E0C8CA904EEBB7AE7B79C5EF257E9DEB0C3;T:DA86551199D7BC8338CBCF23C40EF2F027DCE57030008B6E0F23D992502173614CCD85A40806FDACF5CCB9A6713E05C1E8DDD81D01DFD16D6BE28374A4DD8F345B04D15DA853AA467EBD4CF5916CD6A8'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PROFILE "DBAMGMT"
-- 使用密文修改
alter user scott identified by VALUES 'S:6BC1DB96CE62D9468922A6DD7E0C8CA904EEBB7AE7B79C5EF257E9DEB0C3;T:DA86551199D7BC8338CBCF23C40EF2F027DCE57030008B6E0F23D992502173614CCD85A40806FDACF5CCB9A6713E05C1E8DDD81D01DFD16D6BE28374A4DD8F345B04D15DA853AA467EBD4CF5916CD6A8';
commit;
复制