Halo DB 14 小白零基础系列（2）--用户的权限与管理

原创 Salvatore-zz 2024-01-08

27302

前言：

对于任何数据库来说，权限管理都是最基础也是最重要的功能，对于初学者DBA来说，想学好一种数据库，掌握其权限管理也是最基本的技能，关于数据库的四大语言可以给大家简单说下：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。本篇也会按照这个分类给大家介绍。

一、HaloDB中的Create操作。

1. 创建数据库：CREATE DATABASE

语法格式：

CREATE DATABASE name
    [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LOCALE [=] locale ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ]

部分参数解析：

database_name：数据库名称。

OWNER [ = ] user_name：数据库所有者。缺省时，新数据库的所有者是当前用户，取值范围：当前数据库集簇已存在的用户。

TEMPLATE [ = ] template：模板名。即从哪个模板创建新数据库。

ENCODING [ = ] encoding：指定数据库的编码，常见的取值编码有：GBK、UTF8等。

LC_COLLATE [ = ] lc_collate ：指定新数据库使用的字符集。例如，通过 lc_collate = 'GBK'设定该参数。

LC_CTYPE [ = ] lc_ctype ：指定新数据库使用的字符分类。
TABLESPACE [ = ] tablespace_name : 指定新创建数据库所使用的表空间。

CONNECTION LIMIT [ = ] connlimit ：数据库可以接受的并发连接数。

示例1:

halo0root=# create database halozz owner halozz;
CREATE DATABASE
----查询数据库是否建立成功
halo0root=# SELECT datname FROM pg_database;
datname
-----------
halo0root
template1
template0
testzz
halozz
----验证，切换到halozz数据库
halo0root=# \c halozz
You are now connected to database "halozz" as user "halo".
halozz=#

2. 创建用户：CREATE USER/ROLE

语法格式：

CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid

注意：CREATE USER现在是CREATE ROLE的别名。唯一的区别是，当命令的拼写为CREATE USER时，默认情况下会使用LOGIN，而当命令拼写为CREATE ROLE时会使用NOLOGIN。

部分参数解析：

name：用户名称。

role_name：角色名称。

CONNECTION LIMIT connlimit：连接限制参数。

示例2：

--查看现有用户：
halo0root=# SELECT usename FROM pg_user;
 usename 
---------
 halo
 halozz
(2 rows)
--新建用户halozztest密码为123123；
halo0root=# create user halozztest with password '123123';
CREATE ROLE
--验证用户是否创建成功：
halo0root=# SELECT usename FROM pg_user;
  usename   
------------
 halo
 halozz
 halozztest
(3 rows)
--系统登录验证：
[halo@halodb1 ~]$ psql -U halozztest -W 
Password: 
psql (1.0.14.10 (231130))
Type "help" for help.
halo0root=>

这里需要注意，验证的时候如果不加-W 参数，就会直接登录数据库。

3. 创建表空间：CREATE TABLESPACE

语法格式：

CREATE TABLESPACE tablespace_name
    [ OWNER { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER } ]
    LOCATION 'directory'
    [ WITH ( tablespace_option = value [, ... ] ) ]

部分参数解析：

tablespace_name：所需创建的表空间名称。

user_name : 新建表空间所属的用户名称。

directory：新创建表空间的物理目录位置。

示例3：

[root@halodb1 ~]# mkdir -p /data/tbs
[halo@halodb1 ~]$ chown -R halo:halo /data/tbs
[halo@halodb1 ~]$ chmod 700 /data/tbs
halo0root=# CREATE TABLESPACE halzoz_tbs LOCATION '/data/tbs';
CREATE TABLESPACE
halo0root=# ALTER TABLESPACE halzoz_tbs OWNER TO halo;
ALTER TABLESPACE

4. 创建索引：CREATE INDEX

语法格式：

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

部分参数解析：

UNIQUE：是否唯一，创建唯一性索引，每次添加数据时检测表中是否有重复值。如果插入或更新的值会引起重复的记录时，将导致错误产生。

index_name：要创建的索引名。

table_name：需要为其创建索引的表的名称。

column_name：表中需要创建索引的列的名称（字段名）。

WHERE predicate：创建一个部分索引，部分索引是一个只包含表的一部分记录的索引。（详细用法后面专门开专题介绍）

示例4：

