建立視圖報ORA-01031:insufficientprivileges

建立視圖報ORA-01031:insufficientprivileges

建立視圖報 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>