This content has been marked as final. Show 12 replies
Check the error stack. It will give you the details of a logon trigger created for the entire database which seems to be working fine (as per your post) for a DBA account but fails for normal users.
Search for logon trigger created in your database:
select * from dba_triggers where trigger_type='AFTER EVENT' and rtrim(triggering_event) = 'LOGON'
You can disable this trigger until it will be fixed.
Logon trigger will not be run for user having DBA or SYSDBA role.
Thanks for all
Please Satish, give me more details on how to Check the error stack.
I'm an Oracle developer not a DBA.
Can you give me an example where the LOGON trigger does not work for a SYSDBA or DBA user?
Alex has given you the query to find out the required trigger. If you do not have the privilege to select from DBA* views, ask your database administrator to fix the problem for you.
The error stack is the one that you get when you log on to the database as normal user. That should contain information about the trigger name. If it doesn't (possibility is remote), then ask your administrator to verify the logic in that trigger.
I have got the logon trigger.
I have disabled this trigger by commenting it's code using the enterprise manager .
Now normal user can log in.
It's very good, what should i do now ?
The code of this trigger is :
insert into audit_connection_table
values (user,sysdate,'log on');
This trigger inserts into audit_connection_table table which is owned to Man schema.
Should this table be granted to all users ?
Thank you very much for your help,
I droped the user called man, i don't need it, it was for test.
Now, every thing is ok
I've created logon trigger containing an error which will raise an exception when running.
SQL> conn test/test
ORA-00604: error occurred at recursive SQL level 1
ORA-02003: invalid USERENV parameter
ORA-06512: at line 8
SQL> conn me
SQL> conn sys as sysdba
I guess the exception will still be raised but in case of SYSDBA it would be logged to alert log only. Please check your alert log.
You are right errors will be raised anyway and logged into alert log but users with DBA and SYSDBA role bypass any errors even if trigger is invalid actually.
Still reporting message will appear in alert log.