Re: Privileges Under Oracle 7.1

Richard G. Ramirez (ou79138_at_deere.com) wrote:

: >pixelp_at_ais.net (Shelley La Porte) wrote:
: >>I have an Oracle account with CREATE PROCEDURE and CREATE TRIGGER
: >>privileges.. but when I try to compile my procedure I get a PL/SQL
: >>error... number 00201 to be exact. Now this error means that an
: >>identifier has not been declared.. but the identifier is actually the
: >>name of a table owned by another user.
 

: Also, if you have SELECT through a role, Oracle will not
: let you create a stored object such as a procedure or a view.
 

: It is not a newbie. I had exactly the same problem. To make things
: worse,I was copying the procedure from Bobrowski's book and I
: could not figure out what was wrong.
 

: Richard

If this the same problem that I saw then it has something to do with dictionary cache and version 7.1

My scenario:

User A has table T (A.T)
User T has procedure B (T.B)
User X has EXEC priv on T.B

The user A who has EXEC privilege on T.B wants to use it in his own procedure. The procedure was created under 7.0 and worked OK, but it was to be modified, that is, re-created.

Now, an error occured telling about "undefined identifier" T.B, obviously interpreted as table T, column B.

And after the user X executes T.B and thus brings it to dictionary cache, the object name resolves correctly and user A can compile the procedute T.B without error messages.

--
Madis Ollisaar
m_ollisaar_at_crebit.ee