Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    14

    Question Unanswered: Issue with Synonym

    Hi,

    I have a database with two users in it.

    1) Test_app_data
    2) Test_app_user

    I created all the tables in 'test_app_data' and created synonym for all the tables so that it can be accessed with the same name by 'test_app_user'.I used the following query.

    CREATE SYNONYM test_app_user.table1 FOR table1;

    However after creating the synonyms, if I login as 'test_app_data' and
    query the DB with the following query

    select * from all_synonyms;

    I can see only few synonyms created.However if I run the query after logging in using 'test_app_user', I can see all the synonyms created.

    Iam totally confused . Could someone please explain why few tables are shown in one schema alone.

    Thanks and Regards

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You need to create the synonyms when being logged in as test_app_data

    An ugly alternative would be to use PUBLIC synonyms, then it doesn't matter with which user you are logged in

  3. #3
    Join Date
    Mar 2008
    Posts
    14
    Quote Originally Posted by shammat
    You need to create the synonyms when being logged in as test_app_data

    An ugly alternative would be to use PUBLIC synonyms, then it doesn't matter with which user you are logged in

    I created the synonym by logging in as 'test_app_data' only.
    But when I query the DB, I can find few tables only under 'test_app_user'.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by vijavip
    I created the synonym by logging in as 'test_app_data' only.
    But when I query the DB, I can find few tables only under 'test_app_user'.
    Sorry I read it the wrong way round. test_app_user must be the owner of the synonym, you you should create them being logged in as test_app_user. Not sure I your way of qualifying the owner really works (apparently not the way you did it), so you should retry while being logged in as the user who should own the synonyms.

  5. #5
    Join Date
    Mar 2008
    Posts
    14
    Quote Originally Posted by shammat
    Sorry I read it the wrong way round. test_app_user must be the owner of the synonym, you you should create them being logged in as test_app_user. Not sure I your way of qualifying the owner really works (apparently not the way you did it), so you should retry while being logged in as the user who should own the synonyms.

    Thanks for the suggestion.

    The issue was resolved after I gave (Grant) 'Select' option to that table for 'test_app_user'. Now as you had suggested, I created the synonym in 'test_app_user' and it was appearing in both the user tables query results.

    So I believe giving proper Grant privilage was the issue.

    Many thanks once again.

Posting Permissions

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