grant a constraint
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: grant a constraint

  1. #1
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282

    Is that possible to GRANT foreign key constraint to a user?
    I dont want to GRANT REFERENCES, i want to grant the constraint fk_cha


    F.


  2. #2
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    A constraint is a business rule. It is not a privilege. I guess, it means you can not grant a constraint.
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  3. #3
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282

    I suspected that. I used to write applications using delphi, now I`m learning Forms and I`m facing some stupid problems. I wrote a simple procedure to insert data in a table in When-button-Pressed trigger ... the table belongs to user A, and the user B has the permission to insert data in this table. When I fill in the text items and press the button with user A it works, but when I press the button connected with the user B, oracle forms raises something like 'can`t check exclusivity in a fk constraint' .... I gave to user B all the priviledges related to the table the fk referes to, and nothing. Do you know what`s going on? I just want to insert data using when-button-pressed and not the insert symbol from default&smartbar.

    Thanks in advance.
    F.

  4. #4
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    Assuming that FK table belongs to USERA, you need to do the following:

    1. Login as USERB and create a synonym for FK table in USERB schema

    CREATE SYNONYM {object_name} for USERA.{object_name}

    2. Then login as USERA and Grant select, insert on FK_table to USERB. You need only select privileges on FK Table.

    This way USERB has not only has access to the object but also has necessary privileges to select/insert data.

    Another simpler way is to create PUBLIC synonyms for all the tables you are using in your application and grant all to public. Then you will not have a problem whatever the user you are logged in as.


    [Edited by kris109 on 02-17-2002 at 10:59 AM]
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  5. #5
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282

    I did it already, thanks anyway. This is the message oracle returns when I try to insert through when-button-pressed trigger:

    FRM-40506: ORACLE ERROR: it's not possible to verify record's exclusivity.


    F.

  6. #6
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    This isn't problem with foreign key constraint.

    Processing error encountered while checking a record's primary key items for uniqueness.
    The table associated with the current block of the form does not exist, or you do not have authority to access the table.

    1. Check priviliges for all tables in application for user A and B again.
    2. (This is my suggest only) in foms applications use full objects names (schema.oblect). and u will have much less headace with grants and priviliges.

  7. #7
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282


    It's very strange. When I'm connected as USERA it works and when I connect as USERB it doesn't. I get the same error. I gave all the priviledges that Larry Ellinson could imagine to USERB and ... nothing.

    F.

  8. #8
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Try to execute select statments (that modeling Form's blocks) as USERB thru SQLPLUS.

  9. #9
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    Compare the privileges of USERA and USERB in user_tab_privs and user_sys_privs tables and see if something is missing for USERB. USERB needs SELECT ANY TABLE system privilege to select from another user's schema.

    [Edited by kris109 on 02-19-2002 at 04:36 PM]
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  10. #10
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282

    Shestakov, in sql/plus it works, so Kris109, I believe the problem is not with privileges. Might it be a problem with
    forms? (not discarding the problem should be me).

    thanks anyway.

    F.

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