One way to find that ORA-942 causing missing grant

Hi all,

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:

  1. 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)
  2. 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;
  3. I did my ‘application action’ in the application, which went fine because on development the issue is not there
  4. 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’
  5. 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.

3 thoughts on “One way to find that ORA-942 causing missing grant

  1. It’s better:
    alter system set events ‘942 trace name errorstack level 2’;
    And you’ll find the sql text in the tracefile generated.

    • Many thanks for this valuable reply. In our case here at the customer, we do not have access to the trace files that easily, but I love the tip. And I guess having the DBA be able to execute a rigid flush shared pool makes access to that trace file possible as well. Thanks. As said, it is just one unorthodox way, better ways exist, like you show!

    • Joaquín, I could actually use your tip today at another customer trying to find the statement that caused another error (ORA-00932). Great tip and when you have access to the trace files, a whole lot faster than the cumbersome way I described above 🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s