Home » SQL & PL/SQL » SQL & PL/SQL » Execute privilege (Oracle, 10g, Windows Server 2003)
Execute privilege [message #448304] Mon, 22 March 2010 12:24 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,

Consider following scenario

Schema Update Privilege on emp directly
User1 Yes
User2 No

I have created a package named pack in user1 schema which contains one procedure which updates table emp.

Now i executed the following in schema user1

Grant execute on pack to user2;

I connected as user2 and executed

Create Synonym pack for user1.pack;

Note that user2 doesn't have update privileges on table emp

Please tell me whether user2 can update emp through package pack

Regards,
Ritesh

Re: Execute privilege [message #448305 is a reply to message #448304] Mon, 22 March 2010 12:25 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Please tell me whether user2 can update emp through package pack
What do your own test reveal?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Execute privilege [message #448306 is a reply to message #448304] Mon, 22 March 2010 12:28 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
OK, we could tell you, but why? Seems to me you have all you need to test and find out for yourself. One of the real indicators of a good Oracle person is if they can figure out how to construct a test to learn what want on their own.

Again, you have done all the test scenario setup you need. Do the test and then let us know what you find out.

Kevin
Re: Execute privilege [message #448307 is a reply to message #448305] Mon, 22 March 2010 12:29 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,
I cannot test it on home machine since it does have oracle installed, hence i'll be very thankful to u if u give me the answer


Regards,
Ritesh
Re: Execute privilege [message #448309 is a reply to message #448307] Mon, 22 March 2010 12:33 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I cannot test it on home machine since it does have oracle installed, hence i'll be very thankful to u if u give me the answer

We are not in any hurry.
We can wait until you can run your own tests.
Re: Execute privilege [message #448310 is a reply to message #448307] Mon, 22 March 2010 12:33 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
getritesh wrote on Mon, 22 March 2010 12:29
Hi,
I cannot test it on home machine since it does have oracle installed

Do you mean "does not?"

The obvious response to this is, "why not then install oracle on your home machine?"
Re: Execute privilege [message #448311 is a reply to message #448307] Mon, 22 March 2010 12:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"u" cannot give you the answer as "u" is not a member of OraFAQ.

Regards
Michel

[Updated on: Mon, 22 March 2010 12:38]

Report message to a moderator

Re: Execute privilege [message #448313 is a reply to message #448309] Mon, 22 March 2010 12:38 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,

I tried on oracle application express but there i was not allowes to create another user for testing purpose, it said

ORA-01031: insufficient privileges

when i tried to execute

create user user2 identified by t;


I tried but was not successful, so please answer my query as it is urgent


Regards,
Ritesh
Re: Execute privilege [message #448314 is a reply to message #448313] Mon, 22 March 2010 12:41 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Urgent is relative. If you can convince me as to why it is urgent, I will tell you.

I have already constructed the test case and executed it (took all of 10 minutes).

Otherwise, you will have to wait till tomorrow to go to work and spend the 10 minutes like me to create a test case and try it.

Kevin
Re: Execute privilege [message #448315 is a reply to message #448313] Mon, 22 March 2010 12:41 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I tried but was not successful, so please answer my query as it is urgent

Since USER2 does not exist, the answer to question is moot.

To issue CREATE USER, the user trying to do so needs CREATE USER privilege.
Re: Execute privilege [message #448316 is a reply to message #448313] Mon, 22 March 2010 12:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you are at home why is it so urgent? Anyway, why it should be urgent for us? First read and follow the guidelines (how many times had we tell you this?)
If you are at home why can't you grant you the necessary privilege?

Regards
Michel
Re: Execute privilege [message #448317 is a reply to message #448314] Mon, 22 March 2010 12:46 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,
I have created the package on user1 and it is going to be tested on client side in USA, they (client) are going to test my package on another schema and they want my suggestions as to how will test it on another schema


Regards,
Ritesh
Re: Execute privilege [message #448318 is a reply to message #448317] Mon, 22 March 2010 12:48 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I have created the package on user1 and it is going to be tested on client side in USA, they (client) are going to test my package on another schema and they want my suggestions as to how will test it on another schema

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Execute privilege [message #448319 is a reply to message #448317] Mon, 22 March 2010 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Then do it yourself and don't rely on us. Who knows if we'll give you the correct answer?

Regards
Michel
Re: Execute privilege [message #448320 is a reply to message #448304] Mon, 22 March 2010 12:52 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
OK, this post is getting dull.

The answer is YES, when user2 executes the packaged procedure it will modify the table even though user2 has no actual direct grants on the table.

Quote:
1) this is clearly stated in the PLSQL documentation
2) this is easily verifiable with a simple test
3) you are most welcome on OraFAQ but next time we expect you to have downloaded and installed a version of Oracle on your HOMEPC and run your own tests before you ask us for help
4) better make sure I am not fibbing by trying this at work tomorrow.
Kevin
Re: Execute privilege [message #448321 is a reply to message #448320] Mon, 22 March 2010 12:56 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi Kevin,

Thank you very much

Regards,
Ritesh
Re: Execute privilege [message #448323 is a reply to message #448304] Mon, 22 March 2010 13:04 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Don't thank me yet.

I expect you to log in to ORAFAQ tomorrow with the results of your own tests to show us that you put out the effort on your own.

DO NOT DISAPPOINT US.

Kevin
Re: Execute privilege [message #448689 is a reply to message #448323] Wed, 24 March 2010 13:15 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member

DO NOT DISAPPOINT US.


I'm not surprised by lack of response.
Re: Execute privilege [message #449189 is a reply to message #448304] Sat, 27 March 2010 11:58 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I sent an email to the OP. Either he/she did not get it, or they do not care enough to reply to us to show us they did the work we asked them to do.

Kevin
Previous Topic: Commit V/S Rollback
Next Topic: View Insert
Goto Forum:
  


Current Time: Sun Dec 11 06:28:07 CST 2016

Total time taken to generate the page: 0.04542 seconds