Create view reports ORA-01031: insufficient privileges, insufficient

Create view reports ORA-01031: insufficient privileges, insufficient

Create view reported ORA-01031: insufficient privileges

Create a script for the required experiment environment:

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;

 

Perform the following experiments:

F: \ oracle \ product \ 10.2.0 \ db_1 \ BIN> set oracle_sid = fsF: \ oracle \ product \ 10.2.0 \ db_1 \ BIN> sqlplus lc00%9/aaaaaaSQL * Plus: release 10.2.0.4.0-Production on Fri Jun 19 18:48:36 2015 Copyright (c) 1982,200 7, Oracle. all Rights Reserved. connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options18: 48: 37 lc009709 @ FS> 18:54:54 lc009709 @ FS> create view lc004.7 9.v _ t1 as select * from lc0029999.t1; create view lc005.3 9.v _ t1 as select * from lc0029999.t1 * ERROR at line 1: ORA-01031: insufficient privileges ---> error! Elapsed: 00:00:00. 0118:54:56 lc0019999 @ FS>

Problem Analysis:
Reference: ORA-1031 While Creating A View On A Table On Which The Select Privilege Is Granted Via A Role (Document 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. 

Solution:

Grant the select privilege on the base table directly rather than through a role:19:30:36 lc0019999@FS> conn lc0029999/aaaaaaConnected.19:34:22 lc0029999@FS> grant select on t1 to lc0019999;Grant succeeded.Elapsed: 00:00:00.0419:34:41 lc0029999@FS> conn lc0019999/aaaaaaConnected.19:35:19 lc0019999@FS> create view lc0019999.v_t1 as select * from lc0029999.t1;View created.Elapsed: 00:00:00.1819:35:49 lc0019999@FS>