Oracle创建视图问题
我以 dba 帐户登录，我想在 User1 的架构中创建一个视图，但从 User2 的架构中选择数据。
我使用了以下查询：
CREATE OR REPLACE VIEW User1.NewView (Column1) AS
SELECT DISTINCT Column1 FROM User2.Table
并收到以下错误：
SQL Error: ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*Cause:
*Action:
要解决此问题，我必须向 User1 对 User2.Table 授予选择访问权限。有没有办法在不授予访问权限的情况下执行此操作，因为我已经以 dba 身份登录？
I am logged in as the dba account and I want to create a view in User1's schema, but selecting data from User2's.
I used the following query:
CREATE OR REPLACE VIEW User1.NewView (Column1) AS
SELECT DISTINCT Column1 FROM User2.Table
and I get the following error:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*Cause:
*Action:
To resolve this I had to grant select access to User1 on User2.Table. Is there a way to do this without having to grant access, since I am already logged in as the dba?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
是的，您必须（并且始终应该）显式授予对另一个模式中的对象的访问权限。
尽管您以“dba 帐户”（SYS，我假设）身份登录，但 CREATE 语句专门针对 user1 模式。
Yes, you have (and always should) to explicitly grant access to objects in another schema.
Though you're logged in as "the dba account" (SYS, I'm assuming), the CREATE statement is for the user1 schema specifically.
您可以执行 CREATE OR REPLACE FORCE VIEW ...
这将在缺乏权限的情况下创建视图，但除非授予权限，否则视图将不可用。如果稍后授予权限（例如在某些自动构建脚本中），那么这是一个非常有用的解决方案。
You can do CREATE OR REPLACE FORCE VIEW ...
That will create the view despite the lack of privileges, but the view would not be usable unless the privileges are granted. It is a solution that can be useful if privileges will be granted later (eg in some automated build script).
可能是因为您无权访问表或视图
查询
向用户授予对象的权限；
Probably because you do not have access to the table or view
Query
grant privileges on object to user;