Chinaunix首页 | 论坛 | 博客
  • 博客访问: 217661
  • 博文数量: 57
  • 博客积分: 1376
  • 博客等级: 中尉
  • 技术积分: 658
  • 用 户 组: 普通用户
  • 注册时间: 2010-05-09 09:51
文章分类

全部博文(57)

文章存档

2012年(1)

2011年(56)

分类: Oracle

2011-02-26 21:13:29

I’d like to get all my Oracle knowledge back, ’cause I lost almost 2 years.
It’s a little hard for me now, ’cause I study these after work.

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.

阅读(1498) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~