OraFAQ Forum: Security » Role with execute package and drop table

Home » RDBMS Server » Security » Role with execute package and drop table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Role with execute package and drop table [message #400685]

Wed, 29 April 2009 04:55

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

Report message to a moderator

Re: Role with execute package and drop table [message #400698 is a reply to message #400685]

Wed, 29 April 2009 05:37

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

Report message to a moderator

Re: Role with execute package and drop table [message #400707 is a reply to message #400698]

Wed, 29 April 2009 05:45

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?

Report message to a moderator

Re: Role with execute package and drop table [message #400714 is a reply to message #400707]

Wed, 29 April 2009 06:11

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

Report message to a moderator

Re: Role with execute package and drop table [message #400715 is a reply to message #400698]

Wed, 29 April 2009 06:13

cookiemonster
Messages: 13973
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

Report message to a moderator

Re: Role with execute package and drop table [message #400722 is a reply to message #400715]

Wed, 29 April 2009 06:34

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 ??

Report message to a moderator

Re: Role with execute package and drop table [message #400723 is a reply to message #400722]

Wed, 29 April 2009 06:37

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Role with execute package and drop table [message #400727 is a reply to message #400685]

Wed, 29 April 2009 06:45

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

Report message to a moderator

Re: Role with execute package and drop table [message #400755 is a reply to message #400685]

Wed, 29 April 2009 07:33

cookiemonster
Messages: 13973
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.

Report message to a moderator

Re: Role with execute package and drop table [message #400761 is a reply to message #400727]

Wed, 29 April 2009 07:38

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	Problem in creating view in toad
Next Topic:	PRIVILEGES

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Oct 27 23:36:06 CDT 2025