Home » RDBMS Server » Security » Role with execute package and drop table
Role with execute package and drop table [message #400685] Wed, 29 April 2009 04:55 Go to next message
maciego
Messages: 11
Registered: January 2009
Junior Member
Hi,

I work on Oracle 9.2 and want to create new user add him a role with grant execute on specified package.
If I thing right with that option if in specified package will be 'drop table' statement new user will have rights to execute drop.
Is there any way to block dropping inside package ??

Thanks for any help
Re: Role with execute package and drop table [message #400698 is a reply to message #400685] Wed, 29 April 2009 05:37 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Quote:
I work on Oracle 9.2 and want to create new user add him a role with grant execute on specified package.


Yes It's possible.
Quote:

If I thing right with that option if in specified package will be 'drop table' statement new user will have rights to execute drop.


no.

Babu
Re: Role with execute package and drop table [message #400707 is a reply to message #400698] Wed, 29 April 2009 05:45 Go to previous messageGo to next message
maciego
Messages: 11
Registered: January 2009
Junior Member
Thans for answer...

Then what I can call with execute grant on package?
- can I call a another package procedure from procedure of granted package?
- can I call procedure inserting data to table?


Re: Role with execute package and drop table [message #400714 is a reply to message #400707] Wed, 29 April 2009 06:11 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

What exactly you want??

You can grant "Execute" privilege to another user to run packages or procedures

Babu
Re: Role with execute package and drop table [message #400715 is a reply to message #400698] Wed, 29 April 2009 06:13 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
gentlebabu wrote on Wed, 29 April 2009 11:37
Quote:

If I thing right with that option if in specified package will be 'drop table' statement new user will have rights to execute drop.


no.

Babu



Ummmm - Yes.
The user won't be able issue a direct drop command but they will be able to execute the procedure and have it run the drop.

@maciego

Have a read of this:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/subprograms.htm#LNPLS00809
Re: Role with execute package and drop table [message #400722 is a reply to message #400715] Wed, 29 April 2009 06:34 Go to previous messageGo to next message
maciego
Messages: 11
Registered: January 2009
Junior Member
@cookiemonster

..then is there a way to block possibility to run a 'non direct' drop inside this procedure ??
Re: Role with execute package and drop table [message #400723 is a reply to message #400722] Wed, 29 April 2009 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you describe exactly what is your problem and not ask for a small part of it maybe we should be able to efficiently answer you.

Regards
Michel
Re: Role with execute package and drop table [message #400727 is a reply to message #400685] Wed, 29 April 2009 06:45 Go to previous messageGo to next message
maciego
Messages: 11
Registered: January 2009
Junior Member
I have a user with his schema who has a package with procedures and functions. I am trying to specify a role for another user to be able use this package (work ona first user tables and packages via this package) but not be able to drop anything.

Regards
maciego
Re: Role with execute package and drop table [message #400755 is a reply to message #400685] Wed, 29 April 2009 07:33 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
use invokers rights - see the link I posted.
or move the code you don't want them to run into a different package.
Re: Role with execute package and drop table [message #400761 is a reply to message #400727] Wed, 29 April 2009 07:38 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not clear if you want the user (which one?) be able to drop table or not with the procedure (why the package can drop a table? which one?)
So you don't explain the ROOT problem, just the problem you have to implement what you think at the point you are.
Maybe (most likely) the former steps (design) is wrong.

Regards
Michel
Previous Topic: Problem in creating view in toad
Next Topic: PRIVILEGES
Goto Forum:
  


Current Time: Thu Mar 28 08:46:22 CDT 2024