Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
4  
Is another trigger coming into play here? Your trigger is defined to give the message Denied! You are not allowed to logon the database, but the error displayed is Sorry, you are not allowed here!. Also, the line number seems off. –  Leigh Riffel Jan 10 '12 at 14:45

2 Answers 2

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:

SELECT 'Check This' FROM dual WHERE sys_context('USERENV','SESSION_USER') = 'xx';

SELECT 'Check This' FROM dual WHERE sys_context('USERENV','IP_ADDRESS') <> '10.0.30.219';

SELECT 'Check This' FROM user_role_privs WHERE granted_role='DBA';

SELECT 'Check This' FROM user_objects WHERE Object_Name='YOU_MAY_NOT_LOGIN' AND Object_Type='TRIGGER';

SELECT 'Check This' FROM User_sys_Privs WHERE Privilege='ADMINISTER DATABASE TRIGGER';

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 an EXECUTE 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.

share|improve this answer
    
yes, more than one of the queries return 'Check This'. What do i need to do now to make the query work? –  user996853 Jan 11 '12 at 15:31
    
Excellent, then you have found the problem. Depending on which statement returned "Check This", either 1. The user is not the user being blocked, 2. The IP address is not the IP address being blocked, 3. The user has the DBA role, 4. The user owns the trigger, or 5. The user has the ADMINISTER DATABASE TRIGGER privilege. –  Leigh Riffel Jan 11 '12 at 15:35

From this answer to a similar question on ServerFault:

The trigger will not deny access if

  • A member of the DBA group can't be disconnected with an ON LOGON trigger.
  • The owner of the trigger is the user logging in.
  • The user has the privilege Administer Database Trigger
share|improve this answer
    
None of the three conditions are present here. I created the trigger using a DBA user and in the trigger, I provided a random test user with only connect privilege. –  user996853 Jan 10 '12 at 13:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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