snippetModerate
Grant create permission on a specific schema in Oracle 11g
Viewed 0 times
11gcreatepermissiongrantspecificoracleschema
Problem
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?
Solution
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
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@DatabaseThis 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.Code Snippets
ALTER USER A GRANT CONNECT THROUGH B;connect B[A]/Password@DatabaseContext
StackExchange Database Administrators Q#13320, answer score: 11
Revisions (0)
No revisions yet.