1

I have a user in Oracle 12c database that will be used to run full data pump export and import. I would like to restrict logons from that user to localhost.

I have tried to create logon trigger:

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;
/

I can see from the audit trail that this trigger has fired (ORA-20000: Login not allowed) but the user is still allowed to logon from remote host.

This restriction works for normal users but not for the data pump user. I suspect this has something to do with the roles exp_full_database and imp_full_database.

Is there some way to make this trigger work or should I use local OS authentication?

1

ADMINISTER DATABASE TRIGGER Privilege Causes Logon Trigger to Skip Errors (Doc 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.

And:

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 

So yes, you can try local OS authentication.

  • you should know that quoting from an MOS Doc is a violation of the terms of use of MOS. Just cite the source with a comment of why it is being cited. – EdStevens Oct 10 '17 at 21:45
  • Thank you for the link, this explains the situation. Maybe I will create two different usernames, one for export and one for import. Export needs to be run automatically but import is only run on demand. – sjk Oct 16 '17 at 9:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.