question

I'd suggest working with the security model rather than against it.

Schemas are namespaces, and there should usually be a clear functional division between data in the schemas. In the usual case, data should provided from one schema to outside consumers via an API, not via direct access to the underlying objects. This can easily be managed with AUTHID DECLARER on PL/SQL subprograms.

Admittedly, this isn't always going to work, and sometimes you do need to open up an object to other users. There, roles make the management of privileges much easier, especially if there's a large matrix of users and permissions.

I'd take the view that if one user needs full and unfettered access to another user's schema, including to any objects that might be created there in the future, then something has gone badly wrong with your data model.

I suspect you know the basics, but to run through an example, first create two developer's accounts, and give them permission to create roles:

SQL> CREATE USER dev1 IDENTIFIED BY dev1 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

User created.

SQL> CREATE USER dev2 IDENTIFIED BY dev2 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

User created.

SQL> GRANT connect,resource TO dev1;

Grant succeeded.

SQL> GRANT connect,resource TO dev2;

Grant succeeded.

SQL> GRANT CREATE ROLE TO dev1;

Grant succeeded.

SQL> GRANT CREATE ROLE TO dev2;

Grant succeeded.

Setup a simple schema and a little data for dev1:

SQL> CONNECT dev1/dev1
Connected.
SQL> CREATE TABLE foo(a NUMBER, b VARCHAR2(200));

Table created.

SQL> CREATE TABLE bar (x NUMBER, y DATE);

Table created.

SQL> INSERT INTO foo VALUES (17,'wibble');

1 row created.

SQL> INSERT INTO bar VALUES (23,sysdate);

1 row created.

SQL> INSERT INTO bar VALUES (42,sysdate+1);

1 row created.

SQL> COMMIT;

Commit complete.

Now things get a little more interesting. We create a function with AUTHID DEFINER, and a couple of roles, give those roles appropriate permission and grant those roles to dev2 (remember, this is all being done as dev1, not as DBA).

SQL> CREATE OR REPLACE FUNCTION fun1(v_x NUMBER)
  2  RETURN DATE
  3  AUTHID DEFINER
  4  AS
  5   v_y DATE;
  6  BEGIN
  7  SELECT max(y) INTO v_y FROM bar WHERE x > v_x;
  8  RETURN v_y;
  9  END;
 10  /

Function created.

SQL> CREATE ROLE dev1exec;

Role created.

SQL> CREATE ROLE dev1sel;

Role created.

SQL> GRANT dev1sel TO dev2;

Grant succeeded.

SQL> GRANT dev1exec TO dev2;

Grant succeeded.

SQL> GRANT SELECT ON foo TO dev1sel;

Grant succeeded.

SQL> GRANT EXECUTE ON fun1 TO dev1exec;

Grant succeeded.

Now we connect as dev2 to see what effect this has had:

SQL> CONNECT dev2/dev2;
Connected.
SQL> COLUMN b FORMAT a10
SQL> SELECT * FROM dev1.foo;

         A B
---------- ----------
        17 wibble

SQL> SELECT * FROM dev1.bar;
SELECT * FROM dev1.bar
                   *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> SELECT dev1.fun1(30) FROM dual;

DEV1.FUN1(30)
---------------
08-APR-10

SQL>

Access to dev1.bar is purely through an API, which we've only been given execute permission on, but the AUTHID DEFINER clause means that we can see the data in dev1.bar even though we can't access the table directly.

Access to dev1.foo is via the role that was created and granted by dev1.

At no point did the DBA need to get involved in providing access to any objects. The developers can handle this themselves. A good database design should minimise the amount that you need unmanaged access to another schema. Where that permission does need to be given, it can be managed by the developers through well defined roles for who gets to access which objects.

In the extreme case, where all hope of good database design and user security has already been abandoned, rather than giving developers DBA access, you could have a stored procedure like:

CREATE PROCEDURE openaccess(username VARCHAR2)
AS
BEGIN
FOR i IN (SELECT owner,object_name FROM dba_objects WHERE owner=username AND object_type IN ('TABLE','VIEW')) LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON '||i.owner||'.'||i.object_name||' TO PUBLIC';
END LOOP;
FOR i IN (SELECT owner,object_name FROM dba_objects WHERE owner=username AND object_type IN ('PROCEDURE','FUNCTION')) LOOP
EXECUTE IMMEDIATE 'GRANT EXECUTE ON '||i.owner||'.'||i.object_name||' TO PUBLIC';
END LOOP;
END;
/

and arrange for it to be run whenever is appropriate.