Home » RDBMS Server » Security » New procedures not working even after granting EXECUTE
New procedures not working even after granting EXECUTE [message #474113] Thu, 02 September 2010 15:44 Go to next message
rkhatiwala
Messages: 177
Registered: April 2007
Senior Member
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

Hi,
I have created two new procedures. When I log in as ADMIN, I can execute these procedures.. but when i log in as WC_GUEST or WC_TECH, I cannot.. One of the procedure name is WCL_RPRTD_PARTS_REP_SEARCH, to which, I have given this:
grant execute on WCL_RPRTD_PARTS_REP_SEARCH to WC_GUEST,WC_TECH;


Also, I have changed the ROLE of ADMIN, to the same role as these two users and tried.. it works fine.

What else I should check ?

Thanks.

Re: New procedures not working even after granting EXECUTE [message #474115 is a reply to message #474113] Thu, 02 September 2010 17:09 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
You have code. We don't.
You have tables. We don't
You have error message or results. We don't.
You claim you have a problem, but exact what it is you do not post it.
>"I cannot"
Above is less than informative.
>When I log in as ADMIN
>I have changed the ROLE of ADMIN
What does line above mean?

PLEASE to NOT describe what you think you are doing.
use COPY & PASTE so we can see what you do & how Oracle responds.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: New procedures not working even after granting EXECUTE [message #474138 is a reply to message #474113] Fri, 03 September 2010 02:04 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
What error you got when you tried to execute WC_GUEST or WC_TEC?
did you specify the schema name while trying to execute it??
Did you create a synonym for the procedure ?

Regards
Ved
Re: New procedures not working even after granting EXECUTE [message #474205 is a reply to message #474113] Fri, 03 September 2010 08:58 Go to previous messageGo to next message
rkhatiwala
Messages: 177
Registered: April 2007
Senior Member
This is the error I get:

WCL_RPRTD_PARTS_REP_SEARCH: PROCEDURE DOESN'T EXIST

I logged in as user RKHATIWALA, which has a role of GUEST user, which can only execute the procedure.
By changing the role to ADMIN, I meant that , I changed the role from GUEST to ADMIN for user RKHATIWALA. and ADMIN has all the rights on that procedure.

Thanks.
Re: New procedures not working even after granting EXECUTE [message #474207 is a reply to message #474205] Fri, 03 September 2010 08:59 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
I don't know what you have.
I don't know what you do.
I don't know what you see.
It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: New procedures not working even after granting EXECUTE [message #474369 is a reply to message #474205] Mon, 06 September 2010 00:19 Go to previous message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Did you try to execute the procedure by specifying the schema name where the procedure has been created?
exec <schama_name>.test

what is the output when you run the below sql?
select owner,object_name,status
from all_objects 
where object_type='PROCEDURE' 
      and object_name='WCL_RPRTD_PARTS_REP_SEARCH';

Regards
Ved

[Updated on: Mon, 06 September 2010 00:58] by Moderator

Report message to a moderator

Previous Topic: ORA-00942 Table or View does not exists
Next Topic: Prevent users from updating tables
Goto Forum:
  


Current Time: Thu Oct 17 06:24:05 CDT 2019