操作过程:
conn sys/sys as sysdba;
create user a indentified by a;
create create session ,connect to a;
grant select on b.tables to a;
grant create view to a;conn a/a
select * from b.tables ;--有记录返回。
create view T_view as
select * from b.tables ;--提示权限不足的原因。会是什么原因!谢谢各位!
conn sys/sys as sysdba;
create user a indentified by a;
create create session ,connect to a;
grant select on b.tables to a;
grant create view to a;conn a/a
select * from b.tables ;--有记录返回。
create view T_view as
select * from b.tables ;--提示权限不足的原因。会是什么原因!谢谢各位!
grant create any view to a;
select * from b.tables ;--提示权限不足的原因。a 用户没有 select b.tables的权限用b登录,或者sys登录grant select on tables to a即可。
如果上述授权步骤都执行成功,不应该出现这种情况。楼主是按下面的执行的吗,每个帖子楼主的代码都有点小错误,是发帖的时候写错了吧,那么楼主执行授权操作的时候都成功了吗?
登录a用户查看下当前的权限:
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE VIEW
是否有create view权限
SQL> conn sys/a as sysdba;
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as SYS
SQL> create user a identified by a;
User created
SQL> grant create session,connect to a;
Grant succeeded
SQL> grant select on w.a to a;
Grant succeeded
SQL> grant create view to a;
Grant succeeded
SQL> conn a/a;
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as a
SQL> select * from w.a;
MM NN
---------- ----
10 飞机
10 汽车
10 轮船
SQL> create view t_views as select * from w.a;
View created
SQL>
Connected to Oracle Database 10g Release 10.2.0.4.0
Connected as cm_admin
SQL> conn cm_admin/cm_admin
Not logged on
SQL> conn cm_admin/cm_admin@cm
Connected to Oracle Database 10g Release 10.2.0.4.0
Connected as cm_admin
SQL> select * from session_privs where privilege LIKE '%VIEW%';
PRIVILEGE
----------------------------------------
CREATE VIEW
CREATE ANY VIEW
DROP ANY VIEW
CREATE MATERIALIZED VIEW
CREATE ANY MATERIALIZED VIEW
ALTER ANY MATERIALIZED VIEW
DROP ANY MATERIALIZED VIEW
7 rows selected
SQL> SELECT * FROM CM_DATA.ASAS_DATABASE;
ITEM_ID ITEM_SOURCE_ID BEGIN_DATE END_DATE LATEST_FLAG CAPTION DESCRIPTION
-------------------- -------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
SQL> CREATE VIEW Z_TEST AS
2 SELECT * FROM CM_DATA.ASAS_DATABASE;
CREATE VIEW Z_TEST AS
SELECT * FROM CM_DATA.ASAS_DATABASE
ORA-01031: insufficient privileges
SQL>
这个问题一直都没有弄明白,我刚刚说的这个例子,就是我从测试库上摘的例子。
你在别的库上测试也会得到这样的结果吗
还是只在一个库中遇到
(C) 版权所有 1985-2001 Microsoft Corp.C:\Documents and Settings\Admin>sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 23 10:44:06 2009Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn scott/tiger
Connected.
SQL> show user
USER is "SCOTT"
SQL> create user tt identified by tt
2 default tablespace users;
create user tt identified by tt
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> create user tt identified by tt
2 default tablespace users;User created.SQL> grant connect to tt;Grant succeeded.SQL> grant create view to tt;Grant succeeded.SQL> grant select on scott.dept to tt;Grant succeeded.SQL> conn tt/tt
Connected.
SQL> select * from scott.dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTONSQL> create view tt as select * from scott.dept;View created.SQL>
SQL> select * from session_privs;PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE VIEWSQL> select * from tt; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTONSQL>