Oracle 创建视图问题 [英] Oracle Create View issue

问题描述

我以 dba 帐户登录，我想在 User1 的架构中创建一个视图，但从 User2 的架构中选择数据.

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.

我使用了以下查询:

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:

为了解决这个问题，我必须在 User2.Table 上授予对 User1 的选择访问权限.因为我已经以 dba 身份登录，所以有没有一种无需授予访问权限的方法?

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?

推荐答案

是的，您必须(并且始终应该)明确授予对另一个架构中的对象的访问权限.

Yes, you have (and always should) to explicitly grant access to objects in another schema.

GRANT SELECT ON user2.table TO user1

尽管您以dba 帐户"(我假设是 SYS)身份登录，但 CREATE 语句专门针对 user1 架构.

Though you're logged in as "the dba account" (SYS, I'm assuming), the CREATE statement is for the user1 schema specifically.

这篇关于Oracle 创建视图问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！