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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
Just create a procedure on the target schema which involves EXECUTE_IMMEDIATE to create tables (check for other DDLs!!). Then grant the source schema privilege to execute on procedure. This should do the trick without security concerns.
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:
Then the connection uses user B's authentication, but gets the permissions of user A.
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
You could just create a role with those permissions, and grant the role to whoever needs that ability.