CNDBA社区
1 权限说明
新建的业务的系统，一般都需要创建独立的业务用户，而为了保障安全性，一般业务用户的权限按照最小权限的原则分配。
关于达梦数据库用户权限的基本概念说明参考我之前的博客：
DM 达梦数据库 用户管理 说明
https://www.cndba.cn/dave/article/3567
达梦生产环境的业务用户，默认推荐如下3个角色的权限：
SQL>grant “PUBLIC”,”VTI”,”SOI” to “CNDBA”;
不同角色的说明如下，这里只是列举了部分，更多角色参考达梦的安全管理手册：
也可以使用SQL查询所有角色，当然三权分立和四权分立版本角色是不一样的：
[dave@www.cndba.cn ~]$ disql SYSDBA/SYSDBA@localhost:5237
Server[localhost:5237]:mode is normal, state is open
login used time : 26.420(ms)
disql V8
SQL> select * from SYS.DBA_ROLES;
LINEID ROLE PASSWORD_REQUIRED AUTHENTICATION_TYPE
---------- ---------------- ----------------- -------------------
1 DBA NULL NULL
2 DB_AUDIT_ADMIN NULL NULL
3 DB_AUDIT_OPER NULL NULL
4 DB_AUDIT_PUBLIC NULL NULL
5 DB_AUDIT_SOI NULL NULL
6 DB_AUDIT_SVI NULL NULL
7 DB_AUDIT_VTI NULL NULL
8 DB_POLICY_ADMIN NULL NULL
9 DB_POLICY_OPER NULL NULL
10 DB_POLICY_PUBLIC NULL NULL
11 DB_POLICY_SOI NULL NULL
LINEID ROLE PASSWORD_REQUIRED AUTHENTICATION_TYPE
---------- ------------- ----------------- -------------------
12 DB_POLICY_SVI NULL NULL
13 DB_POLICY_VTI NULL NULL
14 PUBLIC NULL NULL
15 RESOURCE NULL NULL
16 SOI NULL NULL
17 SVI NULL NULL
18 SYS_ADMIN NULL NULL
19 VTI NULL NULL
19 rows got
used time: 32.220(ms). Execute id is 700.
SQL>
查看PUBLIC角色对应的权限：
SQL> select * from dba_sys_privs where grantee='PUBLIC';
LINEID GRANTEE PRIVILEGE ADMIN_OPTION
---------- ------- ------------------------ ------------
1 PUBLIC INSERT TABLE NO
2 PUBLIC UPDATE TABLE NO
3 PUBLIC DELETE TABLE NO
4 PUBLIC SELECT TABLE NO
5 PUBLIC REFERENCES TABLE NO
6 PUBLIC GRANT TABLE NO
7 PUBLIC INSERT VIEW NO
8 PUBLIC UPDATE VIEW NO
9 PUBLIC DELETE VIEW NO
10 PUBLIC SELECT VIEW NO
11 PUBLIC GRANT VIEW NO
12 PUBLIC EXECUTE PROCEDURE NO
13 PUBLIC GRANT PROCEDURE NO
14 PUBLIC SELECT SEQUENCE NO
15 PUBLIC GRANT SEQUENCE NO
16 PUBLIC EXECUTE PACKAGE NO
17 PUBLIC GRANT PACKAGE NO
18 PUBLIC SELECT ANY DICTIONARY NO
19 PUBLIC SELECT MATERIALIZED VIEW NO
20 PUBLIC GRANT DOMAIN NO
21 PUBLIC USAGE DOMAIN NO
22 PUBLIC DUMP TABLE NO
22 rows got
used time: 0.404(ms). Execute id is 702.
SQL>
SQL> select * from dba_sys_privs where grantee='VTI';
no rows
used time: 1.229(ms). Execute id is 703.
SQL> select * from dba_sys_privs where grantee='SOI';
no rows
used time: 1.298(ms). Execute id is 704.
SQL> select * from dba_sys_privs where grantee='RESOURCE';
LINEID GRANTEE PRIVILEGE ADMIN_OPTION
---------- -------- ------------------------ ------------
1 RESOURCE CREATE SCHEMA NO
2 RESOURCE CREATE TABLE NO
3 RESOURCE CREATE VIEW NO
4 RESOURCE CREATE PROCEDURE NO
5 RESOURCE CREATE SEQUENCE NO
6 RESOURCE CREATE TRIGGER NO
7 RESOURCE CREATE INDEX NO
8 RESOURCE CREATE CONTEXT INDEX NO
9 RESOURCE CREATE LINK NO
10 RESOURCE CREATE PACKAGE NO
11 RESOURCE CREATE SYNONYM NO
12 RESOURCE CREATE PUBLIC SYNONYM NO
13 RESOURCE INSERT TABLE NO
14 RESOURCE UPDATE TABLE NO
15 RESOURCE DELETE TABLE NO
16 RESOURCE SELECT TABLE NO
17 RESOURCE REFERENCES TABLE NO
18 RESOURCE GRANT TABLE NO
19 RESOURCE INSERT VIEW NO
20 RESOURCE UPDATE VIEW NO
21 RESOURCE DELETE VIEW NO
22 RESOURCE SELECT VIEW NO
23 RESOURCE GRANT VIEW NO
24 RESOURCE EXECUTE PROCEDURE NO
25 RESOURCE GRANT PROCEDURE NO
26 RESOURCE SELECT SEQUENCE NO
27 RESOURCE GRANT SEQUENCE NO
28 RESOURCE EXECUTE PACKAGE NO
29 RESOURCE GRANT PACKAGE NO
30 RESOURCE SELECT ANY DICTIONARY NO
31 RESOURCE CREATE MATERIALIZED VIEW NO
32 RESOURCE SELECT MATERIALIZED VIEW NO
33 RESOURCE CREATE DOMAIN NO
34 RESOURCE GRANT DOMAIN NO
35 RESOURCE USAGE DOMAIN NO
36 RESOURCE DUMP TABLE NO
37 RESOURCE CREATE PARTITION GROUP NO
38 RESOURCE USAGE PARTITION GROUP NO
38 rows got
used time: 1.453(ms). Execute id is 705.
SQL>
通过查询，可以看出，如果是普通的业务用户，只需要操作业务自己的数据，那么”PUBLIC”,”VTI”,”SOI” 三个角色就满足DML 操作的需求，如果要DDL的权限，那么还需要增加”RESOURCE”资源的角色，但这里也只是多了CREAE 对象的 DDL权限，比如ALTER 操作就需要”DBA” 角色对应的权限。
RESOURCE 比 PUBLIC 多的权限：
SQL> select privilege from dba_sys_privs where grantee='RESOURCE'
minus
select privilege from dba_sys_privs where grantee='PUBLIC';
LINEID PRIVILEGE
---------- ------------------------
1 CREATE LINK
2 CREATE SEQUENCE
3 CREATE SCHEMA
4 CREATE VIEW
5 CREATE DOMAIN
6 CREATE INDEX
7 CREATE PUBLIC SYNONYM
8 CREATE PARTITION GROUP
9 CREATE PROCEDURE
10 CREATE TRIGGER
11 USAGE PARTITION GROUP
12 CREATE PACKAGE
13 CREATE TABLE
14 CREATE CONTEXT INDEX
15 CREATE MATERIALIZED VIEW
16 CREATE SYNONYM
16 rows got
used time: 3.017(ms). Execute id is 706.
SQL>
DBA 比 RESOURCE 多的权限：
SQL> select privilege from dba_sys_privs where grantee='DBA'
minus
select privilege from dba_sys_privs where grantee='RESOURCE';
LINEID PRIVILEGE
---------- ----------------------------
1 GRANT ANY VIEW
2 CREATE ANY DIRECTORY
3 GRANT ANY DOMAIN
4 CREATE ANY TRIGGER
5 DROP PUBLIC SYNONYM
6 CREATE ANY DOMAIN
7 GRANT ANY SEQUENCE
8 CREATE ANY SCHEMA
9 CREATE ANY VIEW
10 ADMIN JOB
11 DROP ANY SCHEMA
12 CREATE ANY PACKAGE
13 COMMENT ANY TABLE
14 DROP ANY CONTEXT INDEX
15 CREATE ANY MATERIALIZED VIEW
16 DROP ANY SYNONYM
17 ALTER ANY MATERIALIZED VIEW
18 DROP ANY PARTITION GROUP
19 DROP ANY CONTEXT
20 RESTORE DATABASE
21 ADMIN ANY ROLE
22 UPDATE ANY TABLE
23 SELECT ANY MATERIALIZED VIEW
24 DROP ANY MATERIALIZED VIEW
25 GRANT ANY OBJECT PRIVILEGE
26 GRANT ANY TABLE
27 CREATE TABLESPACE
28 CREATE ANY SYNONYM
29 CREATE PROFILE
30 DROP ANY TRIGGER
31 GRANT ANY PROCEDURE
32 CREATE ANY INDEX
33 CREATE ANY CONTEXT
34 DROP ANY SEQUENCE
35 CREATE ANY PARTITION GROUP
36 DROP ANY TABLE
37 ALTER ANY VIEW
38 DUMP ANY TABLE
39 UPDATE ANY VIEW
40 CREATE REPLICATE
41 EXECUTE ANY PACKAGE
42 SELECT ANY TABLE
43 ALTER ANY CONTEXT INDEX
44 DELETE ANY TABLE
45 USAGE ANY DOMAIN
46 CREATE ANY SEQUENCE
47 GRANT ANY PACKAGE
48 DROP PROFILE
49 DELETE ANY VIEW
50 CREATE ANY TABLE
51 CREATE ANY CONTEXT INDEX
52 DROP TABLESPACE
53 USAGE ANY PARTITION GROUP
54 CREATE ANY LINK
55 INSERT ANY TABLE
56 DROP ANY INDEX
57 ADMIN ANY DATABASE PRIVILEGE
58 DROP ANY PROCEDURE
59 ALTER TABLESPACE
60 DROP ANY PACKAGE
61 DROP ANY VIEW
62 ADMIN BUFFER
63 SELECT ANY VIEW
64 ALTER DATABASE
65 ALTER ANY INDEX
66 EXECUTE ANY PROCEDURE
67 ALTER ANY TABLE
68 ALTER ANY TRIGGER
69 ALTER PROFILE
70 ALTER ANY SEQUENCE
71 BACKUP DATABASE
72 DROP USER
73 DROP ANY DOMAIN
74 CREATE ANY PROCEDURE
75 DROP ANY LINK
76 ALTER USER
77 DROP ROLE
78 ALTER REPLICATE
79 DROP ANY DIRECTORY
80 SELECT ANY SEQUENCE
81 CREATE ROLE
82 REFERENCES ANY TABLE
83 CREATE USER
84 GRANT ANY CONTEXT
85 DROP REPLICATE
86 ADMIN REPLAY
87 INSERT ANY VIEW
87 rows got
used time: 3.439(ms). Execute id is 707.
SQL>
2 小结
对于安全严格管控的生产系统，那么业务用户推荐如下3个角色：
SQL>grant "PUBLIC","VTI","SOI" to "CNDBA";
如果考虑CREATE 权限下放，那么需要加上RESOURCE角色：
SQL>grant "RESOURCE","PUBLIC","VTI","SOI" to "CNDBA";
对于ALTER，DROP 类的DDL 权限，生产环境建议是由DBA 管理，有相关的业务变更时，首先由DBA审核，审核通过后由DBA执行，业务侧不应具有这种权限。
版权声明：本文为博主原创文章，未经博主允许不得转载。
