SQL> create profile prf limit PASSWORD_REUSE_TIME 0.00001 PASSWORD_REUSE_MAX UNLIMITED;

Profile created.

SQL> create user test identified by test profile prf;

User created.

SQL> grant create session to test;

Grant succeeded.

SQL> conn test/test
Connected.
TEST> alter user test identified by test;
alter user test identified by test
*
ERROR at line 1:
ORA-28007: the password cannot be reused


TEST> alter user test identified by test1;

User altered.

TEST> alter user test identified by test2;

User altered.

TEST> alter user test identified by test3;

User altered.

TEST> alter user test identified by test4;

User altered.

TEST> alter user test identified by test5;

User altered.

TEST> alter user test identified by test6;

User altered.

TEST> alter user test identified by test7;

User altered.

TEST> alter user test identified by test8;

User altered.

TEST> alter user test identified by test9;

User altered.

TEST> alter user test identified by test;
alter user test identified by test
*
ERROR at line 1:
ORA-28007: the password cannot be reused

create or replace trigger alter_pwd_trigger
 before alter
 on database
 declare
 begin
 if (ora_dict_obj_type = 'USER') and ( ora_dict_obj_name != 'RDS_MASTER_USER')  then
 raise_application_error(-20010,'you cannot change your own password');
 end if;
 end;
 /

SQL> show user
USER is "TEST01"
SQL> alter user test01 identified by "Test5523#";
alter user test01 identified by "Test5523#"
                              *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20010: you cannot change your own password
ORA-06512: at line 5

SQL> alter user u101 default tablespace schema1_tblspace;
alter user u101 default tablespace schema1_tblspace
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20010: you cannot change your own password
ORA-06512: at line 5

create or replace trigger alter_pwd_trigger
   before alter
   on database
   declare
   begin
     if ( ora_sysevent='ALTER') and (ora_dict_obj_type = 'USER') and ( ora_des_encrypted_password is not null ) then
            if ( ora_login_user NOT IN ('MASTERUSER','SYS','SYSTEM','RDSADMIN' )) and ( ora_dict_obj_name = 'MASTERUSER')   then
		         raise_application_error(-20010,'you cannot change rds master user password');
            elsif ( ora_login_user NOT IN ('MASTERUSER','SYS','SYSTEM','RDSADMIN' )) and ( ora_dict_obj_name NOT IN ('MASTERUSER','SYS','SYSTEM','RDSADMIN' )) then
                          raise_application_error(-20010,'you cannot change your own password or others password');
          end if;
     end if;
 end;
 /

create or replace trigger protect_alter_pwd_trigger
before create or atler or drop on database
declare
begin
      IF (ora_login_user != 'MASTERUSER')  THEN
           if (ora_dict_obj_owner = 'MASTERUSER') and (ora_dict_obj_name = 'ALTER_PWD_TRIGGER')  then 
            raise_application_error(-20010, 'You are not authorized to perform DDL on this trigger..');
           END IF;
     end if;
END;
/

SQL> show user
USER is "TEST01"
SQL> drop trigger masteruser.alter_pwd_trigger;

Trigger dropped.

create or replace trigger protect_alter_pwd_trigger
before create or atler or drop on database
declare
begin
      IF (ora_login_user != 'MASTERUSER')  THEN
           if (ora_dict_obj_owner = 'MASTERUSER') and (ora_dict_obj_name = 'ALTER_PWD_TRIGGER')  then 
            raise_application_error(-20010, 'You are not authorized to perform DDL on this trigger..');
           END IF;
     end if;
END;
/

SQL> create or replace trigger protect_alter_pwd_trigger
  2  before create or alter or drop on database
  3  declare
  4  begin
  5        IF (ora_login_user != 'MASTERUSER')  THEN
  6             if (ora_dict_obj_owner = 'MASTERUSER') and (ora_dict_obj_name = 'ALTER_PWD_TRIGGER')  then
  7              raise_application_error(-20010, 'You are not authorized to perform DDL on this trigger..');
  8             END IF;
  9       end if;
 10  END;
 11  /

Trigger created.

SQL> drop trigger masteruser.alter_pwd_trigger;

Trigger dropped.

