openGauss数据库-头歌实验1-3 创建和管理模式

一、创建和使用模式

（一）任务描述

本关任务：基于 openGauss 学习创建模式的相关知识。

（二）相关知识

为了完成本关任务，你需要掌握：1.openGauss 的常用操作，2.SQL 创建模式相关语法。

（三）背景知识

schema 又称作模式。通过管理 schema，允许多个用户使用同一数据库而不相互干扰，可以将数据库对象组织成易于管理的逻辑组，同时便于将第三方应用添加到相应的 schema 下而不引起冲突。管理 schema 包括：创建schema、使用 schema、删除 schema、设置 schema 的搜索路径以及 schema 的权限控制。

（四）注意事项

1.只要用户对当前数据库有 CREATE 权限，就可以创建模式。
2.系统管理员在普通用户同名 schema 下创建的对象，所有者为 schema 的同名用户（非系统管理员）。
3.openGauss 包含一个或多个已命名数据库。用户和用户组在 openGauss 范围内是共享的，但是其数据并不共享。任何与服务器连接的用户都只能访问连接请求里声明的那个数据库。
4.一个数据库可以包含一个或多个已命名的 schema，schema 又包含表及其他数据库对象，包括数据类型、函数、操作符等。同一对象名可以在不同的 schema 中使用而不会引起冲突。例如，schema1 和 schema2都可以包含一个名为mytable的表。
5.和数据库不同，schema 不是严格分离的。用户根据其对 schema 的权限，可以访问所连接数据库的 schema 中的对象。进行 schema 权限管理首先需要对数据库的权限控制进行了解。
6.不能创建以PG_为前缀的 schema 名，该类 schema 为数据库系统预留的。
7.在每次创建新用户时，系统会在当前登录的数据库中为新用户创建一个同名 schema。对于其他数据库，若需要同名 schema，则需要用户手动创建。

（五）语法格式

根据指定的名称创建模式。

CREATE SCHEMA schema_name [ AUTHORIZATION user_name ] [WITH BLOCKCHAIN] [ schema_element [ ... ] ];
根据用户名创建模式。CREATE SCHEMA AUTHORIZATION user_name [ schema_element [ ... ] ];

（六）参数说明

1.schema_name

模式名称。

须知：

模式名不能和当前数据库里其他的模式重名。
模式的名称不可以“pg_”开头。

取值范围：字符串，要符合标识符的命名规范。

2.AUTHORIZATION user_name

指定模式的所有者。当不指定schema_name时，把user_name当作模式名，此时user_name只能是角色名。

取值范围：已存在的用户名/角色名。

3.WITH BLOCKCHAIN

指定模式的防篡改属性，防篡改模式下的行存普通用户表将自动扩展为防篡改用户表。

4.schema_element

在模式里创建对象的SQL语句。目前仅支持CREATE TABLE、CREATE VIEW、CREATE INDEX、CREATE PARTITION、CREATE SEQUENCE、CREATE TRIGGER、GRANT子句。

子命令所创建的对象都被AUTHORIZATION子句指定的用户所拥有。

说明：

如果当前搜索路径上的模式中存在同名对象时，需要明确指定引用对象所在的模式。可以通过命令 SHOW SEARCH_PATH 来查看当前搜索路径上的模式。

（七）示例语句

1.创建 schema

执行如下命令来创建一个 schema:

CREATE SCHEMA myschema;

如果需要在模式中创建或者访问对象，其完整的对象名称由模式名称和具体的对象名称组成。中间由符号“.”隔开。例如：myschema.table。

执行如下命令在创建 schema 时指定 owner:

CREATE SCHEMA myschema AUTHORIZATION omm;

当结果显示为如下信息，则表示成功创建一个名为 myschema 的 schema:

CREATE SCHEMA

2.使用 schema

在特定 schema 下创建对象或者访问特定 schema 下的对象，需要使用有 schema 修饰的对象名。该名称包含 schema 名以及对象名，他们之间用“.”号分开。
执行如下命令在 myschema 下创建 mytable 表:

CREATE TABLE myschema.mytable(id int, name varchar(20));

如果在数据库中指定对象的位置，就需要使用有 schema 修饰的对象名称。

执行如下命令查询 myschema 下 mytable 表的所有数据:

SELECT * FROM myschema.mytable;

执行成功返回：

id | name 
----+------
(0 rows)

（八）编程要求

根据提示，在右侧命令行进行操作，首先新建一个数据库 testdb，在 testdb 数据库中创建一个新模式 new_schema，并指定其拥有者为 gaussdb；然后在 new_schema 下创建一个表 mytable；表的格式为my_id int, my_info varchar(36)。

（九）编程实现

二、管理模式

（一）任务描述

本关任务：基于 openGauss 学习管理模式权限的相关知识。

（二）相关知识

为了完成本关任务，你需要掌握：1.openGauss 的常用操作，2.SQL 模式管理相关语法。

（三）背景知识

每个数据库包含一个或多个 Schema。数据库中的每个 Schema 包含表和其他类型的对象。数据库创建初始，默认具有一个名为 public 的 Schema，且所有用户都拥有此 Schema 的 usage 权限，只有系统管理员和初始化用户可以在 public Schema 下创建函数、存储过程和同义词对象，其他用户即使赋予 create 权限后也不可以创建上述三种对象。可以通过 Schema 分组数据库对象。Schema 类似于操作系统目录，但 Schema 不能嵌套。

