Can I create primary key, foreign key cross schema?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Can I create primary key, foreign key cross schema?

  1. #1
    Join Date
    Jan 2001
    Location
    Vancouver, Canada
    Posts
    87

    Can I create primary key, foreign key cross schema?

    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
    Mohammad Zahid
    Software Developer
    Database Management Applications.
    Vancouver, Canada
    E-mail: mzahid@shaw.ca

  2. #2
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Yep.

    grant {priv} (dname,loc)
    on dept
    to scott,manager;
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  3. #3
    Join Date
    Jun 2001
    Location
    California
    Posts
    124
    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.

  4. #4
    Join Date
    Jan 2001
    Location
    Vancouver, Canada
    Posts
    87
    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?
    Mohammad Zahid
    Software Developer
    Database Management Applications.
    Vancouver, Canada
    E-mail: mzahid@shaw.ca

  5. #5
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    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...
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  6. #6
    Join Date
    Jun 2001
    Location
    California
    Posts
    124
    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.

  7. #7
    Join Date
    Jan 2001
    Location
    Vancouver, Canada
    Posts
    87
    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.
    Mohammad Zahid
    Software Developer
    Database Management Applications.
    Vancouver, Canada
    E-mail: mzahid@shaw.ca

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width