2

I'm having some trouble understanding what's possible and what's not possible in Oracle when it comes to multi-schema setups. Let's say I have two schemata A and B:

-- with user SYS connect as SYSDBA
-- note: ALL PRIVILEGES are granted for simplicity in the scope of this question.
--       real life databases would have more fine-grained grants...
create user A identified by A;
grant all privileges to A;

create user B identified by B;
grant all privileges to B;

-- with user A
create table A.REFERENCED_TABLE (
  ID number(7) not null,
  constraint REFERENCED_TABLE_PK primary key (ID)
);

-- with user A or B
create table B.REFERENCING_TABLE (
  A_ID number(7) not null,
  constraint REFERENCING_TABLE_FK 
    foreign key (A_ID) 
    references A.REFERENCED_TABLE(ID)
    on delete cascade
);

But the above statement causes

ORA-01031: insufficient privileges

How can I make a table from one schema reference a table from another schema? Is there some GRANT still missing? Is this even possible?

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509

2 Answers2

4

There are 2 different kinds of privileges: System privs & object privs.

GRANT ALL PRIVILEGES TO user;

will grant all system privs to the user and should be used very very carefully!

GRANT ALL ON table TO user;

will grant SELECT, INSERT etc on a table (ie an object) to the user.

So you'll need to do a...

GRANT ALL ON a.referenced_table TO b;

...after the CREATE TABLE A.REFERENCED_TABLE statement for the above to work.

cagcowboy
  • 30,012
  • 11
  • 69
  • 93
  • Great, thanks! I hadn't thought of the necessary grant to the referenced table. Good hint with the system privileges. In my case, this is just a test database, though, so `GRANT ALL PRIVILEGES` is fine. – Lukas Eder Feb 05 '12 at 12:21
  • 2
    @LukasEder: If you don't want to grant all privilege, it should be enough to grant `SELECT` and `REFERENCES` –  Feb 05 '12 at 12:34
  • @Ben: I kept the question short on purpose. I needed an integration test case situation with two tables in two schemata, so that I can query the dictionary views for them. I'm doing this for http://www.jooq.org, a SQL abstraction library for Java, so the potential "live" environments is really irrelevant in this very situation. I updated the question with an additional comment – Lukas Eder Feb 05 '12 at 13:09
3

Grant all is too much for most enterprise environments. Use Grant references instead.

Grant references on schema.tablename to target_schema or user;

Henry Wycislo
  • 51
  • 3