拥有dba权限的情况下创建视图报“ORA-01031: insufficient privileg...
本帖最后由 bj-jn 于 2014-3-13 11:05 编辑版本:10.2.0.5.0(64bit)
操作步骤：
1.查看会话角色
SQL> select * from session_roles;
ROLE
------------------------------
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDBWEBSERVICES
PLUSTRACE
OLAP_DBA
2.查看会话权限
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
UNDER ANY VIEW
MERGE ANY VIEW
3.查询数据字典
SQL> select count(*) cnt from dba_data_files;
COUNT(*)
----------
24
4.创建基于数据字典的视图
SQL> create view v as select count(*) cnt from dba_data_files;
create view v as select count(*) cnt from dba_data_files
*
ERROR at line 1:
ORA-01031: insufficient privileges
在该用户下创建基于普通表的视图没问题，只要创建基于数据字典或者动态性能视图的视图时都会报ORA-01031错误，请各位帮忙看看，谢谢！ 做一个1031 的errorstack我看下 Maclean Liu(刘相兵 发表于 2014-3-13 11:32 static/image/common/back.gif
做一个1031 的errorstack我看下
刘大好，上传附件为level 10的errorstack
ksedmp: internal or fatal error
ORA-01031: insufficient privileges
Current SQL statement for this session:
create view v as select count(*) cnt from dba_data_files
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
ksfchk kzpchkbu kzpchkbu 报错 kzpchkbu Kernel Zecurity layer Privileges CHecK privileges on an object
SQL> grant dba to maclean;
Grant succeeded.
SQL> conn maclean/oracle
Connected.
INSTANCE_NAME
----------------
ASMDB1
SQL> create view v1 as select count(*) cnt from all_users;
View created.
SQL> create view v2 as select count(*) cnt from dba_users;
create view v2 as select count(*) cnt from dba_users
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
test case 2:
SQL> select * from v$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> grant dba to maclean;
Grant succeeded.
SQL> conn maclean/oracle
Connected.
SQL> create view v1 as select count(*) cnt from all_users;
View created.
SQL> create view v2 as select count(*) cnt from dba_users;
create view v2 as select count(*) cnt from dba_users
*
ERROR at line 1:
ORA-01031: insufficient privileges
这个现象是正常的
1* select object_name,object_type,owner from dba_objects where object_name='DBA_USERS'
SQL> /
OBJECT_NAME OBJECT_TYPE OWNER
------------------------------ ------------------- ------------------------------
DBA_USERS VIEW SYS
DBA_USERS SYNONYM PUBLIC
dba用户没有办法在属于SYS的视图上建另一层视图
SQL> conn / as sysdba
Connected.
SQL> create view sys_view1 select 1 "abc" from dual;
create view sys_view1 select 1 "abc" from dual
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> create view sys_view1 as select 1 "abc" from dual;
View created.
SQL> conn maclean/oracle
Connected.
SQL> select * from sys.sys_view1;
abc
----------
1
SQL> create view dba_view1 as select * From sys.sys_view1;
create view dba_view1 as select * From sys.sys_view1
*
ERROR at line 1:
ORA-01031: insufficient privileges
Maclean Liu(刘相兵 发表于 2014-3-13 20:31 static/image/common/back.gif
这个现象是正常的
1* select object_name,object_type,owner from dba_objects where object_name='DBA_ ...
感谢刘大，看来这是正常现象那我就部纠结了！ 这个要显示给权限的。
页:
[1]