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.

An application is connecting to Oracle to do stuff. It's not working but I don't get any ORA errors in the application itself. I'm thinking its a permission problem. So my question is what is the best way to check to see if a oracle account tried to perform an operation that it didn't have permission to do? It would also help to know what operation it tried to perform so I can grant it.

share|improve this question
    
You say "it's not working" but you don't say what actually happens. Obviously the database operations don't work but what happens when you try? Does your application throw an exception or return an error code? If so, is it anything remotely useful? –  FrustratedWithFormsDesigner May 22 '12 at 21:24
    
@FrustratedWithFormsDesigner Nothing helpful from the application level so I need to venture to the database level. The application is performing database operations as a specific Oracle user so if I can trace the operations the Oracle account is making I can see if the issue is a permission problem or not. –  Andy Arismendi May 22 '12 at 21:27
    
If missing permissions were the cause, the database raises an error. It never silently fails (at least not Oracle). If you don't see any errors this either means the statements are not executed at all or the application swallows the error message. –  a_horse_with_no_name May 22 '12 at 21:43
    
@a_horse_with_no_name Right, I don't know if the application is not giving me the ORA error or not. Lets assume there is actually an ORA error and the application isn't surfacing it. Do I need to turn on auditing for all actions of this user or something? –  Andy Arismendi May 22 '12 at 21:48
    
If the application doesn't show you the errors, then you'll need to turn on full auditing in Oracle. –  a_horse_with_no_name May 22 '12 at 21:55
add comment

1 Answer

up vote 3 down vote accepted

Another option (short of full auditing) is a servererror trigger for the schema in question (or the whole database).

drop table error_log;
create table error_log (error_time timestamp, username varchar(50), msg varchar(4000), stmt varchar(4000));

CREATE OR REPLACE TRIGGER servererror_trigger 
  AFTER SERVERERROR
  ON SCHEMA
declare
   sql_text ora_name_list_t;
   msg_     varchar2(2000) := null;
   stmt_    varchar2(2000) := null;
begin

  for depth in 1 .. ora_server_error_depth loop
    msg_ := msg_ || ora_server_error_msg(depth);
  end loop;

  for i in 1 .. ora_sql_txt(sql_text) loop
     stmt_ := stmt_ || sql_text(i);
  end loop;

  insert into error_log
  (error_time, username, msg, stmt)
  values 
  (current_timestamp, ora_login_user, msg_, stmt_);
end;
/

If it's for a single schema/user the trigger needs to be created under that user. You also can trap errors for the whole database by specifying ON DATABASE. In that case you'll need to create that trigger and the table as DBA I assume (haven't tried that).

Now nearly all errors that happen will be recorded by the trigger (see the manual for a list of errors that will not be reported: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_trigger.htm#LNPLS1992)

share|improve this answer
    
I'm not having much luck with the trigger. I created 2 users to replicate the scenario. I tried to create a table as user1 in user2's tablespace and got the expected ORA-01031: insufficient privileges error but nothing was captured in the error_log table. I created the trigger as user1 and user2 and the two error_log tables created were empty after receiving the permission error. I also tried ON DATABASE but I had to run that one as the sys account because the test accounts didn't have enough permissions to do that. –  Andy Arismendi May 27 '12 at 22:45
add comment

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.