7 Replies Latest reply: Feb 11, 2013 8:14 AM by mtefft RSS

    Primary - foreign key relationships between different schemas

    937454
      Hi,
      My db version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

      I have two schemas A, B withing same database D.

      I create a table emp in A
      I create a table dept in B as below
      In schema A
      create table emp (
          eno number(2),
          ename varchar2(10),
          deptno number(2));
      alter table emp
      add constraint pk_emp
      primary key (eno);
      
      In schema B
      create table dept (
          deptno number(2),
          dname varchar2(15));
      alter table dept
      add constraint pk_dept
      primary key (deptno);
      Now when I try to establish a relation between these two tables from schema A, I get an error
      alter table emp
      add constraint fk_emp_to_dept
      foreign key (deptno)
      references B.dept (deptno);
      
      error:
      ORA-01031: insufficient privileges
      I have some questions,
      What privileges should I give the schema A, B to establish these table relations?
      Is this a good approach, to have related tables spread across schemas? We have several tables and thought it might be good to segregate tables into different schemas?
      But are there challenegs with this approach? performance? coding complexity? Or is it ok?
      Please advice.
        • 1. Re: Primary - foreign key relationships between different schemas
          sb92075
          934451 wrote:
          Hi,
          My db version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

          I have two schemas A, B withing same database D.

          I create a table emp in A
          I create a table dept in B as below
          In schema A
          create table emp (
          eno number(2),
          ename varchar2(10),
          deptno number(2));
          alter table emp
          add constraint pk_emp
          primary key (eno);
          
          In schema B
          create table dept (
          deptno number(2),
          dname varchar2(15));
          alter table dept
          add constraint pk_dept
          primary key (deptno);
          Now when I try to establish a relation between these two tables from schema A, I get an error
          alter table emp
          add constraint fk_emp_to_dept
          foreign key (deptno)
          references B.dept (deptno);
          
          error:
          ORA-01031: insufficient privileges
          I have some questions,
          What privileges should I give the schema A, B to establish these table relations?
          Is this a good approach, to have related tables spread across schemas? We have several tables and thought it might be good to segregate tables into different schemas?
          But are there challenegs with this approach? performance? coding complexity? Or is it ok?
          Please advice.
          http://docs.oracle.com/cd/E11882_01/timesten.112/e21642/privileges.htm#BABIDBFC

          REFERENCES privilege
          • 2. Re: Primary - foreign key relationships between different schemas
            937454
            Is this a good approach, to have related tables spread across schemas? We have several tables and thought it might be good to segregate tables into different schemas? 
            But are there challenegs with this approach? performance? coding complexity? Or is it ok?
            Please advice on the above
            REFERENCES privilege
            I did this but still getting same error
            SQL> create role ref;
            Role created.
            SQL> grant references on B.dept to ref;
            Grant succeeded.
            SQL> grant ref to A;
            Grant succeeded.
            
            alter table emp
            add constraint fk_emp_to_dept
            foreign key (deptno)
            references B.dept (deptno); 
            
            error:
            ORA-01031: insufficient privileges
            What am I doing wrong. Moreover I have come across articles saying " it is not possible to grant reference privilege via roles"
            http://www.orafaq.com/forum/t/85350/2/
            http://www.orafaq.com/forum/t/142576/2/

            Edited by: 934451 on Feb 9, 2013 9:29 AM
            • 3. Re: Primary - foreign key relationships between different schemas
              sb92075
              has user B granted access on DEPT table to user A ?
              • 4. Re: Primary - foreign key relationships between different schemas
                937454
                SQL> grant all on dept to A;

                Grant succeeded.

                Now it works, I can create relationship between tables.
                Thank you sb.

                I realize there are things to consider like granting priv between schemas for the table relations to work.
                But I lack experience, to judge if this approach is a good one ,to have tables spread across schemas within same database.
                Please provide some reference material or advice on this part.
                • 5. Re: Primary - foreign key relationships between different schemas
                  sb92075
                  in Oracle everything is forbidden; except that which is explicitly granted.

                  regarding privileges & roles; privileges acquired via role do not apply within named PL/SQL procedures.


                  But I lack experience, to judge if this approach is a good one ,to have tables spread across schemas within same database.
                  quantify "good".
                  which metric measures "good"?

                  It depends.
                  • 6. Re: Primary - foreign key relationships between different schemas
                    937454
                    I understand it is a vague question. It depends on the application, database design, volume of data a lot of other variables to consider what is "good".
                    I have do some experiments to understand, what is best for my application.
                    • 7. Re: Primary - foreign key relationships between different schemas
                      mtefft
                      This:
                      SQL> grant all on dept to A;
                      is something you should avoid.
                      Grant only what you need. To do that, you need to know what it is you need to grant. You need to grant the REFERENCE privilege.
                      For other reasons, you may need to grant SELECT or other privielges. But don't GRANT ALL.

                      As for the idea of using multiple schemas: this is something that I encourage, because schemas tend to get more and more objects added to them over time. A schema with many, many objects is less maintainable, because within a schema no grants are needed. Everything can touch everything else. And eventually, everything within a schema will touch everything else within that schema.

                      It is better to identify and create well-bounded schemas as early as possible, with defined interactions and very-limited grants. If you find yourself doing inter-schema grants for the majority of your tables (possibly even 'any' of your tables) then you should re-evaluate your schema boundaries - you may have made them 'too small'.