创建视图：ORA-01031 隐藏的级联权限
一、问题现象
创建视图时，出现ORA-01031: insufficient privileges 报错，而该用户对视图创建语句涉及的表和视图都是存在权限的；再次深入其涉及的表和视图，也都是具有各自视图对自己直接所属表和视图的select权限；但在创建时就是报权限不足。二、问题原因
视图或者表存在一个级联权限，一旦某个视图缺乏对一个表的级联权限，它的上级视图创建是时则权限不足，即存在权限关联和级联授权三、问题分析与解决
创建视图 NEWRISK.V_REPORT_INDUSTRY_INFO_2021
CREATE OR REPLACE FORCE EDITIONABLE VIEW “NEWRISK”.“V_REPORT_INDUSTRY_INFO_2021” (“SECURITY_ID”, “SYMBOL_ID”, “SYMBOL_CODE”, “EXCH_TYPE_D”, “SYMBOL_TYPE_D”, “INDUSTRY_D”, “INDUSTRY_NAME”, “BEGIN_DATE”, “END_DATE”, “LEVEL_CODE”, “LEVEL_NAME”) AS
SELECT SECURITY_ID,SYMBOL_ID,SYMBOL_CODE,EXCH_TYPE_D,SYMBOL_TYPE_D,INDUSTRY_D,INDUSTRY_NAME,BEGIN_DATE,END_DATE, LEVEL_CODE,LEVEL_NAME FROM (SELECT COM_ID,INDUSTRY_D,INDUSTRY_NAME,BEGIN_DATE,END_DATE, LEVEL_CODE,LEVEL_NAME FROM (
SELECT COM_ID,INDUSTRY_D,INDUSTRY_NAME,BEGIN_DATE,END_DATE,LEVEL1_CODE AS LEVEL_CODE,LEVEL1_NAME AS LEVEL_NAME FROM inmdmadmin.v_cm_industry WHERE DELETE_MARK=‘0’ and (/*(INDUSTRY_D=‘2110’ AND LEVEL1_CODE<>‘C’) or*/ INDUSTRY_D=‘2113’)/* UNION ALL
SELECT COM_ID,INDUSTRY_D,INDUSTRY_NAME,BEGIN_DATE,END_DATE,LEVEL2_CODE,LEVEL2_NAME FROM NEWRISK.V_CM_INDUSTRY WHERE DELETE_MARK=‘0’ and INDUSTRY_D=‘2110’ AND LEVEL1_CODE=‘C’ */)) A INNER JOIN (SELECT * FROM inmdmadmin.v_sec_info WHERE SYMBOL_TYPE_D IN (‘101’, ‘102’, ‘103’, ‘104’,‘105’) AND SRC_TYPE=1) B ON A.COM_ID = B.COM_ID WHERE LEVEL_CODE IS NOT NULL;
Warning: View created with compilation errors.
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 ORA-01031: insufficient privileges
涉及的表or视图： inmdmadmin.v_cm_industry inmdmadmin.v_sec_info NEWRISK.V_CM_INDUSTRY (自身视图肯定有权限)
因为是NEWRISK.V_CM_INDUSTRY 自身视图肯定有权限，所以分析其余两个表，拆分单独创建视图查询子视图 inmdmadmin.v_sec_info 失败;getddl:
CREATE OR REPLACE FORCE EDITIONABLE VIEW “INMDMADMIN”.“V_SEC_INFO” (“SECURITY_ID”, “SYMBOL_ID”, “SYMBOL_CODE”, “SYMBOL_SNAME”, “EXCH_TYPE_D”, “SYMBOL_TYPE_D”,
“COM_ID”, “SRC_TYPE”, “CUR_TYPE_D”, “LIST_DATE”, “DELIST_DATE”, “DELETE_MARK”)
AS
select a.SECURITY_ID,a.SYMBOL_ID,a.SYMBOL_CODE,a.SYMBOL_SNAME,a.EXCH_TYPE_D,a.SYMBOL_TYPE_D,a.COM_ID,1 AS SRC_TYPE,a.CUR_TYPE_D,a.LIST_DATE,a.DELIST_DATE,a.delete_mark from mk_db.tc_gl_security_code a union all
select t1.sec_id as SECURITY_ID,t1.sec_id as symbol_id,SEC_CODE AS SYMBOL_CODE,t1.sec_name as symbol_sname,MARKET_CODE,‘9999’ AS SYMBOL_TYPE_D,’ ’ AS COM_ID,2 AS SRC_TYPE,CUR_ID_D,‘20020101’ AS LIST_DATE,‘20991231’ AS DELIST_DATE,0 AS delete_mark from INDBADMIN.TP_GL_FINSEC_DEF t1 WHERE SEC_CODE NOT IN(SELECT HXCODE FROM INDBADMIN.TP_GL_FINSEC_MAP);
ORA-01031: insufficient privileges
涉及的表or视图：mk_db.tc_gl_security_code INDBADMIN.TP_GL_FINSEC_DEF INDBADMIN.TP_GL_FINSEC_MAP
再次拆分视图涉及如下三个对象,单独查询count正常;
select count(*) from MK_DB.TC_GL_SECURITY_CODE;
select count(*) from INDBADMIN.TP_GL_FINSEC_MAP;
select count(*) from INDBADMIN.TP_GL_FINSEC_DEF;
单独拆分,使用如下SQL单独创建视图,发现只要创建 MK_DB.TC_GL_SECURITY_CODE 这个表就报错！！
ORA-01720: grant option does not exist for ‘MK_DB.TC_GL_SECURITY_CODE’
进而查询 INMDMADMIN 用户的权限,发现没有 GRANTABLE 权限
SELECT * FROM dba_tab_privs WHERE TABLE_NAME IN(‘TC_GL_SECURITY_CODE’) and OWNER=‘MK_DB’ and grantee=‘INMDMADMIN’ ;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIE COM TYPE INH
------------------------------ -------------------- ------------------------------ ------------------------------ ---------------------------------------- — — — ------------------------ —
INMDMADMIN MK_DB TC_GL_SECURITY_CODE MK_DB SELECT NO NO NO TABLE NO
–授权后,再次创建视图都Ok
grant select on MK_DB.TC_GL_SECURITY_CODE to INMDMADMIN with grant option;
SELECT * FROM dba_tab_privs WHERE TABLE_NAME IN(‘TC_GL_SECURITY_CODE’) and OWNER=‘MK_DB’ and grantee=‘INMDMADMIN’ ;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIE COM TYPE INH
------------------------------ -------------------- ------------------------------ ------------------------------ ---------------------------------------- — — — ------------------------ —
INMDMADMIN MK_DB TC_GL_SECURITY_CODE MK_DB SELECT YES NO NO TABLE NO
四、问题总结
A用户需要A用户的视图,视图中涉及到B用户的视图;B用户的视图,涉及到C用户的基表;
A用户具有B用户视图的查询权限,B用户具有C用户基表的查询权限,但是没有基表C的级联权限,即使A用户具有C用户基表的查询权限,A用户创建视图还是报错！
–case
对B用户授权 with grant option之后,A用户创建权限OK
A—B—C
在此案例中：
NEWRISK.V_REPORT_INDUSTRY_INFO_2021 (A) ------- INMDMADMIN.V_SEC_INFO (B) --------- MK_DB.TC_GL_SECURITY_CODE ©
因此，下次遇到此类问题，细心严谨地去查询相关权限，不仅限于select权限，还有级联授权GRANTABLE是否为yes
