本人在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
并显示地将引用表授权给创建用户,语句如下:CREATE MATERIALIZED VIEW "SH"."GG"
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
select * from otschema.employee
- 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.
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
select * from otschema.employee;
可以不?
但是这个权限不能随便授予,于是我在DBA用户下把 otschema.employee 的select权限授予 SH,
然后以SH用户登录,创建MV