Correct way to give users access to additional schemas in Oracle

Question

I have two users Bob and Alice in Oracle, both created by running the following commands as sysdba from sqlplus:

   create user $blah identified by $password;
   grant resource, connect, create view to $blah;

I want Bob to have complete access to Alice's schema (that is, all tables), but I'm not sure what grant to run, and whether to run it as sysdba or as Alice.

Happy to hear about any good pointers to reference material as well -- don't seem to be able to get a good answer to this from either the Internet or "Oracle Database 10g The Complete Reference", which is sitting on my desk.

Answer 1

AFAIK you need to do the grants object one at a time.

Typically you'd use a script to do this, something along the lines of:

SELECT 'GRANT ALL ON '||table_name||' TO BOB;'
FROM   ALL_TABLES
WHERE  OWNER = 'ALICE';

And similar for other db objects.

You could put a package in each schema that you need to issue the grant from which will go through all call each GRANT statement via an EXECUTE IMMEDIATE.

e.g.

   PROCEDURE GRANT_TABLES
   IS
   BEGIN

      FOR tab IN (SELECT table_name
                  FROM   all_tables
                  WHERE  owner = this_user) LOOP
         EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON '||tab.table_name||' TO other_user';
      END LOOP;
   END;

-- Note: This only covers access to tables. Packages/package bodies, functions etc requires additional solution.