用了mysql报oracle错误,【案例】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