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?
Best Answer
ADMINISTER DATABASE TRIGGER Privilege Causes Logon Trigger to Skip Errors (Doc ID 265012.1)
And:
So yes, you can try local OS authentication.