ora-01031:insufficient privileges 之跨库问题

执行某sql时过程时报错：ORA-01031: insufficient privileges。

以为是没有权限创建表，给该用户创建表的权限

--授予角色权限
grant connect to housingsecurity_running;
grant dba to housingsecurity_running;
--授予系统权限

后还是会报这个错误。

仔细看SQL 发现sql有跨用户对不是自己的表进行操作

CREATE OR REPLACE VIEW HOUSINGSECURITY_RUNNING.V_PROINSTISSUEALLOWANCE
    (ISSUEALLOWANCERECORD_ID,CONTRACTSUBSIDY_ID,PROINST_ID,ISSUEALLOWANCE_MODE,
    ALLOWANCE_AMOUNT,ISSUEALLOWANCE_MONEY,ISSUEALLOWANCERECORD_SUM,ISSUEALLOWANCE_STATE,
    ISSUEALLOWANCERECORD_DATE,ISSUEALLOWANCERECORD_SDATE,ISSUEALLOWANCERECORD_EDATE,
    ISSUEALLOWANCERECORD_PERSON,CONTRACT_CODE,SIGN_STAFF,LOWESTLIFE_POPULATION,
    LOWESTINCOME_POPULATION,LIVINGTOGETHER_POPULATION,HOUSEUSE_TYPE,BUILDING_AREAPER,
    ISSUEALLOWANCERECORD_DESC,LATESTISSUE_DATE,CALCULATE_FORMULA,CONTRACTSIGN_DATE,
    CONTRACT_ID,FAMILYMEMBER_TELEPHONE,BANK_NAME,BANKACCOUNT_NAME,BANKACCOUNT_NUMBER,
    SIGNSTAFF_IDCARDNO,PUBLICITY_CODE,FAMILY_ID)
AS
SELECT 
A."ISSUEALLOWANCERECORD_ID",
A."CONTRACTSUBSIDY_ID",
A."PROINST_ID",
A."ISSUEALLOWANCE_MODE",
A."ALLOWANCE_AMOUNT",
A."ISSUEALLOWANCE_MONEY",
A."ISSUEALLOWANCERECORD_SUM",
A."ISSUEALLOWANCE_STATE",
A."ISSUEALLOWANCERECORD_DATE",
A."ISSUEALLOWANCERECORD_SDATE",
A."ISSUEALLOWANCERECORD_EDATE",
A."ISSUEALLOWANCERECORD_PERSON",
TO_NUMBER(LTRIM(regexp_replace(A.CONTRACT_CODE,'[^0-9]'))),
A."SIGN_STAFF",
A."LOWESTLIFE_POPULATION",
A."LOWESTINCOME_POPULATION",
A."LIVINGTOGETHER_POPULATION",
A."HOUSEUSE_TYPE",
A."BUILDING_AREAPER",
A."ISSUEALLOWANCERECORD_DESC",
B.LATESTISSUE_DATE,
B.CALCULATE_FORMULA,
C.CONTRACTSIGN_DATE,
C.CONTRACT_ID,
D.FAMILYMEMBER_TELEPHONE,
E.BANK_NAME,
E.BANKACCOUNT_NAME,
E.BANKACCOUNT_NUMBER,
D.CERTIFICATE_CODE,
E.PUBLICITY_CODE,
E.FAMILY_ID
FROM 
      HOUSINGSECURITY_RUNNING.T_ISSUEALLOWANCERECORD A,
      HOUSINGSECURITY_STORAGE.T_CONTRACTSUBSIDY B,
      HOUSINGSECURITY_STORAGE.T_CONTRACT C,
      HOUSINGSECURITY_STORAGE.T_FAMILYMEMBER D,
      HOUSINGSECURITY_STORAGE.T_FAMILY E
WHERE A.CONTRACTSUBSIDY_ID=B.CONTRACTSUBSIDY_ID AND 
      B.CONTRACT_ID=C.CONTRACT_ID and
      C.SIGNSTAFF_IDCARDNO = D.CERTIFICATE_CODE and
      D.FAMILY_ID = E.FAMILY_ID

所以需要去HOUSINGSECURITY_STORAGE用户里找到对应的表，如T_FAMILY E。

在表里面给HOUSINGSECURITY_RUNNING 这个用户操作的权限。

OB给权限如下，命令还未知