DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Grant roles on ORACLE 9.2.0???

  1. #1
    Join Date
    Feb 2001
    Posts
    39

    Angry

    Hi All,
    I was wondering if there are changes made to granting roles on ORACLE 9.2.0? I imported a user and his schema into ORACLE 9.2.0 with the option grants = Y after having imported it from ORACLE 9.0.1 with the same option grants = Y.

    However when I try to select from any of the tables under that schema I get the error "ORA-00942: table of view does not exist. I have granted select on all the tables in that user's schema and still can select from it unless I put the schema name then the table name.

    Any Ideas? Thanks in advance.

    Mee
    God Bless

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    that's synonym problem not grants

  3. #3
    Join Date
    Sep 2002
    Posts
    20
    I have run in to similar problems in 8i also and sometimes the solution has been to grant select on every single table, one by one. This has been an issue when installing third party Oracle tools where some of the objects are owned by sys.

    I don't know if it is of any help?

    /Beppo

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    most probably is you did an user import, an user import only import GRANTS on owner´s tables not SYS

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by smallpin
    ... select from it unless I put the schema name then the table name.
    As pando said, it has nothing to do with grants and roles, its the SYNONYMS isue. In your 9.0.1 you probably have public synonyms for those tables, but public synonyms were not imported into 9.2 database because you did user mode import.

    You must create synonyms for those tables if you don't want to use schema name prefixes.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Feb 2001
    Posts
    39
    Thanks for you help guys. I have created the sysnonyms for the tables and everything works fine now. However I will like to know how I can get around the problem of exp/imp a user and still exp/imp the the synonyms? Do I have to exp/imp the sys user in order to get the synonyms? Thanks in advance.

    Dee.
    God Bless

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    synonyms has to be created under schema who is going to QUERY the tables

    in your case you are not doing that, you are not importing anything to the user who is querying

    imagine

    userA is the one who is going to query userB's objects

    you import into userB

    if userA wants to query userB's tables he has to create private synonyms

    unless you import into userA private synonym definitions from other database

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