This content has been marked as final. Show 7 replies
My db version: Oracle Database 11g Enterprise Edition Release 22.214.171.124.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
Now when I try to establish a relation between these two tables from schema A, I get an error
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);
I have some questions,
alter table emp add constraint fk_emp_to_dept foreign key (deptno) references B.dept (deptno); error: ORA-01031: insufficient privileges
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 on the above
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?
I did this but still getting same error
What am I doing wrong. Moreover I have come across articles saying " it is not possible to grant reference privilege via roles"
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
Edited by: 934451 on Feb 9, 2013 9:29 AM
SQL> grant all on dept to A;
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.
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"?
is something you should avoid.
SQL> grant all on dept to A;
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'.