Create a New user ORACLE with full access to an specific SCHEMA

Question

How can create a New user in ORACLE with full access (alter, delete, select, inset, debug, etc) to an only one specific SCHEMA.

Answer 1

Cannot be done. In Oracle privileges are granted on specific objects, unless you have the highpowered ANY privileges, which grant access to any object in any schema.

This is one of those things which seems quite annoying but actually is quite sound. There is no good business reason for granting privileges on all on the objects in a schema en masse. Either

1. the second schema really needs just a sub-set of privilges on a sub-set of objects; or
2. the second schema is entirely unnecessary.

Now it may be that the sub-set in the first instance is a very large sub-set. But laziness is not an excuse for poor security practices.

What we can do is generate the grant statements from the data dictionary:

select 'grant select on '||table_name||' to B'
from   user_tables
/

(for a script to be run by user A).

This is still better than granting privileges on the schema, because it means at least any new object added by user A will not automatically be propagated to B without an additional action and, hence, without some additional thought as to whether it is appropriate.

Answer 2

You could use a PROXY user. Its not quite the same thing as it allows one database user to connect as another but using their own password. You can therefore have multiple users, each with their own password, using the same schema.

An example of the code is here.