I was obviously speaking too soon in my previous post, saying that that particular statement was actualy never put in the library cache due to failde pares phase. I was wrong about that statement not being put in the library cache, but I was not wrong about failed parsing. That statement realy wasn't parsed succesfully, ORA-1031 (insufficient privileges) clearly indicates this.
I just did some tests on my home database. I logged as SCOTT (with only standard priviledges) and tried ALTER DATABASE RECOVER. It failed fith ORA-1031. So parsing of that statement failed, hovever it indeed was put in V$SQLAREA, with parsing_user_id=0.
Then I connected as SYS and tried the same RECOVER statement on an open database. This time it failed with "ORA-01124: cannot recover data file 1 - file is in use or recovery", which is also recorded in alert.log. This same happens when connected as any user with DBA role or ALTER DATABASE priviledge.
This indicates that the user who isued this statement on Jeff's database was not connected as SYS/INTERNAL or as SYSDBA, he even was not connected as a user with enabled DBA role or ALTER DATABASE privs. So I think this narrows Jeff's potential troublemakers to a non-dba users.
Now Jeff, surprise me and say that all your users have DBA role granted
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Originally posted by jmodic Now Jeff, surprise me and say that all your users have DBA role granted
No, no. Only a handfull of people have the DBA role. The scenario you tested validates my own tests today. I still have my suspicians, but can't prove anything. I'm contemplating auditing user connects and disconnects, but am concerned about the potential overhead during connect time. Somedays I hate being a DBA.