dm8数据库中模式的介绍与使用

数据库中模式的介绍与使用

1.模式的概念
模式即SCHEMA，是数据库的对象，可以将其理解为一个独立空间，不同的模式下可以拥有相同的名称的表而不会发生冲突，只要有权限，不同模式下的表可以相互查询调用，在模式中可以拥有以下对象：表、视图、索引、触发器、存储过程、函数、序列、全文索引、包、同义词、类、外部链接。

2.模式与用户
SQL> select a.name as username, b.name as schenma from sysobjects a inner join sysobjects b on a.id = b.pid where b.subtype$ is null order by username desc;
行号 USERNAME SCHENMA

---

1 SYSSSO SYSSSO
2 SYSDBA SYSDBA
3 SYSAUDITOR SYSAUDITOR
4 SYS SYS
5 SYS CTISYS
查看目前所有用户与模式的对应关系。
已用时间: 3.189(毫秒). 执行号:600.
SQL> CREATE USER DMDBA IDENTIFIED BY DMDBA_123 DEFAULT TABLESPACE MAIN DEFAULT INDEX TABLESPACE MAIN;
操作已执行
已用时间: 10.292(毫秒). 执行号:601.
创建一个新用户。
SQL> select a.name as username, b.name as schenma from sysobjects a inner join sysobjects b on a.id = b.pid where b.subtype$ is null order by username desc;
行号 USERNAME SCHENMA

---

1 SYSSSO SYSSSO
2 SYSDBA SYSDBA
3 SYSAUDITOR SYSAUDITOR
4 SYS SYS
5 SYS CTISYS
6 DMDBA DMDBA
6 rows got
已用时间: 0.892(毫秒). 执行号:602.
与用户同名的模式也被创建。
SQL> CONN DMDBA/DMDBA_123
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 2.489(ms)
SQL> CREATE SCHEMA DMDBA2
2 /
CREATE SCHEMA DMDBA2
[-5512]:没有创建模式权限.
已用时间: 0.502(毫秒). 执行号:0.
SQL> select * from dba_tab_privs where grantee=‘DMDBA’;
select * from dba_tab_privs where grantee=‘DMDBA’;
[-5504]:没有[DBA_TAB_PRIVS]对象的查询权限.
已用时间: 3.635(毫秒). 执行号:0.
SQL> CONN SYSDBA/SYSDBA
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 2.417(ms)
SQL> select * from dba_tab_privs where grantee=‘DMDBA’;
未选定行
已用时间: 23.902(毫秒). 执行号:700.
SQL> select * from dba_sys_privs where grantee=‘DMDBA’;
行号 GRANTEE PRIVILEGE ADMIN_OPTION

---

1 DMDBA CREATE SESSION NO
已用时间: 1.823(毫秒). 执行号:701.
SQL> select * from dba_role_privs where grantee=‘DMDBA’;
行号 GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE

---

1 DMDBA PUBLIC N NULL
用户被创建时，没有任何对象权限，仅有连接数据库的系统权限，以及PUBLIC角色。
SQL> grant DBA to DMDBA;
操作已执行
已用时间: 8.997(毫秒). 执行号:702.
为用户赋权，给予DBA角色权限
SQL> select * from dba_role_privs where grantee=‘DMDBA’;
行号 GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE

---

1 DMDBA DBA N NULL
2 DMDBA PUBLIC N NULL
已用时间: 3.095(毫秒). 执行号:707.
可以查出用户DMDBA目前拥有了DBA的角色权限。
SQL> CONN DMDBA/DMDBA_123
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 2.433(ms)
SQL> CREATE SCHEMA DMDBA2
2 /
操作已执行
已用时间: 10.833(毫秒). 执行号:800.
SQL> select a.name as username, b.name as schenma from sysobjects a inner join sysobjects b on a.id = b.pid where b.subtype$ is null order by username desc;
行号 USERNAME SCHENMA

---

1 SYSSSO SYSSSO
2 SYSDBA SYSDBA
3 SYSAUDITOR SYSAUDITOR
4 SYS SYS
5 SYS CTISYS
6 DMDBA DMDBA2
7 DMDBA DMDBA
7 rows got
创建模式时，该模式默认为该用户所有。
如果想要为模式指定其他的用户，可以这么操作：
SQL> CREATE SCHEMA TEST AUTHORIZATION SYSDBA
2 /
SQL> select a.name as username, b.name as schenma from sysobjects a inner join sysobjects b on a.id = b.pid where b.subtype$ is null order by username desc;
行号 USERNAME SCHENMA

---

