模式简介
模式即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下的所有的表;
- 用户访问非默认模式下的对象时,需要在对象名前面添加模式名(模式名.对象名),访问默认模式下的对象可以不添加模式名;
- 用户的默认模式无法单独删除,需要直接删除用户,而非默认模式可以单独删除;
- 在删除用户时,会删除用户底下所有的模式,所以不要轻易做删除用户的操作。