主页 / dba / 问题 / 188100
Accepted
sjk
Asked: 2017-10-11 04:12:51 +0800 CST

如何限制数据泵登录到本地主机

  • 772

我在 Oracle 12c 数据库中有一个用户将用于运行完整的数据泵导出和导入。我想限制从该用户登录到本地主机。

我试图创建登录触发器：

create or replace trigger logon_trigger 
  after logon on database
declare
  host varchar2(50);
begin 
  host := trim(lower(sys_context('USERENV', 'HOST')));
  if lower(user) = '<data pump user>' then
    if host <> '<hostname>' then
      raise_application_error(-20000, 'Login not allowed');
    end if;
  end if;
end;
/

我可以从审计跟踪中看到此触发器已触发（ORA-20000：不允许登录），但仍允许用户从远程主机登录。

此限制适用于普通用户，但不适用于数据泵用户。我怀疑这与角色 exp_full_database 和 imp_full_database 有关。

有什么方法可以使这个触发器工作，还是我应该使用本地操作系统身份验证？

oracle oracle-12c

1 个回答

  1. Best Answer

    ADMINISTER DATABASE TRIGGER 特权导致登录触发器跳过错误（文档 ID 265012.1）

    ADMINISTER DATABASE TRIGGER Privilege Behavior with Database Logon Trigger
--------------------------------------------------------------------------
Logon triggers can be used to mediate database access: when the restrictive 
conditions are not met, an application error with a message is raised that 
causes the logon to be denied.

...

However, we need to keep at least one user who can still connect when there is 
a problem : a fallback mechanism must exist where an administrative user is 
exempt from such errors of a prohibited connection. 

Any user granted the ADMINISTER DATABASE TRIGGER system privilege can still 
connect : instead of getting the error causing the session to be terminated, 
the error is recorded in the alert.log and a trace file in user_dump_dest.

    和：

    select * from dba_sys_privs where privilege = 'ADMINISTER DATABASE TRIGGER';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
WMSYS                          ADMINISTER DATABASE TRIGGER              NO 
DBA                            ADMINISTER DATABASE TRIGGER              YES
SYS                            ADMINISTER DATABASE TRIGGER              NO 
IMP_FULL_DATABASE              ADMINISTER DATABASE TRIGGER              NO

    所以是的，您可以尝试本地操作系统身份验证。

    • 1

相关问题