--创建实验表
testzz=# CREATE TABLE employees (
testzz(#   id SERIAL PRIMARY KEY,
testzz(#   last_name VARCHAR(50), first_name VARCHAR(50),
testzz(#   age INT,
testzz(#   salary DECIMAL(10, 2)
testzz(# );
CREATE TABLE
--创建索引
testzz=# CREATE INDEX idx_employees_id
testzz-# ON employees (id);
CREATE INDEX
--验证索引是否存在
testzz=# SELECT indexname FROM pg_catalog.pg_indexes WHERE tablename = 'employees';
    indexname     
------------------
 employees_pkey
 idx_employees_id
(2 rows)

5. 创建视图: CREATE VIEW

视图与基本表不同，视图是一个虚拟的表。数据库中仅存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。若基本表中的数据发生变化，从视图中查询出的数据也随之改变。

语法格式：

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

部分参数解析：

REPLACE:如果视图已存在，则重新定义。

TEMP | TEMPORARY:创建临时视图。

View name:要创建的视图名称。

示例5：

--创建测试表
CREATE TABLE halodbt1 (
  id SERIAL PRIMARY KEY,
  last_name VARCHAR(50),  first_name VARCHAR(50),
  age INT,
  salary DECIMAL(10, 2));
--创建视图
CREATE VIEW v_halodbt1 AS
SELECT id, last_name, first_nameFROM halodbt1;
--查询视图
testzz=# \dv+
                            List of relations
 Schema |    Name    | Type | Owner | Persistence |  Size   | Description 
--------+------------+------+-------+-------------+---------+-------------
 public | v_halodbt1 | view | halo  | permanent   | 0 bytes | 
(1 row)

6. 创建同义词：

同义词功能默认不由羲和（Halo）数据库主动开启（非基础功能），交由用户选择是否开启使用，如需使用请

遵循下面的示例步骤开启使用。

语法格式：

CREATE [ OR REPLACE ] SYNONYM synonym_name 
FOR object_name;

部分参数说明：

synonym：创建的同义词名字，可以带模式名。

object_name：关联的对象名字，可以带模式名。

示例6：

--1、创建oracle.pg_synonym 
create table oracle.pg_synonym 
( 
synname name not null, 
synnamespace Oid not null, 
synowner Oid not null, 
synobjschema name default null, 
synobjname name not null, 
synlink text 
);
--2、设置_pg_synonym参数信息 
-检查当前羲和（Halo）数据库是否为ORACLE模式，如果不是，则需要设置成ORACLE模式，并使用pg_ctl restart重新启用 (后面专门介绍)
show database_compat_mode; 
-- 查询oracle.pg_synonym OID数据 
select oid from pg_class where relname = 'pg_synonym'; 
-- 设置当前会话生效 会话退出之后 如需使用，需重新设置 请使用以下语句 
set _pg_synonym = xx; -- xx为上诉查询到的oid 
-- 设置当前数据库生效 会话退出之后 下次登陆无需重新设置 
alter database {当前数据库名} set _pg_synonym = xx; 
-- 假设此处为halo0root数据库 
alter database halo0root set _pg_synonym = xx; 
-- 如果是设置数据库生效，使用上述命令之后，需要\q退出，然后再次登录 
\q 
-- 查看是否设置成功 
show _pg_synonym;
-- 以psql登录对应数据库 
-- view 
create view oracle.vb as select * from dual; 
create synonym syn_b for oracle.vb; 
select * from syn_b; 

-- table 
create table oracle.tc(a int); 
create synonym syn_c for oracl
e.tc; 
select * from syn_c;insert into syn_c values(1); 
select * from syn_c; 
drop synonym syn_c; 
-- procedure 
create procedure oracle.protestd 
as 
begin 
raise notice 'oracle.protestd'; 
end; 
/ 

create synonym syn_d for oracle.protestd; 
call syn_d; 
drop synonym syn_d;

二、HaloDB中的ALTER操作：

ALTER命令作为DBA日常变更最常用的命令，是作为运维人员必须熟练掌握的技能，下面为大家介绍一些常用的使用场景给各位参考。但是请格外注意：任何生产环境，请谨慎使用！！！

我是真怕哪位老哥生产环境一条命令喜提规律的生活三年起步。。。

1、修改数据库名称：ALTER DATABASE

示例7：

----查询现有数据库 
halo0root=# SELECT datname FROM pg_database;
  datname  
-----------
 halo0root
 template1
 template0
 testzz
 halozz
(5 rows)
--查询正在活跃有活动会话的数据库，如果有，请断开链接等活动会话结束后再进行该操作。
halo0root=# SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname='hot' AND pid<>pg_backend_pid();
 pg_terminate_backend 
----------------------
(0 rows)

--使用alter 修改数据库名称
生产环境谨慎使用！！！
halo0root=# alter database  halozz rename to halozz1;
ALTER DATABASE
--查询变更后的结果，可以看到数据库名称已经发生改变
halo0root=#  SELECT datname FROM pg_database;
  datname  
-----------
 halo0root
 template1
 template0
 testzz
 halozz1
(5 rows)

2、修改数据库相关参数：ALTER SYSTEM SET

示例8：

halo0root=# show shared_buffers;
 shared_buffers 
----------------
 128MB
(1 row)

halo0root=# ALTER SYSTEM SET shared_buffers = '256MB';
ALTER SYSTEM

[halo@halodb1 ~]$ pg_ctl restart
waiting for server to shut down....2024-01-08 13:04:06.236 CST [2007] LOG:  received fast shutdown request
2024-01-08 13:04:06.246 CST [2007] LOG:  aborting any active transactions
2024-01-08 13:04:06.248 CST [2007] LOG:  background worker "logical replication launcher" (PID 2014) exited with exit code 1
2024-01-08 13:04:06.248 CST [2009] LOG:  shutting down
2024-01-08 13:04:06.260 CST [2007] LOG:  database system is shut down
 done
server stopped
waiting for server to start....2024-01-08 13:04:06.545 CST [4916] LOG:  starting 羲和(Halo) 1.0.14.10 (231130) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2024-01-08 13:04:06.545 CST [4916] LOG:  listening on IPv4 address "0.0.0.0", port 1921
2024-01-08 13:04:06.545 CST [4916] LOG:  listening on IPv6 address "::", port 1921
2024-01-08 13:04:06.547 CST [4916] LOG:  listening on Unix socket "/var/run/halo/.s.PGSQL.1921"
2024-01-08 13:04:06.552 CST [4917] LOG:  database system was shut down at 2024-01-08 13:04:06 CST
2024-01-08 13:04:06.556 CST [4916] LOG:  database system is ready to accept connections
 done
server started
[halo@halodb1 ~]$ hsql
psql (1.0.14.10 (231130))
Type "help" for help.

halo0root=# show shared_buffers;
 shared_buffers 
----------------
 256MB
(1 row)

3、修改数据库所属者：ALTER DATABASE

示例9：

--查询当前数据库集簇中的所有数据库
halo0root=# \l
                               List of databases
   Name    | Owner  | Encoding |   Collate   |    Ctype    | Access privileges 
-----------+--------+----------+-------------+-------------+-------------------
 halo0root | halo   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 halozz1   | halozz | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | halo   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/halo          +
           |        |          |             |             | halo=CTc/halo
 template1 | halo   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/halo          +
           |        |          |             |             | halo=CTc/halo
 testzz    | halo   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)
--修改数据库halzz1的所有者为halo  
halo0root=# alter database halozz1 owner to halo;
ALTER DATABASE
--验证是否变更成功
halo0root=# \l
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges 
-----------+-------+----------+-------------+-------------+-------------------
 halo0root | halo  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 halozz1   | halo  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | halo  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/halo          +
           |       |          |             |             | halo=CTc/halo
 template1 | halo  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/halo          +
           |       |          |             |             | halo=CTc/halo
 testzz    | halo  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)

4、修改表空间名称：ALTER TABLESPACE

示例10：

--查询现有表空间
halo0root=# SELECT spcname FROM pg_tablespace;
  spcname   
------------
 pg_default
 pg_global
 halzoz_tbs
(3 rows)
--修改表空间名称
halo0root=# ALTER DATABASE database_name
halo0root-# ^C
halo0root=# alter tablespace halzoz_tbs  RENAME to halozz_tbs;
ALTER TABLESPACE
--查询验证结果，表空间名称已经修改
halo0root=# SELECT spcname FROM pg_tablespace;
  spcname   
------------
 pg_default
 pg_global
 halozz_tbs
(3 rows)

5、修改数据库使用表空间：ALTER DATABASE xxx set TABLESPACE

示例11：

--修改testzz使用的表空间为halozz_tbs
halo0root=# ALTER DATABASE testzz set TABLESPACE halozz_tbs;
ALTER DATABASE
halo0root=# SELECT pg_tablespace.spcname AS tablespace_name, pg_database.datname AS database_name
halo0root-# FROM pg_tablespace JOIN pg_database ON (pg_tablespace.oid = pg_database.dattablespace)
halo0root-# WHERE pg_database.datistemplate IS FALSE;
 tablespace_name | database_name 
-----------------+---------------
 pg_default      | halo0root
 pg_default      | halozz1
 halozz_tbs      | testzz
(3 rows)

结尾：

有关数据库的相关操作绝对不仅仅是这点，本次为大家先介绍一部分，后续的部分请继续关注Halo DB 14 小白零基础系列，虽然是慢工，但是绝对不是什么细活，有问题的朋友欢迎留言和通过主页联系方式与我取得联系，最后嘱咐大家一句，最近甲流严重，大家保护好身体，不说了，吃药去了。

最后修改时间：2024-01-08 17:11:50

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

HaloDB小白零基础入门系列（共34篇）

羲和数据库（简称 Halo ) 易景数通自主研发的一款数据库产品。Halo数据库拥有独特的多模解析引擎（现阶段主要兼容Oracle数据库），能大幅减少代码的修改量。

Halo DB 14 小白零基础系列（2）--用户的权限与管理

前言：

一、HaloDB中的Create操作。

1. 创建数据库：CREATE DATABASE

2. 创建用户：CREATE USER/ROLE

3. 创建表空间：CREATE TABLESPACE

4. 创建索引：CREATE INDEX

5. 创建视图: CREATE VIEW

6. 创建同义词：

二、HaloDB中的ALTER操作：

1、修改数据库名称：ALTER DATABASE

2、修改数据库相关参数：ALTER SYSTEM SET

3、修改数据库所属者：ALTER DATABASE

4、修改表空间名称：ALTER TABLESPACE

5、修改数据库使用表空间：ALTER DATABASE xxx set TABLESPACE

结尾：

文章被以下合辑收录

评论