ORA-20003: You are not allowed to alter password user

Today while working on UAT database refresh activity, had a step to change all schemas password to a common one.

 begin
  for x in (select username from dba_users ) loop
     execute immediate 'alter user '||x.username||' identified by xxxxxx profile DEFAULT';
  end loop;
end;
/

The above was executed as SYS user and it errored out —

ERROR at line 1:
ORA-20003: You are not allowed to alter password user.
ORA-06512: at line 4
ORA-06512: at line 3

For me it was the first time that something like this happened.It was a little hard to get why was “alter user identified by” sql failing.

After few minutes i started thinking how can this be implemented and the first thing which came to mind was “Triggers”. So started looking out for triggers.

SQL> select trigger_name from dba_triggers where TRIGGERING_EVENT='ALTER';

no rows selected

SQL> select trigger_name from dba_triggers where TRIGGERING_EVENT like '%ALTER%';

TRIGGER_NAME
--------------------------------------------------------------------------------
xxxxxxxxx
xxxxxxxxx
PASS_CHANGE
xxxxxxxxx

Based on the trigger name PASS_CHANGE checked the trigger body which has “after alter on database” event —

SQL> select  trigger_body from dba_triggers where trigger_name='PASS_CHANGE';

TRIGGER_BODY
--------------------------------------------------------------------------------
BEGIN
   IF ora_sysevent='ALTER' and ora_dict_obj_type = 'USER' and  ora_des_encrypted_password is not null
   THEN
        RAISE_APPLICATION_ERROR(-20003,  'You are not allowed to alter password user.');
  END IF;
END;

Simply disabled the trigger and proceeded with the steps. Chaning the default tablespace for the user would also error out –

SQL> alter user scott default tablespace system;
alter user scott default tablespace system
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20003: You are not allowed to alter password user.
ORA-06512: at line 5

Advertisement