达梦DatabaseMetaData获取元数据时返回全部表,没有权限控制

达梦DatabaseMetaData获取元数据时返回全部表,没有权限控制

在用DatabaseMetaData.getTables()时,如果获取的是非当前用户模式的表时,会返回全部表,不会进行权限控制,

解决办法:更换jdbc驱动,建议去达梦官网下载达梦数据库管理工具,用它那里面的工具
根本原因:在执行查询语句时,没有对用户和模式进行查询

/*+ gateway metadata JDBC_getTables(null,'DWD','%','[TABLE]')*/
SELECT
        /*+ MAX_OPT_N_TABLES(5) */
        NULL                                                                                                                                AS TABLE_CAT  ,
        SCHEMAS.NAME                                                                                                                        AS TABLE_SCHEM,
        TABS.NAME                                                                                                                           AS TABLE_NAME ,
        CASE TABS.SUBTYPE$ WHEN 'UTAB' THEN 'TABLE' WHEN 'VIEW' THEN 'VIEW' WHEN 'STAB' THEN 'SYSTEM TABLE' WHEN 'SYNOM' THEN 'SYNONYM' END AS TABLE_TYPE ,
        (
                SELECT
                        COMMENT$
                FROM
                        SYSTABLECOMMENTS
                WHERE
                        SCHNAME = SCHEMAS.NAME
                    AND TVNAME  = TABS.NAME
        )    AS REMARKS                  ,
        NULL AS TYPE_CAT                 ,
        NULL AS TYPE_SCHEM               ,
        NULL AS TYPE_NAME                ,
        NULL AS SELF_REFERENCING_COL_NAME,
        NULL AS REF_GENERATION
FROM
        (
                SELECT ID, PID, NAME FROM SYSOBJECTS WHERE TYPE$ = 'SCH' AND NAME = 'DWD'
        )
        SCHEMAS,
        (
                SELECT
                        ID      ,
                        SCHID   ,
                        SUBTYPE$,
                        NAME
                FROM
                        SYSOBJECTS
                WHERE
                        TYPE$     = 'SCHOBJ'
                    AND SUBTYPE$ IN ('UTAB', 'STAB', 'VIEW', 'SYNOM')
                    AND
                        (
                                (
                                        SUBTYPE$                                    ='UTAB'
                                    AND CAST((INFO3 & 0x00FF & 0x003F) AS INT) not in ( 9, 27, 29, 25, 12, 7, 21, 23, 18, 5)
                                )
                        )
        )
        TABS
WHERE
        TABS.SCHID = SCHEMAS.ID
    AND
        (
                SF_CHECK_PRIV_OPT(UID(), CURRENT_USERTYPE(), TABS.ID, SCHEMAS.PID, -1, TABS.ID) = 1
        )
ORDER BY
        TABLE_TYPE ASC ,
        TABLE_SCHEM ASC,
        TABLE_NAME ASC;

重点在于这段sql条件

AND
        (
                SF_CHECK_PRIV_OPT(UID(), CURRENT_USERTYPE(), TABS.ID, SCHEMAS.PID, -1, TABS.ID) = 1
        )

我们先执行没有这段代码的结果,发现有84条

加上这个条件再查询,发现只有2条了,可见生效了