如何在保留IMP_FULL_DATABASE/DBA角色时限制特定主机登录数据库？

我在数据库上创建了LOGON事件触发器SYS.LOGIN_RJ，用于拒绝主机名以C开头的登录请求，触发器代码如下：

CREATE OR REPLACE TRIGGER "SYS"."LOGIN_RJ" AFTER LOGON ON DATABASE
DECLARE
HOST_NAME VARCHAR2(128):=SYS_CONTEXT('USERENV','HOST');
BEGIN
if (HOST_NAME like 'C%') then
    raise_application_error(-20000,'host login denied');
end if;
END;

该触发器对普通用户有效，但拥有DBA或IMP_FULL_DATABASE角色的特权用户仍可正常登录，仅在数据库告警日志中留下错误跳过的记录：

> Skipped error 604 during the execution of SYS.LOGIN_RJ
> 
> dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=0, mask=0x0)
> Error Stack Dump -----
> ORA-00604: error occurred at recursive SQL level 1
> ORA-20000: host login denied
> ORA-06512: at line 6

现需在不回收DBA/IMP_FULL_DATABASE角色权限的前提下，实现对这类特权用户的登录限制。

Oracle对特权用户的AFTER LOGON触发器错误会自动跳过（此时登录流程已完成），因此不能仅通过抛出错误来限制，需要主动断开会话。以下是修改后的触发器代码：

CREATE OR REPLACE TRIGGER "SYS"."LOGIN_RJ" AFTER LOGON ON DATABASE
DECLARE
    l_host_name VARCHAR2(128) := SYS_CONTEXT('USERENV', 'HOST');
    l_sid NUMBER;
    l_serial# NUMBER;
    l_is_priv_user BOOLEAN;
BEGIN
    -- 检查当前用户是否为特权用户（拥有DBA/IMP_FULL_DATABASE角色）
    SELECT CASE WHEN COUNT(*) > 0 THEN TRUE ELSE FALSE END
    INTO l_is_priv_user
    FROM dba_role_privs
    WHERE grantee = SYS_CONTEXT('USERENV', 'SESSION_USER')
      AND granted_role IN ('DBA', 'IMP_FULL_DATABASE');

    -- 若主机名以C开头，无论是否为特权用户，直接断开会话
    IF l_host_name LIKE 'C%' THEN
        SELECT sid, serial#
        INTO l_sid, l_serial#
        FROM v$session
        WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

        EXECUTE IMMEDIATE 'ALTER SYSTEM DISCONNECT SESSION ''' || l_sid || ',' || l_serial# || ''' IMMEDIATE';
    END IF;
END;
/

关键说明 #

1. 主动断开会话：通过ALTER SYSTEM DISCONNECT SESSION强制终止符合条件的会话，特权用户无法绕过此操作。
2. 特权用户判断：通过查询dba_role_privs确认用户是否拥有目标角色，确保逻辑覆盖所有特权用户。
3. 权限要求：触发器需以SYS用户创建，确保具备查询dba_role_privs、v$session以及执行ALTER SYSTEM的权限。
4. 可选优化：可添加例外逻辑（如排除SYS、SYSTEM等核心用户），避免误阻断关键操作。

内容的提问来源于stack exchange，提问作者cheeeeen