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;

Answer 2

There are many things to consider. When you say access, do you want to prefix the tables with the other users name? You can use public synonyms so that you can hide the original owner, if that is an issue. And then grant privs on the synonym.

You also want to plan ahead as best you can. Later, will you want Frank to be able to access Alice's schema as well? You don't want to have to regrant privileges on N number of tables. Using a database role would be a better solution. Grant the select to role "ALICE_TABLES" for example and when another user needs access, just grant them privilege to the role. This helps to organize the grants you make inside the DB.

Answer 3

thanks, it is working as per a requirement

Answer 4

Another solution if you have different owner:

BEGIN

  FOR x IN (SELECT owner||'.'||table_name ownertab
            FROM   all_tables
            WHERE  owner IN ('A', 'B', 'C', 'D'))
  LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON '||x.ownertab||' TO other_user';
  END LOOP;
END;