Can I create primary key, foreign key cross schema?

[mzahid]

How can I reference columns of a table in different schema?
Is there a GRANT that allow user to reference any columns in any table in any schema if granted?

Thanks,

Mohammad Zahid
Software Engineer
Vancouver, Canada.
e-mail : mzahid@shaw.ca

[OracleDoc]

Yep.

grant {priv} (dname,loc)
on dept
to scott,manager;

[bhallar]

Pls read the following example
------------------------------

To grant to user SCOTT the REFERENCES privilege on the employee_id column and the UPDATE privilege on the employee_id, salary, and commission_pct columns of the employees table in the schema hr, issue the following statement:

GRANT REFERENCES (employee_id),
UPDATE (employee_id, salary, commission_pct)
ON hr.employees
TO SCOTT;

SCOTT can subsequently update values of the employee_id, salary, and commission_pct columns. SCOTT can also define referential integrity constraints that refer to the employee_id column. However, because the GRANT statement lists only these columns, SCOTT cannot perform operations on any of the other columns of the employees table.

For example, SCOTT can create a table with a constraint:

CREATE TABLE dependent

(dependno NUMBER,
dependname VARCHAR2(10),
employee NUMBER
CONSTRAINT in_emp REFERENCES SCOTT.employees(employee_id) );


The constraint in_emp ensures that all dependents in the dependent table correspond to an employee in the employees table in the schema hr.

[mzahid]

Thank you for quick response. I know how to grant references to user at column level to another user.

I want to find out is if there is any or such privileges that can be granted at schema level that would allow reference any tables in the database.


This is just an hypothetical example: GRANT REFERENCES TO USER ABC;

Have anyone experienced this scenario in there environemnt?

[OracleDoc]

I understand what your saying but I don't see the problem with it.

because the GRANT statement lists only these columns, SCOTT cannot perform operations on any of the other columns of the employees table.

Unless I misunderstood why can't you just add other columns to the grant statement?


I also may have jumped over my head on this as well...

[bhallar]

grant SELECT ANY TABLE, UPDATE ANY TABLE, INSERT ANY TABLE, DELETE ANY TABLE to SCOTT;

That would allow reference any tables in the database as far as DML operation is concerned.

[mzahid]

No, it does not. I granted the privileges suggested in the previous update and did not work. I still cannot build reference link to table in another schema without granting privileges at the object level.

I am looking for a way to grant privileges at schema level to be able to build reference to column in any schema in the database.

Thanks again!

- Mohammad Zahid
Software Engineer.
Vancouver, Canada.

[slimdave]

Originally posted by mzahid
I am looking for a way to grant privileges at schema level to be able to build reference to column in any schema in the database.

Can't do it -- you have to specify the grant at the object level.