危险的Alter User权限

最新推荐文章于 2024-11-04 17:02:33 发布

转载最新推荐文章于 2024-11-04 17:02:33 发布 · 120 阅读

文章标签：

#数据库

本文介绍如何通过创建触发器来限制拥有ALTERUSER权限的用户，禁止他们修改SYSDBA用户的密码。通过具体实例演示了如何实现这一功能，并解释了触发器的作用和使用方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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.