模式（schema）
模式是对数据库的逻辑分割，是一个命名空间，在不同的模式下可以有相同名称的表、函数等。一个数据库包含一个或者多个模式，模式中又包含表、函数等数据库对象。
- 数据库对象的集合
- 一个数据库包含一个或多个模式
- 不同模式下可存在同名对象
- 创建模式需要用户对数据库有C权限
特殊模式（public）说明
未收回数据库public角色对public模式权限时，新建用户默认继承public角色权限，在任何数据库的public 模式下有usage和create的权限。
即：新建用户在未收回数据库public角色对public模式权限时，默认使用public模式，在该数据库拥有public模式对象的所有权限。
模式对象
数据库模式是数据库所有对象的集合，称为模式对象，KingbaseES SQL提供许多类型的模式对象，如下：
| 模式对象 | 描述 |
|---|---|
| 表 | 表按行存储数据。表是关系数据库中最重要的模式对象。 |
| 索引 | 索引提供指向存储在表的指定列中数据值的指针，实现对行进行直接、快速访问。KingbaseES数据库支持几种类型的索引。 |
| 分区 | 分区是大表和索引的一部分。每个分区都有自己的名称，并且可以选择拥有自己的存储特性。 |
| 视图 | 视图是对一个或多个表或其他视图中数据查询。可以将它们视为存储查询。视图实际上不包含数据。 |
| 序列 | 序列自动生成有序的整数值，可以由多个用户共享使用。通常，使用序列来生成主键值 |
| 同义词 | 同义词是另一个模式对象的别名对象。因为同义词只是一个别名，所以它不需要存储。除了在数据字典中的定义外，它不需要存储。 |
| PL/SQL程序和包 | PL/SQL是SQL的过程扩展。PL/SQL子程序是一个命名PL/SQL的块，可以用一组参数调用。PL/SQL包对逻辑相关的PL/SQL类型、变量和子程序进行分组。 |
模式对象访问
- 用户需要对模式有USAGE权限
- 对目标模式对象有select权限
- 不带模式名时沿search_path模式列表顺序查找，带模式名时忽略search_path
- 修改search_path需要数据库属主用户或超级用户
- sys_catalog模式总是被搜索，与search_path无关
- 模式重命名后，模式对象随模式名变更所属模式
模式权限
模式权限说明
通过ksql元命令\dn+ [schema_name]查询
| 权限 | 说明 | 标志 |
|---|---|---|
| ALL | UC权限 | |
| USAGE | 允许访问模式中的对象，允许使用currval和naxtval函数，允许创建表、函数和其他模式对象时使用该模式下的类型或域 | U |
| CREATE | 允许创建模式对象 | C |
常用操作
-- 模式创建
create schema <schema_name>;
-- 新建模式指定用户
CREATE SCHEMA <schema_name> AUTHORIZATION <user_name>;
-- 创建一个用户同名模式（注意：已存在用户）
CREATE SCHEMA AUTHORIZATION <user_name>;
-- 检查模式定义
\dn+ <schema_name>
-- 修改模式属主
ALTER SCHEMA <schema_name> OWNER TO <user_name>;
-- 修改模式名
ALTER SCHEMA <old_schema_name> RENAME TO <new_schema_name>;
-- 模式下创建表
CREATE TABLE <schema_name>.<table_name(col_name col_type,.....)>;
-- 查看模式对象信息
\d+ <schema_name>.*
-- 查看对象默认查找模式（顺序查找）
SHOW search_path;
-- 查看$USER
\echo :USER
-- 修改search_path
alter database <db_name> set search_path to "$user",<schema_name_list>,sys_catalog, pg_catalog;
select sys_reload_conf(); -- 重新加载配置
-- 模式授权
grant USAGE|ALL|CREATE ON SCHEMA <schema_name> to <user_name>;
grant USAGE|ALL|CREATE ON SCHEMA <schema_name> to PUBLIC;
-- 回收权限
revoke USAGE|ALL|CREATE on SCHEMA <schema_name> from <user_name>;
revoke USAGE|ALL|CREATE on SCHEMA <schema_name> from PUBLIC;
-- 删除模式
drop schema <schema_name> [cascade]; -- cascade为级联删除模式对象
试验测试
test=# \du+
角色列表
角色名称 | 属性 | 成员属于 | 描述
----------+--------------------------------------------+----------+------
dbadmin | 超级用户 | {} |
dbopra | 建立 DB | {} |
kcluster | 无法登录 | {} |
sao | 没有继承 | {} |
sso | 没有继承 | {} |
ssoadmin | | {} |
system | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {} |
testuser | | {} |
utyxl | 2个连接 +| {} |
| 密码有效直至2023-12-31 00:00:00+08 | |
utyxl01 | | {} |
utyxl02 | | {} |
-- 创建schtyxl用户同名模式
test=# CREATE SCHEMA AUTHORIZATION schtyxl; -- schtyxl用户不存在，创建模式失败
错误: 角色 "schtyxl" 不存在
db02=> \l db02
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
------+--------+----------+-------------+-------------+-----------------
db02 | dbopra | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | dbopra=c/dbopra+
| | | | | utyxl=c/dbopra
(1 行记录)
-- 测试数据库无C权限时模式创建
db02=> \c db02 utyxl;
You are now connected to database "db02" as userName "utyxl".
db02=> CREATE SCHEMA AUTHORIZATION utyxl; -- utyxl用户在db02上无C权限
错误: 对数据库 db02 权限不够
-- 赋权数据库C权限，创建utyxl用户同名模式
db02=> \c db02 dbopra;
You are now connected to database "db02" as userName "dbopra".
db02=> grant CREATE on DATABASE db02 to utyxl; -- 授权C
GRANT
db02=> \l+ db02
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限 | 大小 | 表空间 | 描述
------+--------+----------+-------------+-------------+-----------------+-------+-------------+------
db02 | dbopra | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | dbopra=c/dbopra+| 14 MB | sys_default |
| | | | | utyxl=Cc/dbopra | | |
(1 行记录)
db02=> \c db02 utyxl;
You are now connected to database "db02" as userName "utyxl".
db02=> CREATE SCHEMA AUTHORIZATION utyxl; -- 创建utyxl用户同名模式
CREATE SCHEMA
db02=> \dn+ utyxl
架构模式列表
名称 | 拥有者 | 存取权限 | 描述
-------+--------+----------+------
utyxl | utyxl | |
(1 行记录)
-- 查看模式查询路径
db02=> show search_path; -- 查看search_path
search_path
-----------------
"$user", public
(1 行记录)
db02=> \echo :USER -- 查看$USER
utyxl
-- 创建表
db02=> create table flg(id int);
CREATE TABLE
db02=> \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+-------------------------+--------+--------
public | sys_stat_statements | 视图 | system
public | sys_stat_statements_all | 视图 | system
utyxl | flg | 数据表 | utyxl -- 默认创建对象到同名模式下
db02=> \c - dbadmin
You are now connected to database "db02" as userName "dbadmin".
db02=# revoke USAGE on SCHEMA public from PUBLIC ; -- 回收对public模式的访问权限
REVOKE
db02=> \d -- 看不到public模式对象
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+------+--------+--------
utyxl | flg | 数据表 | utyxl
(1 行记录)
-- 修改模式名
db02=> alter schema utyxl rename to new_utyxl;
ALTER SCHEMA
db02=> \dn+ new_utyxl
架构模式列表
名称 | 拥有者 | 存取权限 | 描述
-----------+--------+----------+------
new_utyxl | utyxl | |
(1 行记录)
db02=> show search_path;
search_path
-----------------
"$user", public
(1 行记录)
-- 查询默认对象
db02=> \dt
Did not find any relations. -- 无对象
-- 指定模式名查询对象
db02=> \dt new_utyxl.*
关联列表
架构模式 | 名称 | 类型 | 拥有者
-----------+------+--------+--------
new_utyxl | flg | 数据表 | utyxl
(1 行记录)
-- 修改search_path，查询默认对象
db02=> alter database db02 set search_path to "$user",new_utyxl,public;
ALTER DATABASE
db02=> \c - utyxl
You are now connected to database "db02" as userName "utyxl".
db02=> show search_path ;
search_path
----------------------------
"$user", new_utyxl, public
(1 行记录)
db02=> \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
-----------+------+--------+--------
new_utyxl | flg | 数据表 | utyxl
(1 行记录)
-- 回收utyxl用户在new_utyxl上的U权限，查询对象
db02=> revoke USAGE on SCHEMA new_utyxl from utyxl;
REVOKE
db02=> \d -- utyxl用户在new_utyxl上无U权限，不能查询new_utyxl模式对象
Did not find any relations.
-- 删除new_utyxl模式
db02=> drop schema new_utyxl ;
错误: 无法删除 模式 new_utyxl 因为有其它对象倚赖它
描述: 表 new_utyxl.flg 倚赖于 模式 new_utyxl
提示: 使用 DROP .. CASCADE 把倚赖对象一并删除.
db02=> drop schema new_utyxl cascade; -- 递归强制删除new_utyxl模式
注意: 递归删除 表 new_utyxl.flg
DROP SCHEMA
db02=> \dn+ new_utyxl
架构模式列表
名称 | 拥有者 | 存取权限 | 描述
------+--------+----------+------
(0 行记录)
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
