备份当前用户密码

11g 备份当前DHW用户密码

select sqltext from ( select name, 'alter user '||name||' identified by values '''||password||''';' sqltext from
user$ where spare4 is null and password is not null union select name, 'alter user '||name||' identified by values '''||spare4||';'||password||''';'
sqltext from user$ where spare4 is not null and password is not null ) where name ='DHW';


SQLTEXT
-----------------------------------------------------------------------------------------------------------------------
alter user DHW identified by values 'S:22F5C13C8E4360B91B07DE167301521E920B5860F61C1309B5787FE28ADE;29993640B77C2E30';
复制

19c 备份当前C##DHW用户密码

select sqltext from ( select name, 'alter user '||name||' identified by values '''||password||''';' sqltext from
user$ where spare4 is null and password is not null union select name, 'alter user '||name||' identified by values '''||spare4||';'||password||''';'
sqltext from user$ where spare4 is not null and password is not null union select name, 'alter user '||name||' identified by values '''||spare4||''';'
sqltext from user$ where spare4 is not null and password is null ) where name ='C##DHW';

SQLTEXT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter user C##DHW identified by values 'S:1B55C7DF10CFE4F5485D6C758BA5B8C5719CDEF0535E1B52D60BE2BC119F;T:F9EEB97EFE8E494EA415F51F11835C033E69CDECDEA28341B2448B959F8E10581EFB660613BDEF9D815C86EE9A4ABF72364C3C57CCA32C986E258CA13BCA7D9EA31A25325E700E75315EDD0358880360';
复制

是否可以自定义密码验证函数

Can we customize existing password verify function? (Doc ID 2781070.1)
可以。
It is not recommended to change the existing profiles and functions.
You can create function as per your requirement and assign profile to dummy user. Once the testing is successful, you can assign the profile to sys user.

19c中自带的密码函数

set lines 200
col owner for a30
col object_name for a40
col owner for a10
select owner,object_name,object_type,status from dba_objects where object_name like '%VERIFY%' and OBJECT_TYPE='FUNCTION';

OWNER      OBJECT_NAME                              OBJECT_TYPE             STATUS
---------- ---------------------------------------- ----------------------- -------
SYS        ORA12C_VERIFY_FUNCTION                   FUNCTION                VALID
SYS        VERIFY_FUNCTION_11G                      FUNCTION                VALID
SYS        VERIFY_FUNCTION                          FUNCTION                VALID
SYS        ORA12C_STRONG_VERIFY_FUNCTION            FUNCTION                VALID
SYS        ORA12C_STIG_VERIFY_FUNCTION              FUNCTION                VALID

select * from dba_dependencies where name in (select object_name from dba_objects where object_name like '%VERIFY%' and OBJECT_TYPE='FUNCTION')

复制

存在很多依赖的函数和包

自定义密码函数（参考11g utlpwdmg.sql）

实现功能：
长度至少8位，不能是内置的welcome等，至少包含一个大写字母和一个特殊字符，新旧密码至少相差3个字符。
（如果想实现更高级的限制功能，可以使用19c内置的密码函数。）

CREATE OR REPLACE FUNCTION verify_function_cust(username     varchar2,
                                           password     varchar2,
                                           old_password varchar2)
  RETURN boolean IS
  n          boolean;
  m          integer;
  differ     integer;
  isdigit    boolean;
  ischar     boolean;
  ispunct    boolean;
  digitarray varchar2(20);
  punctarray varchar2(25);
  chararray  varchar2(52);

BEGIN
  digitarray := '0123456789';
  chararray  := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  punctarray := '!"#$%&()``*+,-/:;<=>?_';

  -- Check if the password is same as the username
  IF NLS_LOWER(password) = NLS_LOWER(username) THEN
    raise_application_error(-20001, 'Password same as or similar to user');
  END IF;

  -- Check for the minimum length of the password
  IF length(password) < 8 THEN
    raise_application_error(-20002, 'Password length less than 8');
  END IF;

  -- Check if the password is too simple. A dictionary of words may be
  -- maintained and a check may be made so as not to allow the words
  -- that are too simple for the password.
  IF NLS_LOWER(password) IN ('welcome',
                             'database',
                             'account',
                             'user',
                             'password',
                             'oracle',
                             'computer',
                             'abcd',
                             'welcome1',
                             'database1',
                             'account1',
                             'user1234',
                             'password1',
                             'oracle123',
                             'computer1',
                             'abcdefg1',
                             'change_on_install') THEN
    raise_application_error(-20002, 'Password too simple');
  END IF;

  -- Check if the password contains at least one letter, one digit and one
  -- punctuation mark.
/*  -- 1. Check for the digit
  isdigit := FALSE;
  m       := length(password);
  FOR i IN 1 .. 10 LOOP
    FOR j IN 1 .. m LOOP
      IF substr(password, j, 1) = substr(digitarray, i, 1) THEN
        isdigit := TRUE;
        GOTO findchar;
      END IF;
    END LOOP;
  END LOOP;
  IF isdigit = FALSE THEN
    raise_application_error(-20003,
                            'Password should contain at least one digit, one character and one punctuation');
  END IF;*/
  -- 2. Check for the character
  <<findchar>>
  ischar := FALSE;
  FOR i IN 1 .. length(chararray) LOOP
    FOR j IN 1 .. m LOOP
      IF substr(password, j, 1) = substr(chararray, i, 1) THEN
        ischar := TRUE;
        GOTO findpunct;
      END IF;
    END LOOP;
  END LOOP;
  IF ischar = FALSE THEN
    raise_application_error(-20003,
                            'Password should contain at least one \
              digit, one upper letter and one punctuation');
  END IF;
  -- 3. Check for the punctuation
  <<findpunct>>
  ispunct := FALSE;
  FOR i IN 1 .. length(punctarray) LOOP
    FOR j IN 1 .. m LOOP
      IF substr(password, j, 1) = substr(punctarray, i, 1) THEN
        ispunct := TRUE;
        GOTO endsearch;
      END IF;
    END LOOP;
  END LOOP;
  IF ispunct = FALSE THEN
    raise_application_error(-20003,
                            'Password should contain at least one \
              digit, upper letter and one punctuation');
  END IF;

  <<endsearch>>
-- Check if the password differs from the previous password by at least
  -- 3 letters
  IF old_password IS NOT NULL THEN
    differ := length(old_password) - length(password);
  
    IF abs(differ) < 3 THEN
      IF length(password) < length(old_password) THEN
        m := length(password);
      ELSE
        m := length(old_password);
      END IF;
    
      differ := abs(differ);
      FOR i IN 1 .. m LOOP
        IF substr(password, i, 1) != substr(old_password, i, 1) THEN
          differ := differ + 1;
        END IF;
      END LOOP;
    
      IF differ < 3 THEN
        raise_application_error(-20004,
                                'Password should differ by at \
         least 3 characters');
      END IF;
    END IF;
  END IF;
  -- Everything is fine; return TRUE ;   
  RETURN(TRUE);
END;
/

复制

验证函数应用给profile

注意：生产环境一般新建profile给业务用户，因为默认内置用户都是使用的default profile

alter profile DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION verify_function_cust;
复制

密码验证函数测试

使用sys用户修改密码（具有alter user权限，不需旧密码）

上述函数特权用户修改密码不受新旧密码至少相差3个字符限制。

Password Verify Function Not Enforcing Difference Between Old and New Passwords (Doc ID 816932.1)

Oracle无法自动提供旧密码给验证函数。密码以单向哈希方式加密存储，无法逆向工程转换为明文（有关详细信息，请参阅文档271825.1）。
这意味着，如果在更改密码时未提供旧密码，Oracle将无法检索旧密码以进行任何有用的比较。因此，只有在更改密码的用户提供旧密码时，旧密码才能传递给验证函数。
在正常情况下，始终需要提供旧密码，这些问题就不是问题。然而，某些用户有特权在不提供旧密码的情况下更改密码。在这些情况下，无法将旧密码传递给验证函数，因此必须跳过任何与旧密码比较的检查。

SQL> alter user C##DHW identified by "Qosd!oop";

User altered.

SQL> alter user C##DHW identified by "Qosd";
alter user C##DHW identified by "Qosd"
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20002: Password length less than 8

SQL> alter user C##DHW identified by "Qosdwrts";
alter user C##DHW identified by "Qosdwrts"
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20003: Password should contain at least one upper letter and one
punctuation


SQL> alter user C##DHW identified by "aosdwrts";
alter user C##DHW identified by "aosdwrts"
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20003: Password should contain at least one upper letter and one
punctuation

SQL> alter user C##DHW identified by "aosdwrts_Q";

User altered.

SQL> alter user C##DHW identified by "aosdwrts_Q";

User altered.

复制

低权限用户修改密码

无alter user权限时

可以使用ALTER USER或者password方式修改，都需要验证旧密码才行。

SQL> conn C##DHW/aosdwrts_Q
Connected.
SQL> ALTER USER C##DHW IDENTIFIED BY "aosdwrts_Q";
ALTER USER C##DHW IDENTIFIED BY "aosdwrts_Q"
*
ERROR at line 1:
ORA-28221: REPLACE not specified

SQL> ALTER USER C##DHW IDENTIFIED BY "aosdwrts_Q" REPLACE "aosdwrts_Q";
ALTER USER C##DHW IDENTIFIED BY "aosdwrts_Q" REPLACE "aosdwrts_Q"
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20004: Password should differ by at \
least 3 characters

SQL> ALTER USER C##DHW IDENTIFIED BY "aosdwrts_Q123" REPLACE "aosdwrts_Q";

User altered.

复制

有alter user权限时

SQL> grant  alter user to C##DHW;

Grant succeeded.

SQL> conn C##DHW/aosdwrts_Q123
Connected.
SQL> ALTER USER C##DHW IDENTIFIED BY "aosdwrts_Q123" ;

User altered.

SQL> 
复制

订阅号：DongDB手记
墨天轮：https://www.modb.pro/u/231198