1 SYSSSO SYSSSO
2 SYSDBA TEST
3 SYSDBA SYSDBA
4 SYSAUDITOR SYSAUDITOR
5 SYS SYS
6 SYS CTISYS
7 DMDBA DMDBA2
8 DMDBA DMDBA
8 rows got
新建的TEST模式，所属用户为SYSDBA
SQL> DROP SCHEMA DMDBA2 RESTRICT
2 /
操作已执行
已用时间: 32.177(毫秒). 执行号:804.
模式可以删除，模式删除后，模式中的对象也会被删除，所有在数据库中的删除操作都需要小心。
SQL> CONN SYSDBA/SYSDBA
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 2.322(ms)
SQL> select a.name as username, b.name as schenma from sysobjects a inner join sysobjects b on a.id = b.pid where b.subtype$ is null order by username desc;
行号 USERNAME SCHENMA

---

1 SYSSSO SYSSSO
2 SYSDBA TEST
3 SYSDBA SYSDBA
4 SYSAUDITOR SYSAUDITOR
5 SYS CTISYS
6 SYS SYS
7 DMDBA DMDBA2
8 DMDBA TEST2
9 DMDBA DMDBA
9 rows got
已用时间: 2.064(毫秒). 执行号:900.
SQL> DROP USER DMDBA
2 /
操作已执行
已用时间: 81.092(毫秒). 执行号:901.
SQL> select a.name as username, b.name as schenma from sysobjects a inner join sysobjects b on a.id = b.pid where b.subtype$ is null order by username desc;
行号 USERNAME SCHENMA

---

1 SYSSSO SYSSSO
2 SYSDBA TEST
3 SYSDBA SYSDBA
4 SYSAUDITOR SYSAUDITOR
5 SYS SYS
6 SYS CTISYS
6 rows got
已用时间: 0.918(毫秒). 执行号:902.
在删除用户时，会删除用户底下所有的模式，所以在达梦数据库中，如果一个用户有多个模式，在删除一个模式时，千万注意不能使用drop user的方式，要用drop schema删除。
SQL> CREATE USER MONDAY IDENTIFIED BY DMDBA_123 DEFAULT TABLESPACE MAIN DEFAULT INDEX TABLESPACE MAIN;
操作已执行
已用时间: 10.295(毫秒). 执行号:903.
SQL> CREATE USER FRIDAY IDENTIFIED BY DMDBA_123 DEFAULT TABLESPACE MAIN DEFAULT INDEX TABLESPACE MAIN;
操作已执行
已用时间: 12.575(毫秒). 执行号:904.
SQL> GRANT RESOURCE TO MONDAY;
操作已执行
已用时间: 7.736(毫秒). 执行号:905.
SQL> GRANT RESOURCE TO FRIDAY;
操作已执行
已用时间: 7.404(毫秒). 执行号:906.
SQL> CONN MONDAY/DMDBA_123
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 3.146(ms)
SQL> CREATE TABLE TEST1 (NAME VARCHAR(10));
操作已执行
已用时间: 13.722(毫秒). 执行号:1100.
SQL> INSERT INTO TEST1 VALUES (‘LOUXINRU’);
影响行数 1
已用时间: 0.618(毫秒). 执行号:1101.
SQL> COMMIT;
操作已执行
已用时间: 3.717(毫秒). 执行号:1102.
SQL> CONN FRIDAY/DMDBA_123
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 2.477(ms)
SQL> CREATE TABLE TEST1 (NAME VARCHAR(10));
操作已执行
已用时间: 12.206(毫秒). 执行号:1200.
SQL> INSERT INTO TEST1 VALUES (‘LOUXINRU’);
影响行数 1
已用时间: 0.701(毫秒). 执行号:1201.
SQL> COMMIT;
操作已执行
已用时间: 3.569(毫秒). 执行号:1202.
SQL> SELECT * FROM MONDAY.TEST1;
SELECT * FROM MONDAY.TEST1;
[-5504]:没有[TEST1]对象的查询权限.
已用时间: 0.477(毫秒). 执行号:0.
SQL> SELECT * FROM FRIDAY.TEST1;
行号 NAME

---

1 LOUXINRU
已用时间: 0.562(毫秒). 执行号:1203.
默认情况下，各模式之间不可直接访问。
SQL> CONN SYSDBA/SYSDBA;
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 2.341(ms)
SQL> GRANT SELECT ON MONDAY.TEST1 TO FRIDAY;
操作已执行
SQL> CONN FRIDAY/DMDBA_123
给FRIDAY用户授予MONDAY.TEST1表查询权限
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 2.141(ms)
SQL> SELECT * FROM MONDAY.TEST1;
行号 NAME

---

1 LOUXINRU
此时FRIDAY用户可以查询MONDAY.TEST1。
SQL> CONN MONDAY/DMDBA_123
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 2.263(ms)
SQL> CREATE TABLE TEST2 (NAME VARCHAR(10));
操作已执行
已用时间: 11.655(毫秒). 执行号:1500.
SQL> INSERT INTO TEST2 VALUES (‘LOUXINRU’);
影响行数 1
已用时间: 0.754(毫秒). 执行号:1501.
SQL> COMMIT;
此时MONDAY创建新表TEST2，FRIDAY想要访问还需要再次赋权。想要一次性可以用SYSDBA做以下操作：
SQL> CONN SYSDBA/SYSDBA;
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 2.396(ms)
SQL> GRANT SELECT ANY TABLE TO FRIDAY
2 /
操作已执行
SQL> CONN FRIDAY/DMDBA_123
SQL> SELECT * FROM MONDAY.TEST2;
行号 NAME

