oracle 如何在 Oracle11g 中授予物化视图的权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12212479/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to grant rights to materialized view in Oracle11g
提问by user1638200
I'm very new to oracle and trying to grant rights to some materialized views I created.
我对 oracle 很陌生，并试图授予我创建的一些物化视图的权限。
What I've done (all from sqlplus command line):
我所做的（全部来自 sqlplus 命令行）：
sqlplus "/ as sysdba
sqlplus "/ 作为 sysdba
>create tablespace tablespace_test datafile 'g:\tables\tablespace_test.dbf' size 20M autoextend on;
>create materialized view test_mv_1 tablespace tablespace_test parallel build immediate refresh complete on demand as select cm.* from yyy.jjj$project@externaldb p, yyy.jjj$mgmt@externaldb cm where (p.project_nbr = cm.project_nbr);
>create user dummy profile default identified by password default tablespace tablespace_test account unlock;
>grant connect to dummy;
>grant select on test_mv_1 to dummy;
>select * from test_mv_1; <---***this works, data is there
>connect dummy
>select * from test_mv_1;
ERROR at line 1:
ORA-00942: table or view does not exist
Don't know how to fix this, and I'm sure I done some things wrong. Any help is greatly appreciated.
不知道如何解决这个问题，而且我确定我做错了一些事情。任何帮助是极大的赞赏。
回答by Justin Cave
If the materialized view is owned by SYS(this is a reallybad idea-- you should never, ever create objects in the SYSschema. SYSis special. It is reserved for Oracle. It frequently behaves differently from every other schema in the database.) and there is no synonym created for the materialized view, you would need to specify the fully qualified name when you issued the query as dummy. That is
如果物化视图由SYS（这是一个非常糟糕的主意 - 你永远不应该在SYS模式中创建对象。 SYS是特殊的。它是为 Oracle 保留的。它的行为经常与数据库中的其他模式不同。）和没有为物化视图创建同义词，您需要在发出查询时指定完全限定名称为dummy。那是
SQL> connect dummy/password
SQL> select * from sys.test_mv_1;
You really, really, reallywant to create the materialized view in a different schema, though. Assuming you create the materialized view in the mv_ownerschema, the query would change to
不过，您真的、真的、真的想在不同的模式中创建物化视图。假设您在mv_owner架构中创建物化视图，查询将更改为
select *
from mv_owner.test_mv_1
If you want to avoid using a fully qualified name, you can create a synonym. If you create a public synonym
如果要避免使用完全限定名称，可以创建同义词。如果您创建公共同义词
CREATE PUBLIC SYNONYM test_mv_1
FOR mv_owner.test_mv_1
or if you create a private synonym in the dummyschema
或者如果您在dummy架构中创建了一个私有同义词
CREATE SYNONYM test_mv_1
FOR mv_owner.test_mv_1
Then dummywould be able to specify an unqualified object name
然后dummy将能够指定一个不合格的对象名称
SELECT *
FROM test_mv_1
