database Oracle 数据库角色 - 从没有模式标识符的模式中的表中选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/223238/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle database role - select from table across schemas without schema identifier
提问by asalamon74
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.
哪个 Oracle 数据库角色将允许用户在不指定模式标识符的情况下从另一个模式中的表中进行选择？即，作为用户 A- 将 A.table 上的选择授予用户 B；然后 B 可以-“从表中选择 *”而不指定 'A'。我们的一个数据库允许这样做，另一个返回“表或视图不存在”错误。
回答by Brett McCann
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.
您可以为此创建同义词。在对象“A.CoffeeTable”上创建一个名为“CoffeeTable”的同义词。您可以创建一个公共同义词，以便每个人都这样看到它，或者只是用户 B 下的一个同义词。
回答by hamishmcn
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
只是为了仔细检查您正在使用的架构没有表的私有同义词（或 Leigh 建议的视图），您可以执行以下操作
SELECT * FROM all_objects WHERE object_name = 'mytablename'
and look at the owner and object_type information.
并查看所有者和 object_type 信息。
回答by asalamon74
Maybe only the current_schema is different. Try:
也许只有 current_schema 不同。尝试：
alter session set current_schema=A
alter session set current_schema=A
回答by Leigh Riffel
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.
如果没有同义词，模式 B 中是否有视图使用与表相同的名称从模式 A 中的表中进行选择？在许多方面，这似乎是一个本地引用的表。
回答by pero
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.
布雷特是对的。为此使用同义词。事实上，在某些情况下，您不知道生产中的架构名称是什么。也许您将 A 用于某个模式名称，而 A 已在某些 Oracle 实例中使用。
回答by hamishmcn
@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 thinkif 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)
@erno - 可能 TOAD 没有向您显示公共同义词的原因是因为它过滤了列表中显示的信息 - 我面前没有蟾蜍，但我认为如果您右键单击选项卡，您将获得过滤选项（例如“仅显示架构拥有的对象”、“显示公共对象”、“显示系统对象”等）