---

1 LOUXINRU
SQL> CONN SYSDBA/SYSDBA;
SQL> CREATE USER LOUXINRU IDENTIFIED BY DMDBA_123 DEFAULT TABLESPACE MAIN DEFAULT INDEX TABLESPACE MAIN;
操作已执行
已用时间: 14.032(毫秒). 执行号:1800.
SQL> GRANT RESOURCE TO LOUXINRU;
操作已执行
已用时间: 7.682(毫秒). 执行号:1801.
SQL> CONN LOUXINRU/DMDBA_123
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 2.382(ms)
SQL> CREATE TABLE TEST1 (NAME VARCHAR(10));
操作已执行
已用时间: 14.021(毫秒). 执行号:1900.
SQL> INSERT INTO TEST1 VALUES (‘LOUXINRU’);
影响行数 1
已用时间: 0.646(毫秒). 执行号:1901.
SQL> COMMIT;
操作已执行
已用时间: 5.284(毫秒). 执行号:1902.
SQL> CONN FRIDAY/DMDBA_123
SQL> SELECT * FROM LOUXINRU.TEST1;
行号 NAME

---

1 LOUXINRU
可以看出，我并没有单独赋予FRIDAY用户LOUXINRU模式下的任何权限，但这时候FRIDAY用户拥有该数据库下，所有模式的查询权限。
SQL> GRANT SELECT ON MONDAY.* TO LOUXINRU;
GRANT SELECT ON MONDAY.* TO LOUXINRU;
GRANT SELECT ON MONDAY.* TO LOUXINRU;
*
第 1 行, 第 22 列[.*]附近出现错误[-2007]:
语法分析出错.
已用时间: 0.156(毫秒). 执行号:0.
在DM中，并没有像MYSQL一样直接授予一整个SCHEMA权限的概念，想要授予一个模式下的所有表的权限，只能根据对象来单独赋权。
SQL> alter session set current_schema=FRIDAY;
操作已执行
已用时间: 0.518(毫秒). 执行号:0.
SQL> SELECT * FROM TEST1;
行号 NAME

---

1 LOUXINRU
可以使用ALTER SESSION用户来切换SCHEMA。
SQL> CREATE SCHEMA FRIDAY2
2 /
SQL> CREATE TABLE FRIDAY2.TEST1 (NAME VARCHAR(5));
操作已执行
SQL> INSERT INTO FRIDAY2.TEST1 VALUES (‘Ruby’);
影响行数 1
已用时间: 0.654(毫秒). 执行号:2205.
SQL> COMMIT;
操作已执行
用户有默认的模式，在一个用户有多个模式的情况下，在操作非默认模式时，必须带模式名。
要想切换默认模式，可以做如下操作：
SQL> SET SCHEMA FRIDAY2;
操作已执行
已用时间: 0.230(毫秒). 执行号:0.
SQL> SELECT * FROM TEST1;
行号 NAME

---

1 Ruby
此时，查询FRIDAY2模式下的表，不再需要带模式名，但是查询FRIDAY模式下的表，则需要带模式名。SET命令是临时性的修改默认模式，仅在当前SESSION下有效。
SQL> CONN MONDAY/DMDBA_123
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 2.423(ms)
SQL> DROP SCHEMA MONDAY;
DROP SCHEMA MONDAY;
第1 行附近出现错误[-6509]:当前对象被占用.
已用时间: 0.277(毫秒). 执行号:0.
SQL> CONN SYSDBA/SYSDBA
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 2.385(ms)
SQL> DROP SCHEMA MONDAY;
DROP SCHEMA MONDAY;
第1 行附近出现错误[-2506]:试图删除用户[MONDAY]的默认模式.
已用时间: 0.462(毫秒). 执行号:0.
默认模式无法直接删除。
3.学习心得
不同的schema之间它们没有直接的关系，不同的schema之间的表可以同名, 也可以互相引用（但必须有权限），在没有操作别的schema的权限时，每个用户只能操作它自己的schema下的所有的表；
用户访问非默认模式下的对象时，需要在对象名前面添加模式名（模式名.对象名），访问默认模式下的对象可以不添加模式名；
用户的默认模式无法单独删除，需要直接删除用户，而非默认模式可以单独删除；
在删除用户时，会删除用户底下所有的模式，所以不要轻易做删除用户的操作

如有兴趣可以上达梦社区查看详情：https://eco.dameng.com