建立视图报 ORA-01031: insufficient privileges

建立视图报 ORA-01031: insufficient privileges

建立所需要的实验环境的脚本：

create user lc0019999 identified by aaaaaa;
create user lc0029999 identified by aaaaaa;
grant dba to lc0019999;
grant dba to lc0029999;
create table lc0029999.t1 (c1 varchar(2));
select * from lc0029999.t1;
create view lc0019999.v_t1 as select * from lc0029999.t1;

 

进行如下实验：

F:\oracle\product\10.2.0\db_1\BIN>set oracle_sid=fs

F:\oracle\product\10.2.0\db_1\BIN>sqlplus lc0019999/aaaaaa

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jun 19 18:48:36 2015

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

18:48:37 lc0019999@FS>
18:54:54 lc0019999@FS> create view lc0019999.v_t1 as select * from lc0029999.t1;

create view lc0019999.v_t1 as select * from lc0029999.t1
                                                      *
ERROR at line 1:
ORA-01031: insufficient privileges      --->报错！


Elapsed: 00:00:00.01
18:54:56 lc0019999@FS>

问题分析：
参考：ORA-1031 While Creating A View On A Table On Which The Select Privilege Is Granted Via A Role (文档 ID 271587.1)

In order to create a view in a schema, that schema must have the privileges necessary to either select, insert, update, 
or delete rows from all the tables or views on which the view is based. 
The view owner must be granted these privileges directly, rather than through a role. 
The reason is that privileges granted to roles cannot be inherited via objects, this is explained also in referenced note 168168.1. 

解决方法：

Grant the select privilege on the base table directly rather than through a role:

19:30:36 lc0019999@FS> conn lc0029999/aaaaaa
Connected.
19:34:22 lc0029999@FS> grant select on t1 to lc0019999;

Grant succeeded.

Elapsed: 00:00:00.04

19:34:41 lc0029999@FS> conn lc0019999/aaaaaa
Connected.
19:35:19 lc0019999@FS> create view lc0019999.v_t1 as select * from lc0029999.t1;

View created.

Elapsed: 00:00:00.18

19:35:49 lc0019999@FS>