跨用户执行函数报错 -5731 用户不匹配解决记录

使用A用户执行B用户下的F_OBJECT_EXIST函数时报错-5731 用户不匹配，并且B用户下F_OBJECT_EXIST函数执行权限已授予A用户。
数据库版本：8.1.4.48

函数定义如下：

CREATE OR REPLACE FUNCTION F_OBJECT_EXIST (
        OBJ_NAME    VARCHAR2,
        SUBOBJ_NAME VARCHAR2,
        OBJ_TYPE    VARCHAR2,
        OBJ_OWNER   VARCHAR2
)
RETURN  BOOLEAN IS
    RECORD_COUNTS NUMBER ;
BEGIN
    IF SUBOBJ_NAME IS NULL THEN
        SELECT 1 INTO RECORD_COUNTS FROM DUAL
        WHERE EXISTS
            (SELECT * FROM ALL_OBJECTS
             WHERE OBJECT_NAME = UPPER(OBJ_NAME)
                AND OWNER = UPPER(OBJ_OWNER)
                AND OBJECT_TYPE = UPPER(OBJ_TYPE));
    ELSE
        SELECT 1 INTO RECORD_COUNTS FROM DUAL
        WHERE EXISTS
            (SELECT * FROM ALL_OBJECTS
             WHERE OBJECT_NAME = UPPER(OBJ_NAME)
                AND SUBOBJECT_NAME = UPPER(SUBOBJ_NAME)
                AND OWNER = UPPER(OBJ_OWNER)
                AND OBJECT_TYPE = UPPER(OBJ_TYPE));
    END IF;
    IF RECORD_COUNTS = 1 THEN
        RETURN TRUE;
    ELSE
        RETURN FALSE;
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN FALSE;
END F_OBJECT_EXIST;

问题原因：
该版本数据库的ALL_OBJECTS视图中存在用户权限判断有关的条件

解决方法：
方案一：创建函数时指定AUTHID CURRENT_USER 当前用户权限，默认为定义者权限。

CREATE OR REPLACE FUNCTION F_OBJECT_EXIST (
        OBJ_NAME    VARCHAR2,
        SUBOBJ_NAME VARCHAR2,
        OBJ_TYPE    VARCHAR2,
        OBJ_OWNER   VARCHAR2
)
RETURN  BOOLEAN AUTHID CURRENT_USER
IS
    RECORD_COUNTS NUMBER ;
BEGIN
    IF SUBOBJ_NAME IS NULL THEN
        SELECT 1 INTO RECORD_COUNTS FROM DUAL
        WHERE EXISTS
            (SELECT * FROM ALL_OBJECTS
             WHERE OBJECT_NAME = UPPER(OBJ_NAME)
                AND OWNER = UPPER(OBJ_OWNER)
                AND OBJECT_TYPE = UPPER(OBJ_TYPE));
    ELSE
        SELECT 1 INTO RECORD_COUNTS FROM DUAL
        WHERE EXISTS
            (SELECT * FROM ALL_OBJECTS
             WHERE OBJECT_NAME = UPPER(OBJ_NAME)
                AND SUBOBJECT_NAME = UPPER(SUBOBJ_NAME)
                AND OWNER = UPPER(OBJ_OWNER)
                AND OBJECT_TYPE = UPPER(OBJ_TYPE));
    END IF;
    IF RECORD_COUNTS = 1 THEN
        RETURN TRUE;
    ELSE
        RETURN FALSE;
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN FALSE;
END F_OBJECT_EXIST;

方案二：使用以下视图定义创建ALL_OBJECTS_NEW，替换函数定义中的ALL_OBJECTS

CREATE OR REPLACE  VIEW  ALL_OBJECTS_NEW 
AS 
    SELECT 
O1.NAME OWNER,
O2.NAME OBJECT_NAME, 
NULL SUBOBJECT_NAME, 
O2.ID OBJECT_ID, 
NULL DATA_OBJECT_ID, 
O2.TYPE$ OBJECT_TYPE,
O2.CRTDATE CREATED,
TO_TIMESTAMP(INFO.STR_VALUE, 'YYYY-MM-DD HH24:MI:SS') LAST_DDL_TIME,
TO_TIMESTAMP(INFO.STR_VALUE, 'YYYY-MM-DD HH24:MI:SS') TIMESTAMP,
CASE WHEN O2.VALID == 'Y' THEN 'VALID' ELSE 'INVALID' END STATUS, 
'N' TEMPORARY,
NULL GENERATED,
NULL SECONDARY,
NULL NAMESPACE,
NULL EDITION_NAME 
    FROM 
