To block a particular user and IP combination on an Oracle Database , I created the following trigger, and compiled without errors.
Create or replace trigger you_may_not_login
after logon on database
begin
if sys_context('USERENV','SESSION_USER')='xx' AND sys_context('USERENV','IP_ADDRESS')='10.0.30.219' then
raise_application_error(-20001,'Denied! You are not allowed to logon the database');
end if;
end;
/
When I login using the 'xx' user, the alert log shows the following error but access IS granted to the database, the trigger somehow fails from blocking the login of the user.
Errors in file /oraarch/core/udump/wfsbi_ora_9338.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Sorry, you are not allowed here!
ORA-06512: at line 9
Where am I going wrong?/
Best Answer
You might want to verify this in SQLPlus. If it still doesn't disconnect you then verify your assumptions by running the following after the trigger finishes:
If any of these return
Check This
then one of your assumptions is incorrect.For me all that was necessary to be disconnected was the
RAISE_APPLICATION_ERROR
, but another site uses anEXECUTE IMMEDIATE 'DISCONNECT';
as well. Perhaps this allows it to work even when the DBA role has been granted. I have not tested it to know.You should also be aware that this should not be used for security in place of secure passwords and other measures as the IP_ADDRESS the client sends can be altered.