Oracle 9i：同义表不存在？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/379672/
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 9i: Synonymed Table Does Not Exist?
提问by Mike Hofer
I've created a package that contains a stored procedure that I plan to invoke from a separate application. The stored procedure will return a sorted list of all the views and tables in the schema. To do that, it performs a simple select on the DBA_TABLES and DBA_VIEWS synonyms, as shown below:
我创建了一个包，其中包含我计划从单独的应用程序调用的存储过程。存储过程将返回模式中所有视图和表的排序列表。为此，它对 DBA_TABLES 和 DBA_VIEWS 同义词执行简单的选择，如下所示：
CREATE OR REPLACE
PACKAGE BODY TITAN_ENTITY AS
PROCEDURE GETSCHEMAOBJECTS (RESULTS IN OUT T_CURSOR)
IS
V_CURSOR T_CURSOR;
BEGIN
OPEN V_CURSOR FOR
SELECT 'T' OBJECTTYPE, TABLE_NAME OBJECTNAME
FROM DBA_TABLES
WHERE OWNER = 'SONAR5'
UNION ALL
SELECT 'V' OBJECTTYPE, VIEW_NAME OBJECTNAME
FROM DBA_VIEWS
WHERE OWNER = 'SONAR5'
ORDER BY OBJECTNAME;
RESULTS := V_CURSOR;
END GETSCHEMAOBJECTS;
END TITAN_ENTITY;
I have verified that the synonyms in question exist, and are public:
我已经验证有问题的同义词存在，并且是公开的：
CREATE PUBLIC SYNONYM "DBA_TABLES" FOR "SYS"."DBA_TABLES"
CREATE PUBLIC SYNONYM "DBA_VIEWS" FOR "SYS"."DBA_VIEWS"
My understanding is that, because they are public, I don't need any further permissions to get to them. If that understanding is incorrect, I wish someone would disabuse me of the notion and point me to more accurate data.
我的理解是，因为它们是公开的，所以我不需要任何进一步的权限来访问它们。如果这种理解是不正确的，我希望有人能消除我的误解，并为我指出更准确的数据。
Now here's my problem: I can open a worksheet in Oracle SQL Developer and select from these tables just fine. I get meaningful data just fine (567 rows, as a matter of fact). But when I try to execute the stored procedure, Oracle complains with a compilation error, as shown below:
现在这是我的问题：我可以在 Oracle SQL Developer 中打开一个工作表并从这些表中选择就好了。我得到了很好的有意义的数据（实际上是 567 行）。但是当我尝试执行存储过程时，Oracle 提示编译错误，如下所示：
Error(9,8): PL/SQL: SQL Statement ignored
Error(10,16): PL/SQL: ORA-00942: table or view does not exist
When I double-click on that second error message, SQL Developer takes me to the first FROM clause ("FROM DBA_TABLES").
当我双击第二条错误消息时，SQL Developer 将我带到第一个 FROM 子句（“FROM DBA_TABLES”）。
So I'm fairly stumped. I know SQL Server pretty well, and I'm new to Oracle, so please bear with me. If you could provide some clues, or point me in the right direction, I'd really appreciate it.
所以我很难过。我非常了解 SQL Server，而且我是 Oracle 的新手，所以请耐心等待。如果您能提供一些线索，或为我指明正确的方向，我将不胜感激。
Thanks in advance!
提前致谢！
回答by Raimonds Simanovskis
Use ALL_TABLES and ALL_VIEWS instead of DBA_TABLES and DBA_VIEWS. ALL_% views should be accessible to all users.
使用 ALL_TABLES 和 ALL_VIEWS 而不是 DBA_TABLES 和 DBA_VIEWS。所有用户都应该可以访问 ALL_% 视图。
回答by tuinstoel
If you select from a table or a view in a stored PL/SQL-procedure or a stored PL/SQL-function you need a direct grant. A grant via a database role isn't enough.
如果您从存储的 PL/SQL 过程或存储的 PL/SQL 函数中的表或视图中进行选择，您需要直接授权。通过数据库角色授予是不够的。
You probably need a direct grant on view dba_tables. (public) synonyms are just (public) synonyms. You need directly granted select rights.
您可能需要直接授权查看 dba_tables。（公共）同义词只是（公共）同义词。您需要直接授予选择权限。
See here: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:48704116042682#48798240264807
请参阅此处：http: //asktom.oracle.com/pls/asktom/f?p=100:11:0 ::::P11_QUESTION_ID: 48704116042682#48798240264807
回答by Dave Costa
Edit:Sorry, I glossed over the part where you seem to say that you can select from DBA_TABLES directly. Most likely the issue is that your privileges are granted through a role as someone else answered. But it's still worth explaining that your understanding of PUBLIC synonyms is incomplete, and that using ALL_TABLES would be better if it accomplishes what you need.
编辑：抱歉，我忽略了您似乎说可以直接从 DBA_TABLES 中选择的部分。最有可能的问题是您的权限是通过其他人回答的角色授予的。但是仍然值得解释的是，您对 PUBLIC 同义词的理解是不完整的，如果使用 ALL_TABLES 可以完成您的需要，它会更好。
The synonym being PUBLIC only means that all users can reference the synonym; it does not grant them any access to the object that the synonym refers to.
同义词为 PUBLIC 仅意味着所有用户都可以引用该同义词；它不会授予他们对同义词所指对象的任何访问权限。
What you would do to most directly solve this error is grant SELECT privilege on the SYS views to the user(s) that will run this procedure. However, I think that is a very bad idea.
要最直接地解决此错误，您将执行的操作是将 SYS 视图的 SELECT 权限授予将运行此过程的用户。但是，我认为这是一个非常糟糕的主意。
As suggested by Raimonds, consider whether you can get what you need from USER_TABLES or ALL_TABLES instead. What user is calling this procedure, and what access does that user have to SONAR5's tables?
正如 Raimonds 所建议的那样，请考虑是否可以从 USER_TABLES 或 ALL_TABLES 获得所需的内容。哪个用户正在调用此过程，该用户对 SONAR5 的表有什么访问权限？
Generally, if your application is interested in a table, presumably it has some privileges on it, in which case is should be listed in ALL_TABLES.
一般来说，如果你的应用程序对一个表感兴趣，想必它对它有一些特权，在这种情况下应该列在 ALL_TABLES 中。
