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.
不知道是博主所说的什么版本不能revoke,11g R2是可以被revoke的。
这里说的revoke,不是ORACLE功能上不能revoke,而是指企业内部权限职责管理环境下不太好去revoke.