Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a schema called SCHEMA1 with packages and tables that needs to be accessed by user SCHEMA2.

I want SCHEMA2 to have access for executing packages and look at source code. Also I would like to give SELECT access to all tables for SCHEMA2.

How can I do that? Thank you.

share|improve this question

1 Answer 1

up vote 3 down vote accepted

I answered a very similar question on stackoverflow.

Basically, it's this:

BEGIN
  FOR Rec IN (SELECT object_name, object_type FROM all_objects WHERE owner='SCHEMA1' AND object_type IN ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE')) LOOP
    IF Rec.object_type IN ('TABLE','VIEW') THEN
      EXECUTE IMMEDIATE 'GRANT SELECT ON SCHEMA1.'||Rec.object_name||' TO SCHEMA2';
    ELSIF Rec.object_type IN ('PROCEDURE','FUNCTION','PACKAGE') THEN
      EXECUTE IMMEDIATE 'GRANT EXECUTE ON SCHEMA1.'||Rec.object_name||' TO SCHEMA2';
    END IF;
  END LOOP;
END;
share|improve this answer
    
+1 for an excellent script. I would prefer, though, that the script grant the privileges to a role and that this role gets granted to Schema2. Otherwise, administration gets quite hairy because there will inevitably be multiple users that will need the same set of permissions. –  Justin Cave Aug 4 '09 at 18:56
    
I try to run it from Toad and I get errors, any idea why? (I'm such a lousy DBA) –  Philippe Aug 4 '09 at 19:06
    
@Phillipe: What kind of errors? @Justin: Of course, roles are superior when there's any chance that multiple users will become involved. The problem is that administration is going to be hairy anyway - any time an object is created in schema1, schema2 will have to be granted access. It would be nice to be able to grant all on a schema to a user, rather than on an object by object basis. –  DCookie Aug 4 '09 at 20:22
    
Error at line 1 ORA-06550: line 2, column 157: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 2, column 14: PL/SQL: SQL Statement ignored ORA-06550: line 4, column 82: PLS-00103: Encountered the symbol ";" when expecting one of the following: loop –  Philippe Aug 4 '09 at 20:32
1  
There was a missing right paren on the FOR statement. Try it now. –  DCookie Aug 4 '09 at 20:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.