当前操作用户为:b 具有create view权限 对a用户的表x1有查看,修改的权限
版本oracle 10.2.0.4
create view xxx a.x1 as
select * from a.b;
--创建成功
create view xxx x1 as
select * from a.b;
--权限不足版本oracle 10.2.0.1
create view xxx a.x1 as
select * from a.b;
--权限不足
create view xxx x1 as
select * from a.b;
--创建成功通过这两种测试结果,使得我对create any view 和create view 都有点迷糊了。下面是两种权限的解释:
CREATE VIEW
在自己的方案中创建、更改和删除视图
CREATE ANY VIEW
在任何方案中创建视图但是依据上述解释,似乎无法解释我测试到的结果!
抛砖引玉!说说你们的看法!
版本oracle 10.2.0.4
create view xxx a.x1 as
select * from a.b;
--创建成功
create view xxx x1 as
select * from a.b;
--权限不足版本oracle 10.2.0.1
create view xxx a.x1 as
select * from a.b;
--权限不足
create view xxx x1 as
select * from a.b;
--创建成功通过这两种测试结果,使得我对create any view 和create view 都有点迷糊了。下面是两种权限的解释:
CREATE VIEW
在自己的方案中创建、更改和删除视图
CREATE ANY VIEW
在任何方案中创建视图但是依据上述解释,似乎无法解释我测试到的结果!
抛砖引玉!说说你们的看法!
在自己的方案中创建、更改和删除视图
CREATE ANY VIEW
在任何方案中创建视图 描述的很清楚了呀。这里有主要是权限问题。
create view xxx x1 as
select * from a.b;
--权限不足 此处可能是当前用户没有a.b的select权限,你grant a.b to 当前用户试试。这里主要是对象权限和系统权限的问题。 你给的解释是正确的。
select * from a.b;
--权限不足
可能是你没有select用户a的b表的权限。
要是我没有权限,我怎么可以执行
create view xxx a.x1 as
select * from a.b;
这句呢?
而且 我当前有的是create view 的权限,还不是create any view 的权限。怎么可以创建b用户下面的表呢?
SQL> connect w/w@orcl;
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as w
SQL> create user w5 identified by 1;
User created
SQL> grant create view,connect,resource to w5; --赋create view权限
Grant succeeded
SQL> grant select,update on w.a to w5;
Grant succeeded
SQL> connect w5/1@orcl;
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as w5
SQL> show user;
User is "w5"
--创建w用户下的视图
SQL> create view w.v as select * from w.a;
create view w.v as select * from w.a
ORA-01031: 权限不足
--创建本用户下视图
SQL> create view v as select * from w.a;
View created
SQL> connect w/w@orcl;
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as w
SQL> revoke create view from w5; --取消create view权限
Revoke succeeded
SQL> grant create any view to w5; --授create any view权限
Grant succeeded
SQL> connect w5/1@orcl;
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as w5
SQL> create view w.v as select * from w.a; --创建w用户下的视图
View created
SQL> create view v1 as select * from w.a; --建本用户下视图
View created
SQL>
楼主这里create view xxx a.x1 as 的xxx是什么?
楼主举的两个例子下面那个结果是正常的,上面那个,用户或用户所属的角色应该有create any view的权限,权限不足是否有别的原因?理论上不会这样的
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> 上面的例子 有谁可以解释下,为什么会出现这样的情况吗?
已连接。TEST@tl> create table t as select 1 aaa from dual;表已创建。TEST@tl> select * from t; AAA
----------
1TEST@tl> conn test2/aaa
已连接。--查询得到与lz类似结果
TEST2@tl> 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
UNDER ANY VIEW
MERGE ANY VIEW已选择9行。--这里可以查询t表
TEST2@tl> select * from test.t; AAA
----------
1--但是不能建view
TEST2@tl> create view v_t as select * from test.t;
create view v_t as select * from test.t
*
第 1 行出现错误:
ORA-01031: 权限不足
TEST2@tl> conn oper/111
已连接。--因为没有显式授权
OPER@tl> grant select on test.t to test2; 授权成功。OPER@tl> conn test2/aaa
已连接。--现在好了
TEST2@tl> create view v_t as select * from test.t;视图已创建。TEST2@tl>
楼主的用户大概是有dba权限,所以session_privs里显示的权限太多,使用了like '%VIEW%'
可以使用该用户对自己授权select any table