Oracle 9i：同义词表不存在？
我创建了一个包，其中包含一个存储过程，我计划从单独的应用程序调用该存储过程。 存储过程将返回架构中所有视图和表的排序列表。 为此，它对 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;
我已验证相关同义词是否存在，并且是公开的：
CREATE PUBLIC SYNONYM "DBA_TABLES" FOR "SYS"."DBA_TABLES"
CREATE PUBLIC SYNONYM "DBA_VIEWS" FOR "SYS"."DBA_VIEWS"
我的理解是，因为它们是公开的，所以我不需要任何同义词获得他们的进一步许可。 如果这种理解不正确，我希望有人能纠正我的观点并为我指出更准确的数据。
现在我的问题是：我可以在 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
当我双击第二条错误消息时，SQL Developer 将我带到第一个 FROM 子句（“FROM DBA_TABLES”）。
所以我很困惑。 我非常了解 SQL Server，而且我是 Oracle 的新手，所以请耐心等待。 如果您能提供一些线索，或者为我指明正确的方向，我将非常感激。
提前致谢！
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:
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:
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").
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.
Thanks in advance!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果您从存储的 PL/SQL 过程或存储的 PL/SQL 函数中的表或视图中进行选择，则需要直接授权。 通过数据库角色授予是不够的。
您可能需要对视图 dba_tables 进行直接授予。 （公共）同义词只是（公共）同义词。 您需要直接授予选择权限。
请参阅此处： http: //asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:48704116042682#48798240264807
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.
You probably need a direct grant on view dba_tables. (public) synonyms are just (public) synonyms. You need directly granted select rights.
See here: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:48704116042682#48798240264807
编辑：抱歉，我掩盖了您似乎说可以直接从 DBA_TABLES 中进行选择的部分。 最有可能的问题是您的权限是通过其他人回答的角色授予的。 但仍然值得解释一下，您对 PUBLIC 同义词的理解是不完整的，如果它能满足您的需要，使用 ALL_TABLES 会更好。
同义词为PUBLIC仅表示所有用户都可以引用该同义词； 它不会授予他们对同义词引用的对象的任何访问权限。
要最直接地解决此错误，您要做的就是向将运行此过程的用户授予 SYS 视图的 SELECT 权限。 但是，我认为这是一个非常糟糕的主意。
按照 Raimonds 的建议，考虑是否可以从 USER_TABLES 或 ALL_TABLES 获得所需的内容。 哪个用户正在调用此过程，该用户对 SONAR5 表有什么访问权限？
一般来说，如果您的应用程序对某个表感兴趣，那么它可能对该表具有一些权限，在这种情况下，应该在 ALL_TABLES 中列出该表。
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.
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.
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.
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?
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 和 ALL_VIEWS 而不是 DBA_TABLES 和 DBA_VIEWS。 所有用户都应该可以访问 ALL_% 视图。
Use ALL_TABLES and ALL_VIEWS instead of DBA_TABLES and DBA_VIEWS. ALL_% views should be accessible to all users.