This discussion is archived
7 Replies Latest reply: Feb 11, 2013 6:14 AM by mtefft RSS

Primary - foreign key relationships between different schemas

937454 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    has user B granted access on DEPT table to user A ?
  • 4. Re: Primary - foreign key relationships between different schemas
    937454 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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'.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points