ORA-28221 DDL Replication Error After Changing An Expired Password If Password Verify Function Exists (Doc ID 2824671.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

DDL replication error (ORA-ora-28221) after changing an expired password if password verify function exists

After changing an expired password on request, ddl replication fails with ora-28221.
Because the "alter user USERNAME identified by PASS" command is implicitly executed on the shard catalog without "REPLACE" option and replicates to the shards for execution as is. However, the user on shard does not have the "alter user" privilege to execute the "alter user USERNAME identified by PASS" command without the "REPLACE" option.

Error Codes
---------------------------------------------------
28221


example of the problem:

-----------------------------------
1. we have a profile with a simple verification function on the shard catalog and each shard database:

-- create verify function
create or replace function sys.test_verify_function(username varchar2,password varchar2,old_password varchar2) return boolean is
begin
  return true;
end test_verify_function;
/

-- Create profile
create profile test_profile limit password_verify_function test_verify_function;

-----------------------------------

2. create new user:

-- create user
alter session enable shard ddl;

create user test_user identified by test_user profile test_profile password expire;
grant create session to test_user;

-----------------------------------

3. connect as test_user and change password:

>sqlplus test_user/test_user@cpscat1

SQL*Plus: Release 12.2.0.1.0 Production on Wed Sep 1 17:24:07 2021

Copyright (c) 1982, 2016, Oracle. All rights reserved.

ERROR:
ORA-28001: the password has expired


Changing password for test_user
New password:
Retype new password:
Password changed

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

-----------------------------------

4. ddl replication fail:

SQL> select t.ddl_num, t.ddl_error, t.name, t.database_num from gsmadmin_internal.database t order by t.database_num;

  DDL_NUM DDL_ERROR NAME DATABASE_NUM
---------- ------------------------------------------------------ --------- ------------
  0 cpssh11 1
  0 cpssh12 11

SQL> /

  DDL_NUM DDL_ERROR NAME DATABASE_NUM
---------- ------------------------------------------------------ --------- ------------
  2707 ORA-28221: REPLACE not specified cpssh11 1
  ORA-06512: at "SYS.EXECASUSERID", line 41
  ORA-06512: at "SYS.DBMS_SYS_SQL", line 1529
  ORA-06512: at "SYS.EXECASUSERID", line 19
  ORA-06512: at "SYS.EXECASUSER", line 25
  ORA-06512: at "GSMADMIN_INTERNAL.EXECUTEDDL", line 69
  ORA-06512: at line 1 \(ngsmoci_execute\)
  2707 ORA-28221: REPLACE not specified cpssh12 11
  ORA-06512: at "SYS.EXECASUSERID", line 41
  ORA-06512: at "SYS.DBMS_SYS_SQL", line 1529
  ORA-06512: at "SYS.EXECASUSERID", line 19
  ORA-06512: at "SYS.EXECASUSER", line 25
  ORA-06512: at "GSMADMIN_INTERNAL.EXECUTEDDL", line 69
  ORA-06512: at line 1 \(ngsmoci_execute\)

SQL>

Changes

 19C (sharding feature)

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.