当前位置：首页 > article >正文

达梦数据库授权给某个用户查询其他指定用户下所有表的权限

article 2025/2/28 17:29:47

方法1：

新版本有一个数据库参数

GRANT_SCHEMA，表示是否开启授予和回收模式权限功能。0：否；1：是

此参数为静态参数，默认是0，将改参数修改为1后，重启数据库生效。

将参数修改为1

SP_SET_PARA_VALUE(2,'GRANT_SCHEMA',1);

参数生效后，可直接执行SQL进行授权，例如将模式 B 下所有对象的查询权限授予用户 A

GRANT SELECT ON SCHEMA B TO A;

方法2：

可以通过创建一个存储过程来实现：

CREATE OR REPLACE PROCEDURE GRANT_ALL_SEL(GRANTOR IN VARCHAR2, GRANTEE IN VARCHAR2) AS
    SQLSTMT VARCHAR2(4000);
    CURSOR CUR FOR SELECT ID, NAME FROM SYSOBJECTS WHERE TYPE$ = 'SCHOBJ' AND SUBTYPE$ IN ('STAB', 'UTAB') AND (PID = -1 OR PID = 0) AND SCHID = (SELECT ID FROM SYSOBJECTS WHERE TYPE$ = 'SCH' AND NAME = GRANTOR);
    TYPE MYREC IS CUR%ROWTYPE;
    REC_V MYREC;
    TYPE R_PRIV IS RECORD (CUR_PRIV_NAME VARCHAR2(128), CUR_BE_GRANT CHAR(1), CUR_CAN_GRANT CHAR(1), CUR_IS_GRANT CHAR(1), ORG_PRIV_NAME VARCHAR2(128), ORG_CAN_GRANT CHAR(1)); 
    TYPE T_PRIV IS TABLE OF R_PRIV;
    t_p T_PRIV;
    OBJ_PRIV CURSOR;
BEGIN
    FOR REC_V IN CUR LOOP
        OPEN OBJ_PRIV FOR
            WITH CUR_PRIVS(PRIV_NAME, BE_GRANT, CAN_GRANT, ISGRANT) AS
            (
                SELECT 'SELECT', 'Y', 'N', 'Y' FROM DUAL 
            )
            SELECT CUR_PRIVS.*, SF_GET_SYS_PRIV(ORG_PRIV.PRIVID), ORG_PRIV.GRANTABLE
            FROM CUR_PRIVS
            LEFT JOIN (SELECT * FROM SYSGRANTS WHERE OBJID = REC_V.ID AND COLID = -1 AND URID = 50331751) ORG_PRIV 
            ON CUR_PRIVS.PRIV_NAME = SF_GET_SYS_PRIV(ORG_PRIV.PRIVID);

        FETCH OBJ_PRIV BULK COLLECT INTO t_p;
        CLOSE OBJ_PRIV;

        FOR I IN 1..t_p.COUNT LOOP
            SQLSTMT := NULL;
            IF (t_p(I).CUR_IS_GRANT = 'Y') THEN
                IF (t_p(I).CUR_CAN_GRANT = 'Y') THEN
                    SQLSTMT := 'GRANT ' || t_p(I).CUR_PRIV_NAME || ' ON "' || GRANTOR || '"."' || REC_V.NAME || '" TO "' || GRANTEE || '" WITH GRANT OPTION';
                ELSE
                    IF (t_p(I).ORG_PRIV_NAME IS NULL) THEN
                        SQLSTMT := 'GRANT ' || t_p(I).CUR_PRIV_NAME || ' ON "' || GRANTOR || '"."' || REC_V.NAME || '" TO "' || GRANTEE || '"';
                    ELSE
                        IF (t_p(I).ORG_CAN_GRANT = 'Y') THEN
                            SQLSTMT := 'REVOKE GRANT OPTION FOR ' || t_p(I).CUR_PRIV_NAME || ' ON "' || GRANTOR || '"."' || REC_V.NAME || '" FROM "' || GRANTEE || '" CASCADE';
                        END IF;
                    END IF;
                END IF;
            ELSE
                IF (t_p(I).CUR_BE_GRANT = 'Y' AND t_p(I).ORG_PRIV_NAME IS NOT NULL) THEN
                    SQLSTMT := 'REVOKE ' || t_p(I).CUR_PRIV_NAME || ' ON "' || GRANTOR || '"."' || REC_V.NAME || '" FROM "' || GRANTEE || '" CASCADE';
                ELSE
                    IF (t_p(I).CUR_CAN_GRANT = 'Y' AND t_p(I).ORG_CAN_GRANT = 'Y') THEN
                        SQLSTMT := 'REVOKE GRANT OPTION FOR ' || t_p(I).CUR_PRIV_NAME || ' ON "' || GRANTOR || '"."' || REC_V.NAME || '" FROM "' || GRANTEE || '" CASCADE';
                    END IF;
                END IF;
            END IF;

            IF (SQLSTMT IS NOT NULL) THEN
                EXECUTE IMMEDIATE SQLSTMT;
            END IF;
        END LOOP;
    END LOOP;

    COMMIT;
END GRANT_ALL_SEL;

然后调用此存储过程即可，例如将模式 B 下所有对象的查询权限授予用户 A