TSYSOBJECTS O1, 
TSYSOBJECTS O2 
LEFT JOIN SYS.SYSOBJINFOS INFO ON INFO.ID = O2.ID AND INFO.TYPE$ = 'ALTTIME'
    WHERE 
        O1.TYPE$ = 'UR' AND O2.TYPE$ = 'SCH' AND O1.ID = O2.PID
        
        UNION ALL
        
        SELECT 
O1.NAME OWNER,
O2.NAME OBJECT_NAME, 
NULL SUBOBJECT_NAME, 
O2.ID OBJECT_ID, 
NULL DATA_OBJECT_ID, 
O2.TYPE$ OBJECT_TYPE,
O2.CRTDATE CREATED,
TO_TIMESTAMP(INFO.STR_VALUE, 'YYYY-MM-DD HH24:MI:SS') LAST_DDL_TIME,
TO_TIMESTAMP(INFO.STR_VALUE, 'YYYY-MM-DD HH24:MI:SS') TIMESTAMP,
CASE WHEN O2.VALID == 'Y' THEN 'VALID' ELSE 'INVALID' END STATUS, 
'N' TEMPORARY,
NULL GENERATED,
NULL SECONDARY,
NULL NAMESPACE,
NULL EDITION_NAME 
    FROM 
TSYSOBJECTS O1, 
TSYSOBJECTS O2 
LEFT JOIN SYS.SYSOBJINFOS INFO ON INFO.ID = O2.ID AND INFO.TYPE$ = 'ALTTIME'
    WHERE 
         (O2.TYPE$ in ('DSYNOM','GDBLINK') and O1.ID = 50331648) --sys USER and SCHEMA
        
        UNION ALL
        
        SELECT 
O3.NAME OWNER,
O2.NAME OBJECT_NAME, 
NULL SUBOBJECT_NAME, 
O2.ID OBJECT_ID, 
NULL DATA_OBJECT_ID, 
CASE WHEN O2.TYPE$='SCHOBJ' THEN 
	     CASE WHEN O2.SUBTYPE$='PKG' THEN PKG.TYPES 
              WHEN O2.SUBTYPE$ = 'PROC' THEN DECODE(O2.INFO1 & 0x1, 0, 'FUNCTION', 'PROCEDURE')
			  WHEN O2.SUBTYPE$ = 'VIEW' THEN DECODE((O2.INFO1 >> 5) & 0xFFFF, 0, 'VIEW', 'MATERIALIZED VIEW')
              ELSE DECODE(O2.SUBTYPE$,'UTAB','TABLE','STAB','TABLE','SYNOM','SYNONYM','TRIG','TRIGGER','SEQ','SEQUENCE','CLASS','CLASS','TYPE','TYPE','DOMAIN','DOMAIN','DBLINK','DBLINK','CHARSET','CHARSET','CONTEXT','CONTEXT') 
	     END
     ELSE O2.TYPE$ END OBJECT_TYPE,
O2.CRTDATE CREATED,
INFO.STR_VALUE LAST_DDL_TIME,
INFO.STR_VALUE TIMESTAMP,
CASE WHEN O2.VALID == 'Y' THEN 'VALID' ELSE 'INVALID' END STATUS, 
DECODE(O2.INFO3 & 0X0040,64,'Y','N') TEMPORARY,
CASE 
                WHEN O2.SUBTYPE$ = 'STAB'  THEN 'Y'
                WHEN O2.SUBTYPE$ = 'UTAB'  THEN 'N'
                WHEN O2.SUBTYPE$ = 'VIEW'  THEN DECODE(O2.INFO1&0X10,16,'Y','N')
                WHEN O2.SUBTYPE$ = 'TRIG'  THEN DECODE(O2.INFO1&0X20,32,'Y','N')
                WHEN O2.SUBTYPE$ = 'PROC'  THEN DECODE(O2.INFO1&0X04,4,'Y','N')
                WHEN O2.SUBTYPE$ = 'SYNOM' THEN DECODE(O2.INFO1&0X01,1,'Y','N')
                WHEN O2.SUBTYPE$ = 'PKG'   THEN DECODE(O2.INFO1&0X02,2,'Y','N')
