DM8安全版数据库各模式之间数据互相访问

DM8安全版数据库各模式之间数据互相访问
DM8安全版数据库管理系统，在项目实施的过程中，应用厂商从旧服务器上把输入导入/迁移到DM数据库后，存在各用户下模式之间不能互相访问。应用连接DM数据库提示没有某个模式下的表的访问权限，导致应用部署完后系统某些模块调取不了数据。

DM安全版数据库对权限很细化

一、创建用户

创建用户：
登录SYSDBA用户

CREATE USER TEST01；
CREATE USER TEST02；
CREATE USER TEST03；

授予DBA权限

GRANT DBA TO TEST01;
GRANT DBA TO TEST02;
GRANT DBA TO TEST03;

设置用户密码

ALTER USER TEST01 IDENTIFIED BY "Dameng123";
ALTER USER TEST02 IDENTIFIED BY "Dameng123";
ALTER USER TEST03 IDENTIFIED BY "Dameng123";

二、导入/迁移数据

省略~~~~

三、创建角色

创建角色CZT，给CZT角色赋予权限。
登录SYSDBA用户

create role "CZT";
grant "DBA","PUBLIC","RESOURCE","SOI","VTI" to "CZT";

分别登录三个用户，赋予对象权限的ALL。

登录TEST01用户

DECLARE
    SQLSTMT STRING;
    CURSOR CUR FOR SELECT ID,NAME  FROM SYSOBJECTS  WHERE  TYPE$ = 'SCHOBJ' AND SUBTYPE$ IN ('STAB','UTAB') AND  SCHID=(SELECT ID FROM SYSOBJECTS WHERE TYPE$='SCH' AND NAME='TEST01' );
    TYPE MYREC IS CUR%ROWTYPE;
    REC_V MYREC;
    BEGIN
    FOR REC_V IN CUR LOOP
    SQLSTMT = 'grant SELECT,INSERT,DELETE,UPDATE,REFERENCES,SELECT FOR DUMP ON "TEST01"."'|| REC_V.NAME  || '" TO "CZT";';
    EXECUTE IMMEDIATE SQLSTMT;
    END LOOP;
    COMMIT;
END;

登录TEST02用户

DECLARE
    SQLSTMT STRING;
    CURSOR CUR FOR SELECT ID,NAME  FROM SYSOBJECTS  WHERE  TYPE$ = 'SCHOBJ' AND SUBTYPE$ IN ('STAB','UTAB') AND  SCHID=(SELECT ID FROM SYSOBJECTS WHERE TYPE$='SCH' AND NAME='TEST02' );
    TYPE MYREC IS CUR%ROWTYPE;
    REC_V MYREC;
    BEGIN
    FOR REC_V IN CUR LOOP
    SQLSTMT = 'grant SELECT,INSERT,DELETE,UPDATE,REFERENCES,SELECT FOR DUMP ON "TEST02"."'|| REC_V.NAME  || '" TO "CZT";';
    EXECUTE IMMEDIATE SQLSTMT;
    END LOOP;
    COMMIT;
END;

登录TEST03用户

DECLARE
    SQLSTMT STRING;
    CURSOR CUR FOR SELECT ID,NAME  FROM SYSOBJECTS  WHERE  TYPE$ = 'SCHOBJ' AND SUBTYPE$ IN ('STAB','UTAB') AND  SCHID=(SELECT ID FROM SYSOBJECTS WHERE TYPE$='SCH' AND NAME='TEST03' );
    TYPE MYREC IS CUR%ROWTYPE;
    REC_V MYREC;
    BEGIN
    FOR REC_V IN CUR LOOP
    SQLSTMT = 'grant SELECT,INSERT,DELETE,UPDATE,REFERENCES,SELECT FOR DUMP ON "TEST03"."'|| REC_V.NAME  || '" TO "CZT";';
    EXECUTE IMMEDIATE SQLSTMT;
    END LOOP;
    COMMIT;
END;

四、给用户赋予角色权限

登录SYSDBA用户，给三个用户分别赋予CZT角色。

GRANT "CZT" TO "TEST01";
GRANT "CZT" TO "TEST02";
GRANT "CZT" TO "TEST03";