论坛徽章:: 0

电梯直达

1楼 [收藏(0)] [报告]

发表于 2011-12-19 13:56 |只看该作者 |倒序浏览

<DIV><SPAN class=Apple-style-span style="WORD-SPACING: 0px; FONT: medium 文泉驿正黑; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px">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.
<DIV class=entry style="PADDING-RIGHT: 3px; OVERFLOW-Y: hidden; PADDING-LEFT: 3px; OVERFLOW-X: hidden; PADDING-BOTTOM: 0px; MARGIN: 0px; PADDING-TOP: 0px">
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. <A style="PADDING-RIGHT: 0px; PADDING-LEFT: 0px; FONT-WEIGHT: bold; PADDING-BOTTOM: 0px; MARGIN: 0px; COLOR: rgb(60,108,146); PADDING-TOP: 0px; BORDER-BOTTOM: rgb(221,221,221) 1px solid; TEXT-DECORATION: none; outline-style: none; outline-width: initial; outline-color: initial" href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14196/users_secure002.htm#sthref415">http://download.oracle.com/docs/cd/B19306_01/server.102/b14196/users_secure002.htm#sthref415</A>
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.</DIV></DIV>

返回列表

Chinaunix › 论坛 › IT运维 › Hadoop和大数据技术 › [Archived move] Privilege Problem(create view)

[Hive] [Archived move] Privilege Problem(create view) [复制链接]