I create a user miloluo with connect and resource roles.
SQL> create user miloluo identified by miloluo;
User created.
SQL> grant connect, resource to miloluo;
Grant succeeded.
SQL>
All seems to be ok, but when I create several tables and create the view problem occured:
SQL> create view v_empdept as
2 select dname, ename, sal from emp left outer join dept using(deptno);
create view v_empdept as
*
ERROR at line 1:
ORA-01031: insufficient privileges
So it should have not permission on creating view, after search the document lib, I finally find the privilege I should grant to user miloluo.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14196/users_secure002.htm#sthref415
so again, grant this privilege to miloluo
SQL> grant create view to miloluo;
Grant succeeded.
And view created sucessfully,
SQL> create view v_empdept as
2 select dname, ename, sal from emp left outer join dept using(deptno);
View created.
SQL> select * from v_empdept;
DNAME ENAME SAL
-------------- ---------- ----------
RESEARCH SMITH 1800
SALES ALLEN 1600
SALES WARD 1250
RESEARCH JONES 2975
SALES MARTIN 1250
SALES BLAKE 2850
ACCOUNTING CLARK 2450
RESEARCH SCOTT 3000
KING 5000
SALES TURNER 1500
RESEARCH ADAMS 1100
DNAME ENAME SAL
-------------- ---------- ----------
SALES JAMES 950
RESEARCH FORD 3000
ACCOUNTING MILLER 1300
14 rows selected.
To solve this problem, I googling the “ORA-01031: insufficient privileges create view”.
I found the first item of result it’s about a user(user A) create view on another user’s(user B) table, even though B got the grant on that table with a new role.
A still can’t create the view unless directly grant the create view on B’s table.
It’s very interesting, I will add this topic later.