SQL> set serveroutput on
SQL> declare
  2  begin
  3     IF (ora_login_user != 'MASTERUSER')  THEN
  4          if (ora_dict_obj_owner = 'MASTERUSER') and (ora_dict_obj_name = 'ALTER_PWD_TRIGGER')  then
  5             dbms_output.put_line(ora_dict_obj_owner);
  6             dbms_output.put_line(ora_dict_obj_name);
  7           raise_application_error(-20010, 'You are not authorized to perform DDL on this trigger..');
  8          END IF;
  9    end if;
 10  END;
 11  /

PL/SQL procedure successfully completed.

ora_dict_obj_name 
ora_dict_obj_owner
ora_dict_obj_type. 

create table log
  ( operation varchar2(25),
   owner     varchar2(25),
   name      varchar2(25),
  extra     varchar2(4000) );

create or replace trigger ddl_trigger
    before create or alter or drop on SCHEMA
    declare
        l_sysevent varchar2(25);
    begin
       select ora_sysevent into l_sysevent from dual;
   
       if ( l_sysevent in ('DROP','CREATE') )
       then
           insert into log
          select ora_sysevent, ora_dict_obj_owner,
                       ora_dict_obj_name, null
           from dual;
        commit;
     end if;
 end;
 /

SQL> show user
USER is "TEST01"


SQL> create table log
  2    ( operation varchar2(25),
  3     owner     varchar2(25),
  4     name      varchar2(25),
  5    extra     varchar2(4000) );

Table created.

SQL> create or replace trigger ddl_trigger
  2      before create or alter or drop on SCHEMA
  3      declare
  4          l_sysevent varchar2(25);
  5      begin
  6         select ora_sysevent into l_sysevent from dual;
  7
  8         if ( l_sysevent in ('DROP','CREATE') )
  9         then
 10             insert into log
 11            select ora_sysevent, ora_dict_obj_owner,
 12                         ora_dict_obj_name, null
 13             from dual;
 14          commit;
 15       end if;
 16   end;
 17   /

Trigger created.

SQL> create table t ( x int );

Table created.

SQL> alter table t add y int;

Table altered.

SQL> select * from log;

no rows selected

SQL> create table log
  2    ( operation varchar2(25),
  3     owner     varchar2(25),
  4     name      varchar2(25),
  5    extra     varchar2(4000) );

Table created.

SQL> create or replace trigger ddl_trigger
  2      before create or alter or drop on SCHEMA
  3      declare
  4          l_sysevent varchar2(25);
  5      begin
  6         select ora_sysevent into l_sysevent from dual;
  7     
  8         if ( l_sysevent in ('DROP','CREATE') )
  9         then
 10             insert into log
 11            select ora_sysevent, ora_dict_obj_owner,
 12                         ora_dict_obj_name, null
 13             from dual;
 14          commit;
 15       end if;
 16   end;
 17   /

Trigger created.

SQL> create table t ( x int );
create table t ( x int )
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at line 12

select * from v$version;

create or replace trigger alter_pwd_trigger
   before alter
   on database
   declare
   begin
     if ( ora_sysevent='ALTER') and (ora_dict_obj_type = 'USER') and ( ora_des_encrypted_password is not null ) then
            if ( ora_login_user NOT IN ('MASTERUSER','SYS','SYSTEM' )) and ( ora_dict_obj_name = 'MASTERUSER')   then
		         raise_application_error(-20010,'you cannot change rds master user password');
            elsif ( ora_login_user NOT IN ('MASTERUSER','SYS','SYSTEM' )) and ( ora_dict_obj_name NOT IN ('MASTERUSER','SYS','SYSTEM' )) then
                          raise_application_error(-20010,'you cannot change your own password or others password');
          end if;
     end if;
 end;
 /

create or replace trigger protect_alter_pwd_trigger
before create or alter or drop on database
declare
begin
      IF (ora_login_user != 'MASTERUSER')  THEN
           if (ora_dict_obj_owner = 'MASTERUSER') and (ora_dict_obj_name = 'ALTER_PWD_TRIGGER')  then 
            raise_application_error(-20010, 'You are not authorized to perform DDL on this trigger..');
           END IF;
     end if;
END;
/

create or replace trigger protect_alter_pwd_trigger
before create or alter or drop on database
declare
begin
      IF (ora_login_user != 'MASTERUSER')  THEN
           if (ora_dict_obj_owner = 'MASTERUSER') and (ora_dict_obj_name = 'ALTER_PWD_TRIGGER')  then 
            raise_application_error(-20010, 'You are not authorized to perform DDL on this trigger..');
           END IF;
     end if;
END;
/