END GENERATED,
NULL SECONDARY,
NULL NAMESPACE,
NULL EDITION_NAME 
    FROM 
TSYSOBJECTS O1, 
(SELECT * FROM TSYSOBJECTS WHERE 
     CASE 
         WHEN SUBTYPE$='UTAB' THEN CASE WHEN PID=-1 AND INFO3 & 0x100000000 = 0 THEN TRUE ELSE FALSE END--排除所有分区子表 且不是内部自动创建表
         WHEN SUBTYPE$='TRIG' THEN CASE WHEN (INFO1 & 0x20) = 0 THEN TRUE ELSE FALSE END --非内部自动创建触发器
         WHEN SUBTYPE$='SEQ' THEN CASE WHEN (INFO2 = 0 OR INFO2 IS NULL) THEN TRUE ELSE FALSE END  --非自增列序列
         ELSE TRUE --非UTAB和触发器
     END 
) O2
LEFT JOIN (SELECT ID, MAX(TO_TIMESTAMP(STR_VALUE, 'YYYY-MM-DD HH24:MI:SS')) AS STR_VALUE FROM SYS.SYSOBJINFOS WHERE TYPE$ IN ('ALTTIME', 'TRUNCTIME') GROUP BY ID) INFO ON INFO.ID = O2.ID 
LEFT JOIN (SELECT ID,CASE WHEN TEXT.SEQNO=0 THEN 'PACKAGE' WHEN TEXT.SEQNO=1 THEN 'PACKAGE BODY' END TYPES FROM SYS.SYSTEXTS TEXT)PKG(ID,TYPES) ON PKG.ID=O2.ID AND O2.SUBTYPE$='PKG', 
TSYSOBJECTS O3
    WHERE 
             O3.TYPE$ = 'SCH' 
         AND O2.TYPE$ ='SCHOBJ' 
         AND O3.ID = O2.SCHID 
         AND O1.ID = O3.PID 
         AND SF_CHECK_PRIV_OPT (SYS_CONTEXT('USERENV', 'CURRENT_USERID'), SYS_CONTEXT('USERENV', 'CURRENT_USERTYPE'), O2.PID, O1.ID, O1.INFO1, O2.ID)
         AND O1.TYPE$='UR'
        
        UNION ALL
        
        /* 所有分区 */
        SELECT /*+adaptive_npln_flag(1) outer_cvt_inner_pull_up_cond_flag(0)*/
            O_SCH.NAME OWNER,
            O_ROOT.NAME OBJECT_NAME,
            TABPART.PARTITION_NAME SUBOBJECT_NAME,
            CAST(O_PART.ID AS NUMBER) OBJECT_ID,
            NULL DATA_OBJECT_ID,
            TABPART.OBJECT_TYPE OBJECT_TYPE,
            O_PART.CRTDATE CREATED,
            INFO.STR_VALUE LAST_DDL_TIME,
            INFO.STR_VALUE TIMESTAMP,
            CASE WHEN O_PART.VALID == 'Y' THEN 'VALID' ELSE 'INVALID' END STATUS, 
            'N' TEMPORARY,
            'N' GENERATED,
            NULL SECONDARY,
            NULL NAMESPACE,
            NULL EDITION_NAME 
         FROM 
        (SELECT /*+CNNTB_OPT_FLAG(1)*/ CONNECT_BY_ROOT(BASE_TABLE_ID) AS ROOT_TABLE_ID,BASE_TABLE_ID, PART_TABLE_ID, PARTITION_NAME, 
        CASE WHEN LEVEL > 1 THEN 'TABLE SUBPARTITION' ELSE 'TABLE PARTITION' END OBJECT_TYPE
        FROM SYS.SYSHPARTTABLEINFO WHERE PART_TABLE_ID > 1 CONNECT BY BASE_TABLE_ID = PRIOR PART_TABLE_ID START WITH BASE_TABLE_ID NOT IN (SELECT PART_TABLE_ID FROM SYS.SYSHPARTTABLEINFO) ) TABPART
        LEFT JOIN (SELECT * FROM TSYSOBJECTS WHERE TYPE$='SCHOBJ' AND SUBTYPE$='UTAB') O_ROOT ON TABPART.ROOT_TABLE_ID = O_ROOT.ID
        LEFT JOIN (SELECT * FROM TSYSOBJECTS WHERE TYPE$='SCHOBJ' AND SUBTYPE$='UTAB') O_PART ON TABPART.PART_TABLE_ID = O_PART.ID
        LEFT JOIN (SELECT ID, MAX(TO_TIMESTAMP(STR_VALUE, 'YYYY-MM-DD HH24:MI:SS')) AS STR_VALUE FROM SYS.SYSOBJINFOS WHERE TYPE$ IN ('ALTTIME', 'TRUNCTIME') GROUP BY ID) INFO ON INFO.ID = O_PART.ID,
        TSYSOBJECTS O_SCH,
        TSYSOBJECTS O_USER
        WHERE 
            O_ROOT.SCHID = O_SCH.ID
         AND O_USER.ID = O_SCH.PID
         AND SF_CHECK_PRIV_OPT (SYS_CONTEXT('USERENV', 'CURRENT_USERID'), SYS_CONTEXT('USERENV', 'CURRENT_USERTYPE'), O_ROOT.PID, O_USER.ID, O_USER.INFO1, O_ROOT.ID)
         AND O_SCH.TYPE$='SCH' AND O_USER.TYPE$='UR'

        UNION ALL
        
        SELECT 
