alter user mysql_危险的Alter User权限

Alter user 是Oracle数据库系统中十分powerful的权限，可以修改一般用户的属性例如默认表空间、profile等，甚至于可以修改SYS用户的密码。所以该权限是十分危险的， 在赋权时要十分小心。

针对现有用户已经拥有ALTER USER权限的，又不能revoke回来的，可以考虑使用如下触发器的方法，禁止任何拥有Alter user权限的用户修改SYSDBA的密码：

SQL> grant alter user to alteruser;

Grant succeeded.

SQL> select * from DBA_SYS_PRIVS where grantee like 'ALTER%USER%';

GRANTEE PRIVILEGE ADMIN_

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

ALTERUSER UNLIMITED TABLESPACE NO

ALTERUSER ALTER USER NO

SQL> conn / as sysdba

Connected.

SQL> CREATE or REPLACE TRIGGER prohibit_alter_SYSTEM_SYS_pass

BEFORE ALTER on ALTERUSER.schema

BEGIN

IF ora_sysevent='ALTER' and ora_dict_obj_type = 'USER' and

(ora_dict_obj_name = 'SYSTEM' or ora_dict_obj_name = 'SYS')

THEN

RAISE_APPLICATION_ERROR(-20003,

'You are not allowed to alter SYSTEM/SYS user.');

END IF;

END;

/

Trigger created.

使用范例：

SQL> conn alteruser/oracle

Connected.

SQL> alter user system identified by manager;

alter user system identified by manager

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20003: You are not allowed to alter SYSTEM/SYS user.

ORA-06512: at line 5

SQL> alter user sys identified by manager;

alter user sys identified by manager

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20003: You are not allowed to alter SYSTEM/SYS user.

ORA-06512: at line 5

SQL>

SQL> alter user dbsnmp identified by dbsnmp;

User altered.