一、创建触发器
A用户下创建触发器：
SQL> conn a/oracle
Connected.
SQL> create or replace trigger db_operator.NO_PASSWD_CHANGE_FOR_B
before alter on database
declare
user STRING(30);
begin
SELECT SYS_CONTEXT('USERENV','SESSION_USER') into user from dual;
if user = 'A'
THEN
if (ORA_DICT_OBJ_NAME = 'B')
THEN RAISE_APPLICATION_ERROR(-20001,'NO PERMIT TO CHANGE PASSWORD FOR B !');
END IF;
END IF;
END;
/
Trigger created.
SQL>
二、测试效果
1.A修改B密码
SQL> show user;
USER is "A"
SQL> alter user b identified by oracle;
alter user b identified by oracle
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: NO PERMIT TO CHANGE PASSWORD FOR B !
ORA-06512: at line 8
SQL>
2.SYS修改B密码
SQL> show user;
USER is "SYS"
SQL>
SQL> alter user b identified by oracle;
User altered.
SQL>
3.SYSTEM修改B密码
SQL> show user;
USER is "SYSTEM"
SQL> alter user b identified by oracle;
User altered.
SQL>
4.B修改B密码
SQL> show user;
USER is "B"
SQL> alter user b identified by oracle;
User altered.
SQL>
