grant drop table priviledge on another scehma in oracle

Can i grant drop table for schema 1 to schema 2 without giving a global priviledge?
pcombAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
You cannot.  The privilege is DROP ANY TABLE.  Which would give privilege to all tables in all schemas.

What you can do is create a procedure in SCHEMA1 that would do the drop and then grant execute privilege on that procedure to SCHEMA2.  Do not allow the table name to be passed in, it would have to be hard coded in the procedure, otherwise it opens it up to dropping anything and in theory it could allow SCHEMA2 to drop tables owned by others if SCHEMA1 has privileges to drop them.  It would also open it up to SQL injection.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
Just because I don't think so doesn't mean there isn't.

What I've seen done in the past is:
Create a procedure in schema1 that drops the table then grant execute on that procedure to schema2.

Maybe some other Experts know of a way.
0
sdstuberCommented:
not directly

you could grant "drop any table" and then create a ddl trigger to block attempts that are for the wrong schema.

or you could create a procedure within the target schema (or owned by another user that has "drop any table")
and have that procedure do the drop within the target schema
then grant access to that procedure
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.