3

Which Oracle database role will allow a user to select from a table in another schema without specifying the schema identifier? i.e., as user A- Grant select on A.table to user B; B can then- "Select * from table" without specifying the 'A'. One of our databases allows this, the other returns a 'table or view does not exist' error.

Taryn
  • 242,637
  • 56
  • 362
  • 405

6 Answers6

7

You can create a synonym for this. Create a synonym named "CoffeeTable" on object "A.CoffeeTable". You can create a public synonym so everyone sees it like this, or just a synonym under user B.

Brett McCann
  • 2,469
  • 2
  • 27
  • 44
4

Just to double check that the schema you are using doesn't have a private synonym for the table (or a view as Leigh suggests) you could the following 

SELECT * FROM all_objects WHERE object_name = 'mytablename'

and look at the owner and object_type information.

hamishmcn
  • 7,843
  • 10
  • 41
  • 46
2

Maybe only the current_schema is different. Try:

alter session set current_schema=A

asalamon74
  • 6,120
  • 9
  • 46
  • 60
2

If there isn't a synonym, is there a view in schema B that selects from the table in schema A using the same name as the table? This would appear to be a locally referenced table in many ways.

Leigh Riffel
  • 6,381
  • 3
  • 34
  • 47
0

Brett is right. Synonyms are used for this. In fact there are cases where you do not know what will be the schema name in production. Maybe you use A for some schema name and A is already taken in some Oracle instance.

pero
  • 4,169
  • 26
  • 27
0

@erno - probably the reason that TOAD didn't show you the public synonym is because it filters the information shown in the list - I don't have toad in front of me but I think if you right click on tab you will get to the filtering options (eg "only show objects owned by the schema", "show public objects", "show system objects" etc)

hamishmcn
  • 7,843
  • 10
  • 41
  • 46