Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two users A and B. I want to grant B the permission to create, drop, etc. all tables in A's schema. As far as I can see, I can grant B full access to all schemas not a specific one. Is this correct?

share|improve this question

2 Answers 2

You are correct that there is no way to grant a user create/drop/etc permissions on an entire schema. I suggest you look into proxy authentication. This basically involves altering user A to allow user B to proxy as A:

ALTER USER A GRANT CONNECT THROUGH B;

Then the connection uses user B's authentication, but gets the permissions of user A.

connect B[A]/Password@Database

This question was somewhat covered by my more specific question here.


Note on Roles: Roles work well for giving Object Privileges to another user since the privileges are tied to a specific object. While Roles can grant System Privileges, they apply either to the users own schema or to the entire database and therefore can't apply to another schema. For example, the user B could be granted CREATE TABLE which would allow it to create tables in its own schema or CREATE ANY TABLE which would allow it to create tables in any schema. These permissions could be granted directly or through a role, but the former wouldn't allow create privileges in the A schema. The latter would, but would also allow create privileges in any schema including sys, which would be a security concern.

share|improve this answer
    
Thanks for the answer. This is a pity of course. Basically, I don't understand why such a simple feature is not available in Oracle. –  Michael-O Feb 17 '12 at 15:09
2  
Just found this feature on a 12c wish list: dadbm.com/2012/03/oracle-database-12c-wish-list –  Leigh Riffel Aug 30 '12 at 17:33
    
I would highly prefer to decouple users from schemas. Just like in MySQL. –  Michael-O Aug 30 '12 at 18:04
    
The permission would have to exist for the decoupling to work, so perhaps it would be a first step in that direction. –  Leigh Riffel Aug 30 '12 at 20:25

You could just create a role with those permissions, and grant the role to whoever needs that ability.

share|improve this answer
    
How is that supposed to work? Leigh said that I cannot grant on an specfic entire schema. –  Michael-O Feb 17 '12 at 15:29
1  
A Role can only include permissions that can be granted directly. The user B could be granted CREATE TABLE which would allow it to create tables in its own schema or CREATE ANY TABLE which would allow it to create tables in any schema. These permissions could be granted directly or through a role, but the first is too narrow and the second too broad. –  Leigh Riffel Feb 17 '12 at 15:56
    
Leigh, exactly, none fits. –  Michael-O Feb 17 '12 at 18:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.