It has been too long since the last post. It is not that I do not want to, it is just way too busy…
I just came across a small problem where a .NET application written by someone else caused an ugly Stack trace with a ORA-942 (Table or View does not exist) after being moved to the next environment. The error was not there on development, so they asked me what the problem could be. At first I did my compare of grants to the Role I am supposed to use for the application. The problem is that on development the generic application user has direct grants and it is one of these direct grants that cause the problem. Off course I could take away those grants from the generic user, but that would only make the development environment crash, too. I need to find that grant given to the user which is not in the role (there were many, but the application works fine except for one action, so I do not want to provide all those direct grants to the role, I just want to grant that missing grant) and later on see what happens with the application if I remove all the direct grants from the user and only have the role left.
Since I cannot see the code (and sometimes,the code may not even be available to you) but was able to reproduce the difference myself using that interface on Development and QA, this is what I did to find the statement that caused the problem:
- I started the application on development and navigated to the point where I am one click away from the Stack trace error on QA (called my ‘application action’ later on)
- As a DBA (or ask your DBA), I purged the shared pool on development (this is not something you will want to do on production that easy, since all nicely parsed SQL statements will be wiped out and have to be parsed again by the application which in practice may cause CPU problems and a slower application at least for a while): ALTER SYSTEM FLUSH shared_pool;
- I did my ‘application action’ in the application, which went fine because on development the issue is not there
- I selected all the statements from V$SQL where the parsing user is the generic Oracle user used in the application. Since the SQL area was empty before I clicked and it is now populated with at least the statements executed since the ‘application action’
- I copied all these statements into a SQL editor logged in as the generic user on QA and tried to parse them or even execute them…the ORA-942 will show up almost guaranteed. For me it did.
This is just one, unorthodox way of finding your missing privileges. Thought I share it with you.