求助！创建物化视图出错

[tsj68]

本人在oracle9i中创建引用另一个模式（schema）的表时，总是报ORA-01039 insufficient privileges on underlying objects of the view，但是创建用户有DBA,RESOURCE,CONNECT角色，
并显示地将引用表授权给创建用户，语句如下：

CREATE MATERIALIZED VIEW "SH"."GG"
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
select * from otschema.employee

[KingSunSha]

From Oracle document:

To create a materialized view in your own schema:
- You must have been granted either the CREATE MATERIALIZED VIEW or CREATE SNAPSHOT system privilege and either the CREATE TABLE or CREATE ANY TABLE system privilege.
- You must also have access to any master tables of the materialized view that you do not own, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.

To create the materialized view with query rewrite enabled, in addition to the
preceding privileges:
- The owner of the master tables must have the QUERY REWRITE system privilege.
- If you are not the owner of the master tables, you must have the GLOBAL QUERY REWRITE system privilege.
- If the schema owner does not own the master tables, then the schema owner must have the GLOBAL QUERY REWRITE privilege.

[KingSunSha]

最简单的测试方法：

grant select any table to sh;

CREATE MATERIALIZED VIEW "SH"."GG"
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
select * from otschema.employee;

[ATGC]

GRANT GLOBAL QUERY REWRITE TO SH;

[mysohu]

GRANT GLOBAL QUERY REWRITE TO SH;
可以不?

[w_tsinghua]

最好不要授予 SH 用户 SELECT ANY TABLE 权限，而是根据自己的应用，进行select授权

[boydgmx]

我遇到这种问题好久了，也知道需要授予 SH 用户 SELECT ANY TABLE 权限，
但是这个权限不能随便授予，于是我在DBA用户下把 otschema.employee 的select权限授予 SH，
然后以SH用户登录，创建MV