oracle单独禁止A用户修改B用户密码

一、创建触发器

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 "SYS"
SQL>
SQL>

SQL> show user;
USER is "B"
SQL> alter user b identified by oracle;

User altered.

SQL>