当前位置:Oracle研究中心 > 故障案例 >
时间:2016-07-01 23:22 来源:Oracle研究中心 作者:惜分飞 点击: 次
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
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授权
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
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
Powered by Oracle研究中心 OraclePlus Inc.
BY DedeCMS 京ICP备14022373号-5
声明:Oracle研究中心部分文章源自互联网,转载均按原文作者要求转载,如有疑问联系QQ:1026002015,协商未果24小时删除。