拥有dba权限的情况下创建视图报“ORA-01031: insufficient privileg...

bj-jn

版本: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错误，请各位帮忙看看，谢谢！

Maclean Liu(刘相兵

做一个1031 的errorstack我看下

bj-jn

Maclean Liu(刘相兵 发表于 2014-3-13 11:32
做一个1031 的errorstack我看下

刘大好，上传附件为level 10的errorstack

Maclean Liu(刘相兵

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 报错

Maclean Liu(刘相兵

kzpchkbu   Kernel Zecurity layer Privileges CHecK privileges on an object

Maclean Liu(刘相兵

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

Maclean Liu(刘相兵

这个现象是正常的

  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

bj-jn

Maclean Liu(刘相兵 发表于 2014-3-13 20:31
这个现象是正常的

  1* select object_name,object_type,owner from dba_objects where object_name='DBA_ ...

感谢刘大，看来这是正常现象那我就部纠结了！

uj12best

这个要显示给权限的。