Oracle Privilege error

15 pts.
Oracle 10g
Oracle error messages
Oracle Permissions
I am using Oracle 10g and does the following. Scheduling a job (procedure call) from my application. Procedure is querying synonyms of another schema and the schema is given the select privilege on all synonyms. When the procedure is executed directly, it works fine. But when scheduled as a job , the querying of synonyms throw ORA1031: insufficient privileges error. Please help.

Answer Wiki

Thanks. We'll let you know when a new response is added.

My first guess would be that the user executing the job is not the same user that you use when testing the code. Thus the job executing user does not in fact have the correct privileges. Run a test job that does nothing but raise_application_error(-20999,’user-‘||user); and find out who he thinks he is when he runs your job.

My second guess is that there is once again a privilege constraint that oracle has implemented regarding jobs run via the job queue. As you recall, privileges behave differently under PLSQL than straight from SQLPLUS. If you are doing some kind of dynamic sql, you may need direct grants to the rlated objects rather than grants received via a role. If so, this should be documented in the relevant manual.

My third guess is that it is related to your use of synonyms from a different schema. I wager you suspect this as well or you would not have included this information. If this is so, then create your grants using the WITH GRANT OPTION (or whatever the current equivelant is these days).

My fourth guess is a wild one. I have not clue if this is true or not, but maybe the job queue executes its wrapper as a dynamic sql call. I never really paid much attention to it. If so then what I said for guess#2 above about direct grants would come into play.

Good luck, Kevin

Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Djeepp
    It sounds like the job might be owned by a different user than you are executing it as. Execute this query: select priv_user from dba_jobs where job = 'xxxx'; Does that user match the user you were executing it manually as?
    340 pointsBadges:
  • Dwaltr
    One other thing to check is to see what roles are granted to you as a user versus what grants are given directly to the user. PL/SQL when executed remotely on sees the grants that are granted directly to the user, not via role. So if you are querying dba_synonyms, you must grant select to dba_synonyms directly to the user and not via a role. -Dave
    900 pointsBadges:
  • Rajjo
    Thanks all. It worked. Problem was the underlying table for which the synonym is created in another schema did not have the select privilege for the current user. Thanks again -Rajjo
    15 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: