【案例】Oracle报错ORA-01031 创建Oracle视图提示insufficient privileges

天萃荷净 开发DBA反映，在创建Oracle视图时报错ORA-01031: insufficient privileges，分析原因为权限不足导致

1、创建Oracle视图报错ORA-01031

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

SQL> select table_name from user_tables where table_name in('X_T','DEPT');

TABLE_NAME
------------------------------
X_T

SQL> create view  v_xff1
  2  as 
  3  select * from X_T;

View created.


SQL> create view  V_XFF AS
  2  SELECT * FROM SCOTT.DEPT;
SELECT * FROM SCOTT.DEPT
                    *
ERROR at line 2:
ORA-01031: insufficient privileges


SQL> SELECT COUNT(*) FROM SCOTT.DEPT;

  COUNT(*)
----------
         4

通过上面的试验证明：
1）在同一个schema下，有查询权限，就可以创建视图
2）在不同schema下，即使有了查询权限，创建视图，还是会提示ORA-01031

2、查看Oracle用户相关权限

SQL> select * from SESSION_PRIVS where 
  2  PRIVILEGE in('SELECT ANY TABLE','CREATE ANY VIEW','CREATE VIEW');

PRIVILEGE
----------------------------------------
SELECT ANY TABLE
CREATE VIEW
CREATE ANY VIEW

SQL> SELECT * FROM SESSION_PRIVS WHERE PRIVILEGE NOT IN(
  2  SELECT PRIVILEGE
  3    FROM ROLE_SYS_PRIVS
  4   WHERE ROLE IN(SELECT * FROM SESSION_ROLES));

no rows selected

通过上面权限查询得出：用户所具有的select 其他用户表的权限是用过role授权

3、单独授于select权限

SQL> conn / as sysdba
Connected.
SQL> grant select on SCOTT.DEPT to xff;

Grant succeeded.

SQL> conn xff/oracleplus
Connected.
SQL> create view  V_XFF AS
  2  SELECT * FROM SCOTT.DEPT;

View created.

SQL> select view_name from user_views;

VIEW_NAME
------------------------------
V_XFF
V_XFF1

4、Oracle视图创建失败产生问题原因

In order to create a view in a schema, that schema must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The view owner must be granted these privileges directly, rather than through a role. The reason is that privileges granted to roles cannot be inherited via objects

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-01031 创建Oracle视图提示insufficient privileges

本文由大师惜分飞原创分享，网址：http://www.oracleplus.net/arch/684.html

Oracle研究中心

关键词：

Oracle视图

Oracle报错ORA-01031

创建Oracle视图提示insufficient privileges