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.
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)