3,492
社区成员
发帖
与我相关
我的任务
分享
OPER@tl> create user test identified by aaa;
用户已创建。
OPER@tl> grant connect,resource to test;
授权成功。
OPER@tl> create user test2 identified by aaa;
用户已创建。
OPER@tl> grant dba to test2; --test2有dba权限
授权成功。
OPER@tl> conn test/aaa
已连接。
TEST@tl> create table t as select 1 aaa from dual;
表已创建。
TEST@tl> select * from t;
AAA
----------
1
TEST@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>
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>
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 x1 as
select * from a.b;
--权限不足