O3.NAME OWNER,
O2.NAME OBJECT_NAME, 
NULL SUBOBJECT_NAME, 
O2.ID OBJECT_ID, 
NULL DATA_OBJECT_ID, 
CASE 
                WHEN O2.TYPE$='TABOBJ' THEN DECODE(O2.SUBTYPE$,'INDEX','INDEX','CONS','CONSTRAINT','CNTIND','CONTEXT INDEX') 
                ELSE O2.TYPE$ 
            END OBJECT_TYPE,
O2.CRTDATE CREATED,
TO_TIMESTAMP(INFO.STR_VALUE, 'YYYY-MM-DD HH24:MI:SS') LAST_DDL_TIME,
TO_TIMESTAMP(INFO.STR_VALUE, 'YYYY-MM-DD HH24:MI:SS') TIMESTAMP,
CASE WHEN O2.VALID == 'Y' THEN 'VALID' ELSE 'INVALID' END STATUS, 
DECODE(O2.INFO3 & 0X0040,64,'Y','N') TEMPORARY,
CASE 
    WHEN O2.SUBTYPE$ = 'INDEX' THEN CASE WHEN (SELECT FLAG & 0x01 FROM  SYS.SYSINDEXES WHERE ID=O2.ID) = 1 THEN 'Y' ELSE 'N' END
END GENERATED,
NULL SECONDARY,
NULL NAMESPACE,
NULL EDITION_NAME 
    FROM 
TSYSOBJECTS O1, 
TSYSOBJECTS O2 
LEFT JOIN SYS.SYSOBJINFOS INFO ON INFO.ID = O2.ID AND INFO.TYPE$ = 'ALTTIME', 
TSYSOBJECTS O3
    WHERE 
             O3.TYPE$ = 'SCH' 
         AND O2.TYPE$ IN ('TABOBJ', 'DMNOBJ') 
         AND O3.ID = O2.SCHID 
         AND O1.ID = O3.PID 
         AND SF_CHECK_PRIV_OPT (SYS_CONTEXT('USERENV', 'CURRENT_USERID'), SYS_CONTEXT('USERENV', 'CURRENT_USERTYPE'), O2.PID, O1.ID, O1.INFO1, O2.ID)
         AND O1.TYPE$='UR';

跨用户执行函数报错 -5731 用户不匹配 解决记录

作者

跨用户执行函数报错 -5731 用户不匹配解决记录