相同的数据库对象名称可以应用在同一数据库的不同 Schema 中，而没有冲突。例如， a_schema 和 b_schema 都可以包含名为 mytable 的表。具有所需权限的用户可以访问数据库的多个 Schema 中的对象。

CREATE USER 创建用户的同时，系统会在执行该命令的数据库中，为该用户创建一个同名的 SCHEMA。

（四）注意事项

只有模式的所有者或者被授予了模式 ALTER 权限的用户有权限执行 ALTER SCHEMA 命令，系统管理员默认拥有此权限。但要修改模式的所有者，

ALTER SCHEMA schema_name RENAME TO new_name;

当前用户必须是该模式的所有者或者系统管理员，且该用户是新所有者角色的成员。

只有模式的所有者或者被授予了模式 DROP 权限的用户有权限执行 DROP SCHEMA 命令，系统管理员默认拥有此权限。

（五）语法格式

修改模式的防篡改属性。

ALTER SCHEMA schema_name { WITH | WITHOUT } BLOCKCHAIN

修改模式的名称。

ALTER SCHEMA schema_name RENAME TO new_name;

修改模式的所有者。

ALTER SCHEMA schema_name OWNER TO new_owner;

删除模式

DROP SCHEMA [ IF EXISTS ] schema_name [, ...] [ CASCADE | RESTRICT ];

（六）参数说明

1.IF EXISTS

如果指定的模式不存在，发出一个notice而不是抛出一个错误。

2.schema_name

现有模式的名称。

取值范围：已存在的模式名。

3.RENAME TO new_name

修改模式的名称。非系统管理员要改变模式的名称，则该用户必须在此数据库上有CREATE权限。

new_name：模式的新名称。

取值范围：字符串，要符合标识符命名规范。

4.OWNER TO new_owner

修改模式的所有者。非系统管理员要改变模式的所有者，该用户还必须是新的所有角色的直接或间接成员，并且该成员必须在此数据库上有CREATE权限。

new_owner：模式的新所有者。

取值范围：已存在的用户名/角色名。

5.{ WITH | WITHOUT } BLOCKCHAIN

修改模式的防篡改属性。具有防篡改属性模式下的普通行存表均为防篡改历史表，不包括外表、临时表、系统表。当该模式下不包含任何表时才可修改防篡改属性。另外，不支持临时表模式。toast表模式、dbe_perf模式、blockchain模式修改防篡改属性。

（七）示例语句

--创建模式ds。
openGauss=# CREATE SCHEMA ds;
--将当前模式ds更名为ds_new。
openGauss=# ALTER SCHEMA ds RENAME TO ds_new;
--创建用户jack。
openGauss=# CREATE USER jack PASSWORD 'xxxxxxxxx';
--将DS_NEW的所有者修改为jack。
openGauss=# ALTER SCHEMA ds_new OWNER TO jack;
--删除用户jack和模式ds_new。
openGauss=# DROP SCHEMA ds_new;
openGauss=# DROP USER jack;

（八）编程要求

根据提示，在右侧命令行进行操作，将上一关中的 new_schema 更名为 dc_tcpp，并将 dc_tcpp 的所有者修改为 omm。

（九）编程实现

三、模式的权限控制

（一）任务描述

本关任务：基于 openGauss 学习模式的权限控制方法。

（二）相关知识

为了完成本关任务，你需要掌握：1.openGauss 的基本操作，2.SQL 权限控制语法。

（三）基础知识

默认情况下，用户只能访问属于自己的 schema 中的数据库对象。如果需要访问其他 schema 的对象，则该 schema 的所有者应该赋予他对该 schema 的 usage 权限。

通过将模式的 CREATE 权限授予某用户，被授权用户就可以在此模式中创建对象。注意默认情况下，所有角色都拥有在 public 模式上的 USAGE 权限，但是普通用户没有在 public 模式上的 CREATE 权限。普通用户能够连接到一个指定数据库并在它的 public 模式中创建对象是不安全的，如果普通用户具有在 public 模式上的 CREATE 权限则建议撤销该权限。

（四）示例语句

撤销 PUBLIC 在 public 模式下创建对象的权限，下面语句中第一个“public”是模式，第二个“PUBLIC”指的是所有角色：

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

使用以下命令查看现有的schema:

SELECT current_schema();

执行如下命令创建用户 jack，并将 myschema 的 usage 权限赋给用户 jack:

CREATE USER jack IDENTIFIED BY 'xxxxxxxxxx';
GRANT USAGE ON schema myschema TO jack;

将用户 jack 对于 myschema 的 usage 权限收回:

REVOKE USAGE ON schema myschema FROM jack;

（五）编程要求

根据提示，在右侧命令行进行操作，创建用户 jackson，密码设置为jackson@123，并将 dc_tcpp 的 usage 权限赋给用户 jackson。

（六）编程实现

问题：

实验中schema id不一致问题原因：多次创建了schema，schema的oid会递增，所以需要严格按照编程要求的顺序创建schema，并且题目中的关卡的数据库数据是基于前一关卡的，建议从题目的第一关重新做，这样会比较顺利。

或者可以退出实训并释放实验资源，创建与管理模式实验的第2关按照这个顺序完成是没有问题的。