数据库安全操作与实践示例（openGauss&YanshanDB）

签署严格的数据保密协议：双方应签订详尽的数据保密协议，明确服务商对客户数据的访问权限、使用目的以及违约责任等内容。协议中应特别强调服务商对于敏感信息和用户隐私数据的保护义务。
提升操作准确性：通过明确的操作流程和标准化的操作规范，降低服务人员在处理数据库时的操作失误率，确保数据的准确性和完整性。
增强安全意识：加强对服务人员的安全教育和培训，提升其安全意识和操作规范意识，从源头上减少误操作的发生。
实施权限控制：建立严格的权限管理制度，对服务人员的数据库访问和操作权限进行精细化控制，防止非授权访问和误操作。
加强审计与监控：启用数据库操作日志记录功能，对服务人员的数据库操作行为进行审计和监控，及时发现并纠正误操作行为。
制定应急响应机制：制定完善的数据库误操作应急响应机制，明确在发生误操作时的处理流程和责任人，确保能够迅速响应并恢复数据库的正常运行。
……

3.2、服务/实施人员侧（包含但不限于）

不得桥接访问客户服务器，通过正常授权途径操作
不得恶意攻击、破坏客户数据库、不得窃取、收集、处理、泄露客户数据或信息。严格遵守法律法规
不得借用客户工牌、不得破解客户账户及密码、不得伪造申请相关信息。严格遵守客户规章制度
不得安装未经授权的第三方软件，可通过客户进行申请获取。
不得隐瞒或延误通报数据库安全事故，要及时报风险、报事故，以便将损失降到最小化。
严格按流程及实施方案操作，保证项目实施有“法”可依。
工作结束后按要求移交账号信息。
针对具体的数据库不得进行有风险的操作（尤其是除select外的任何操作）等
……

四、案例一：openGauss数据库数据误删除找回实践案例

4.1、openGauss闪回恢复

利用回收站的闪回恢复删除的表。数据库的回收站功能类似于windows系统的回收站，将删除的表信息保存到回收站中。利用MVCC机制闪回恢复到指定时间点或者CSN点。

openGauss闪回功能能够有选择性的高效撤销一个已提交事务的影响，从人为错误中恢复。采用闪回技术，恢复已提交的数据库修改前的数据，只需要秒级，而且恢复时间和数据库大小无关。

4.1.1、适用场景

1）误删除表的场景；

2）需要将表中的数据恢复到指定时间点或者CSN。

4.1.2、闪回支持两种恢复模式

1）基于类似windows系统回收站的恢复：适用于误DROP、误TRUNCATE的表的恢复。用户通过配置回收站开关，并执行相应的恢复命令，可以将误DROP、误TRUNCATE的表找回。

2）基于MVCC多版本的数据恢复：适用于误删除、误更新、误插入数据的查询和恢复，用户通过配置旧版本保留时间，并执行相应的查询或恢复命令，查询或恢复到指定的时间点或CSN点。

4.1.3、openGauss闪回技术前置条件（参数设置）

openGauss=> alter system set enable_recyclebin=on;

openGauss=> alter system set recyclebin_retention_time=3600;

openGauss=> alter system set undo_zone_count=16384;

openGauss=> alter system set undo_retention_time=3600;

[omm@node1 ~]$ gs_om -t restart

参数说明：

1）enable_recyclebin

参数说明：用来控制回收站的实时打开和关闭。

取值范围：布尔型，默认值：off

注意： recyclebin不支持Astore，只支持Ustore。

2）recyclebin_retention_time

参数说明：设置回收站对象保留时间，超过该时间的回收站对象将被自动清理。在执行purge recyclebin或者后台回收站清理线程时，将会删除超过该时间的回收站对象。

取值范围：整型，单位为s，最小值为1，最大值为2147483647。

默认值：15min（即900s）

3）创建Ustore存储引擎表的时候需要提前在postgresql.conf中配置undo_zone_count的值，该参数代表的是undo log的一种资源个数，建议配置为16384。

4）undo_retention_time

参数说明：设置undo旧版本保留时间。

取值范围：整型，单位为s，最小值为0，最大值为2147483647。

默认值：0

5）特别说明：在postgresql.conf中提前设置“enable_default_ustore_table=on”，默认指定用户创建表时使用USTORE存储引擎。

4.1.4、基于类似windows系统回收站的恢复（演示操作闪回DROP/TRUNCATE）

闪回TRUNCATE：可以恢复误操作或意外被进行truncate的表，从回收站中恢复被truncate的表及索引的物理数据。闪回truncate基于回收站机制，通过还原回收站中记录的表的物理文件，实现已truncate表的恢复。
闪回DROP：可以恢复意外删除的表，从回收站（recyclebin）中恢复被删除的表及其附属结构如索引、表约束等。闪回drop是基于回收站机制，通过还原回收站中记录的表的物理文件，实现已drop表的恢复。

--truncate table 并进行闪回恢复

--创建测试表，插入测试数据

drop table if exists omm1.test_table;

create table omm1.test_table

( id integer,

name character(10)

) with(storage_type=ustore);

insert into omm1.test_table values (1, 'a'),(2, 'b'),(3, 'c');

select * from omm1.test_table;

openGauss=> truncate table omm1.test_table ;

openGauss=> select * from omm1.test_table ;

openGauss=> select * FROM gs_recyclebin;

openGauss=> timecapsule table omm1.test_table to before truncate;

openGauss=> select * from omm1.test_table ;

--drop table 并进行闪回恢复

--创建测试表，插入数据

DROP TABLE IF EXISTS omm1.test_table;

CREATE TABLE omm1.test_table

(

r_reason_sk integer,

r_reason_id character(16),

r_reason_desc character(100)

) with(STORAGE_TYPE=USTORE);

INSERT INTO omm1.test_table VALUES (1, 'AA', 'reason1'),(2, 'AB', 'reason2'),(3, 'AC', 'reason3');

SELECT * FROM omm1.test_table;

openGauss=> drop table omm1.test_table ;

openGauss=> select * from gs_recyclebin ;

openGauss=> TIMECAPSULE TABLE omm1.test_table to BEFORE DROP;

openGauss=> select * from omm1.test_table;

4.1.5、基于MVCC多版本的数据恢复（数据恢复到指定时间点或者CSN）

--创建测试表，插入测试数据

drop table if exists omm1.time_table;

create table omm1.time_table

( idx integer,

snaptime timestamp,

snapcsn bigint,

timedesc character(100)

) with(storage_type=ustore);

--每次插入的时间间隔拉长一下，用于区分

insert into omm1.time_table select 1, now(),int8in(xidout(next_csn)), 'time1' from gs_get_next_xid_csn();

insert into omm1.time_table select 2, now(),int8in(xidout(next_csn)), 'time2' from gs_get_next_xid_csn();

insert into omm1.time_table select 3, now(),int8in(xidout(next_csn)), 'time3' from gs_get_next_xid_csn();

insert into omm1.time_table select 4, now(),int8in(xidout(next_csn)), 'time4' from gs_get_next_xid_csn();

insert into omm1.time_table select 5, now(),int8in(xidout(next_csn)), 'time4' from gs_get_next_xid_csn();

select * from omm1.time_table;

--delete后重新查询

openGauss=> delete omm1.time_table ;

openGauss=> SELECT * FROM omm1.time_table TIMECAPSULE TIMESTAMP to_timestamp('2024-08-21 18:26:58.618408','YYYY-MM-DD HH24:MI:SS.FF');

openGauss=> SELECT * FROM omm1.time_table TIMECAPSULE TIMESTAMP to_timestamp('2024-08-21 18:27:58.618408','YYYY-MM-DD HH24:MI:SS.FF');

--测试根据CSN的闪回能力

openGauss=> SELECT * FROM omm1.time_table TIMECAPSULE CSN 4141;

openGauss=> SELECT * FROM omm1.time_table TIMECAPSULE CSN 4166;

--既然可以找到数据，则就可以正常恢复

4.2、openGauss物理备份与恢复

通过物理文件拷贝的方式对数据库进行备份，以磁盘块为基本单位将数据从主机复制到备机。

4.2.1、使用场景

适用于数据量大的场景，主要用于全量数据备份恢复，也可对整个数据库中的WAL归档日志和运行日志进行备份。

4.2.2、物理备份恢复支持的工具

1）gs_backup：提供openGauss备份、恢复重要数据、显示帮助信息和版本号信息。

数据量小数据恢复份时间快。导出数据库相关信息的OM工具，可以导出数据库参数文件和二进制文件。帮助openGauss备份、恢复重要数据、显示帮助信息和版本号信息。在进行备份时，可以选择备份内容的类型，在数据库实例恢复时，通过静态配置文件中的数据库实例信息进行恢复。只恢复参数文件恢复时间较短。在进行还原时，需要保证各节点备份目录中存在备份文件。

2）gs_basebackup：提供做基础的物理备份。gs_basebackup的实现目标是对服务器数据库文件的二进制进行拷贝，其实现原理使用了复制协议。远程执行gs_basebackup时，需要使用系统管理员账户。gs_basebackup当前支持热备份模式和压缩格式备份。

恢复时可以直接拷贝替换原有的文件对服务器数据库文件的二进制进行全量拷贝，只能对数据库某一个时间点的或者直接在备时间作备份。结合PITR恢复，可恢复全量备份时间点后的某一时间点。份的库上启动数据库，恢复时间快。

3）gs_probackup：用于管理openGauss数据库备份和恢复的工具。它对openGauss实例进行定期备份，以便在数据库出现故障时能够恢复服务器。

可用于备份单机数据库，也可对主机或者主节点数据库备机进行备份，为物理备份。
可备份外部目录的内容，如脚本文件、配置文件、日志文件、dump文件等。
支持增量备份、定期备份和远程备份。
可设置备份的留存策略。

4.2.3、gs_probackup

gs_probackup是一个用于管理openGauss数据库备份和恢复的工具。它对openGauss 实例进行定期备份。可用于备份单机数据库或者数据库实例主节点，为物理备份。可备份外部目录的内容，如脚本文件、配置文件、日志文件、dump文件等。支持增量备份、定期备份和远程备份。增量备份时间相对于全量备份时间比较短，只需要备份修改的文件。当前默认备份是数据目录，如果表空间不在数据目录，需要手动指定备份的表空间目录进行备份。当前只支持在主机上执行备份。恢复时可以直接恢复到某个备份点在备份的库上启动数据库，恢复时间快。

4.2.3.1、前置条件

1）前提条件

可以正常连接openGauss数据库。
若要使用PTRACK增量备份，需在postgresql.conf中手动添加参数“enable_cbm_tracking = on”。
为了防止xlog在传输结束前被清理，请适当调高postgresql.conf文件中wal_keep_segments的值。

2）限制说明

备份必须由运行数据库服务器的用户执行。
备份和恢复的数据库服务器的主版本号必须相同。
如果要通过ssh在远程模式下备份数据库，需要在本地和远程主机安装相同主版本的数据库，并通过ssh-copy-id remote_user@remote_host命令设置本地主机备份用户和远程主机数据库用户的无密码ssh连接。
远程模式下只能执行add-instance、backup、restore子命令。
使用restore子命令前，应先停止gaussdb进程。
当存在用户自定义表空间时，备份的时候要加上 --external-dirs 参数，否则，该表空间不会被备份。
当备份的规模比较大时，为了防止备份过程中timeout发生，请适当调整postgresql.conf文件的参数 session_timeout、wal_sender_timeout。并且在备份的命令行参数中适当调整参数--rw-timeout的值。
恢复时，使用-T选项把备份中的外部目录重定向到新目录时，请同时指定参数--external-mapping。
增量备份恢复后，之前创建的逻辑复制槽不可用，需删除重建。
当使用远程备份时，请确保远程机器和备份机器的时钟同步，以防止使用--recovery-target-time恢复的场合,启动gaussdb时有可能会失败。
当远程备份有效时(remote-proto=ssh)，请确保-h和--remote-host指定的是同一台机器。当远程备份无效时，如果指定了-h选项，请确保-h指定的是本机地址或本机主机名。
当前暂不支持备份逻辑复制槽。

4.2.3.2、参数配置

--使用PTRACK增量备份，需在postgresql.conf中手动添加参数“enable_cbm_tracking = on”。

openGauss=> alter system set enable_cbm_tracking = on;

[omm@node1 ~]$ gs_om -t restart

4.2.3.3、初始化备份路径

--会在指定路径下生成backups和wal两个文件夹

[omm@node1 ~]$ gs_probackup init -B /home/omm/probkp

4.2.3.4、初始化备份实例

--在备份路径_backup-path_内初始化一个新的备份实例，并生成pg_probackup.conf配置文件，该文件保存了指定数据目录_pgdata-path_的gs_probackup设置。

[omm@node1 probkp]$ gs_probackup add-instance -B /home/omm/probkp -D /opt/openGauss/data/dn --instance instance_local

[omm@node1 probkp]$ find / -name "pg_probackup.conf"

[omm@node1 probkp]$ more /home/omm/probkp/backups/instance_local/pg_probackup.conf

4.2.3.5、全备

--将指定的连接、压缩、日志等相关设置添加到pg_probackup.conf配置文件中。

[omm@node1 probkp]$ gs_probackup set-config -B /home/omm/probkp --instance=instance_local

--显示位于备份目录中的pg_probackup.conf配置文件的内容。

--可以通过指定--format=json选项，

--以json格式显示。默认情况下，显示为纯文本格式。

[omm@node1 ~]$ gs_probackup show-config -B /home/omm/probkp --instance=instance_local --format json

--在进行增量备份之前，必须至少创建一次全量备份。

--记住备份ID，恢复的时候需要使用，backup ID: SINII4

--这里-d 指定要连接的数据库名称。该连接仅用于管理备份进程，因此您可以连接到任何现有的数据库。

[omm@node1 ~]$ gs_probackup backup -B /home/omm/probkp --instance instance_local -d postgres -b FULL -p 15400

--查看备份情况

[omm@node1 ~]$ gs_probackup show -B /home/omm/probkp --instance instance_local

4.2.3.6、增备

--这里我们对数据库进行一些变更后再进行增量备份。

openGauss=> insert into test_table values(4,'AD','resson4');

--增量备份

--增量备份ID : SINJYH

gs_probackup backup -B /home/omm/probkp --instance instance_local -d postgres -b PTRACK -p 15400

增量备份成功后，我们可以查询到对应的这条记录

[omm@node1 ~]$ gs_probackup show -B /home/omm/probkp --instance instance_local

4.2.3.7、合并备份数据

--将增量备份和全量备份合并（取最后一次增量备份的ID）

[omm@node1 ~]$ gs_probackup merge -B /home/omm/probkp --instance instance_local -i SINJYH

4.2.3.8、误删

-- rm -rf /opt/openGauss/data/dn/*

[omm@node1 dn]$ pwd

/opt/openGauss/data/dn

[omm@node1 dn]$ rm -rf *

[omm@node1 dn]$ ll

total 0

[omm@node1 dn]$ gsql -d postgres -p 15400 -ar

failed to connect /opt/openGauss/tmp:15400.

[omm@node1 dn]$ gs_om -t restart

Stopping cluster.

=========================================

Successfully stopped cluster.

=========================================

End stop cluster.

Starting cluster.

=========================================

[GAUSS-53600]: Can not start the database, the cmd is source /home/omm/.bashrc; python3 '/opt/openGauss/om/script/local/StartInstance.py' -U omm -R /opt/openGauss/app -t 300 --security-mode=off, Error:

[FAILURE] node1:

[GAUSS-50201] : The /opt/openGauss/data/dn/postgresql.conf does not exist..

4.2.3.9、恢复

--使用restore子命令前，应先停止gaussdb进程(本示例误删除后已经不存在数据库进程)。

[omm@node1 dn]$ gs_probackup restore -B /home/omm/probkp --instance instance_local -D /opt/openGauss/data/dn

--验证

4.2.3.10、清理备份

[omm@node1 dn]$ gs_probackup del-instance -B /home/omm/probkp --instance instance_local

--查看

五、案例二：YanshanDB数据库数据误删除找回实践案例

5.1、YashanDB表闪回

5.1.1、使用场景

某些时候，可能由于用户误操作等原因导致表的数据被删除，如果能在短期内就发现并希望立刻恢复被删除的数据，此时可以使用YashanDB提供的闪回功能，该功能支持查看过去的数据状态，并在时间上倒回指定时间点的数据，无需从备份中恢复数据。

5.1.2、闪回条件

需要拥有DBA权限，或者闪回相关系统权限。
在当前时间和目标闪回时间之间，表的结构不得发生变化。
表上的行迁移已开启，这表明发生闪回后rowid会发生变化。如果应用程序依赖于rowid，则不能闪回表。
对于被truncate的数据（包括truncate表或truncate分区），必须在truncate之前回收站已开启。在闪回时可能存在表truncate后已经插入了新的数据，需注意这些新数据将被放入回收站中。
对于被drop的表，必须在drop之前回收站已开启。
在当前时间和目标闪回时间之间，未对回收站执行purge。
可以执行闪回表的时间点由撤销保持期（UNDO_RETENTION）决定，建议将该参数设置为86400秒（24小时）或更长。
如在drop表的时候加了purge指令，删除的表不会放入回收站，而是直接释放所占空间，将无法闪回。
当表空间满时，即使不加purge指令，删除的表也不会放到回收站中。
LSC表无闪回功能。

5.1.3、delete操作闪回

--创建测试表

drop table if exists area_info;

create table area_info (id int ,city_name varchar(20));

insert into area_info values(1,'xian'), (2,'xianyang'), (2,'baoji');

SELECT * FROM area_info;

--查看并开启回收站row movement

SELECT ROW_MOVEMENT FROM DBA_TABLES WHERE TABLE_NAME ='AREA_INFO'

ALTER TABLE AREA_INFO ENABLE ROW MOVEMENT;

--或

ALTER SYSTEM SET RECYCLEBIN_ENABLED=ON;

--获取delete发生前的时间戳、SCN

SELECT CURRENT_TIMESTAMP FROM DUAL;

SELECT CURRENT_SCN FROM V$DATABASE;

--delect area_info

DELETE area_info;

COMMIT;

--通过时间戳进行表闪回

--通过SCN进行表闪回

5.1.4、drop操作闪回

--同上需要开启回收站（略）

--drop并查看回收站

drop table area_info;

SELECT object_name FROM DBA_RECYCLEBIN WHERE original_name = 'AREA_INFO';

--闪回

--表结构及表数据闪回

FLASHBACK TABLE "BIN$2490" TO BEFORE DROP;

--或者可以使用表的原始名称进行闪回

FLASHBACK TABLE AREA_INFO TO BEFORE DROP;

--还可以通过RENAME TO命令指定表的新名称

FLASHBACK TABLE "BIN$2490" TO BEFORE DROP RENAME TO AREA_INFO;

FLASHBACK TABLE AREA_INFO TO BEFORE DROP RENAME TO AREA_INFO_NEW;

5.1.5、truncate操作闪回

--同上需要开启回收站（略）

--truncate并查看回收站

truncate TABLE area_info;

SELECT object_name FROM DBA_RECYCLEBIN WHERE original_name = 'AREA_INFO';

--表闪回

FLASHBACK TABLE AREA_INFO TO BEFORE TRUNCATE;

5.2、YashanDB物理备份与恢复（SQL命令）

5.2.1、查看数据库状态机归档模式状态

--使用本方式执行备份时，要求数据库运行于OPEN状态且归档模式开启。

[yashan@localhost /]$ yasboot cluster status -c yashandb

select status from v$instance;

SELECT database_name,log_mode,open_mode FROM V$DATABASE;

5.2.2、备份（全量）

mkdir -p /home/yashan/backup

--全量备份

BACKUP DATABASE FULL FORMAT '/home/yashan/backup/full_20240812155350' TAG 'yas_full_backup' PARALLELISM 3;

5.2.3、恢复（全量）

--误删除操作

--使用本方式执行恢复时，要求数据库运行于NOMOUNT状态

--执行如下命令重启YashanDB数据库，并将实例启动至NOMOUNT阶段：

[yashan@localhost ~]$ yasboot cluster restart -c yashandb -m nomount

select status from v$instance;

--执行恢复

RESTORE DATABASE FROM '/home/yashan/backup/full_20240812155350' PARALLELISM 3;

--重启数据库

--执行如下命令重启YashanDB数据库，并将实例启动至OPEN阶段：

yasboot cluster restart -c yashandb

SQL> select status from v$instance;

--附说明：

--执行如下命令关闭YashanDB服务：

$ yasboot cluster stop -c yashandb

--执行如下命令开启YashanDB服务，同时会将实例切换至OPEN阶段

$ yasboot cluster start -c yashandb

--执行如下命令重启YashanDB数据库，并将实例启动至OPEN阶段：

$ yasboot cluster restart -c yashandb

--执行如下命令重启YashanDB数据库，并将实例启动至NOMOUNT阶段：

$ yasboot cluster restart -c yashandb -m nomount

--执行如下命令重启YashanDB数据库，并将实例启动至MOUNT阶段：

$ yasboot cluster restart -c yashandb -m mount

--如需进行正常的数据库操作，请将实例切换至OPEN阶段

opengauss yanshandb

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

关注作者

数据库安全操作与实践示例（openGauss&YanshanDB）

一、背景

二、目的

三、数据库安全操作事项

3.1、管理侧（包含但不限于）

3.2、服务/实施人员侧（包含但不限于）

四、案例一：openGauss数据库数据误删除找回实践案例

4.1、openGauss闪回恢复

4.1.1、适用场景

4.1.2、闪回支持两种恢复模式

4.1.3、openGauss闪回技术前置条件（参数设置）

4.1.4、基于类似windows系统回收站的恢复（演示操作闪回DROP/TRUNCATE）

4.1.5、基于MVCC多版本的数据恢复（数据恢复到指定时间点或者CSN）

4.2、openGauss物理备份与恢复

4.2.1、使用场景

4.2.2、物理备份恢复支持的工具

4.2.3、gs_probackup

4.2.3.1、前置条件

4.2.3.2、参数配置

4.2.3.3、初始化备份路径

4.2.3.4、初始化备份实例

4.2.3.5、全备

4.2.3.6、增备

4.2.3.7、合并备份数据

4.2.3.8、误删

4.2.3.9、恢复

4.2.3.10、清理备份

五、案例二：YanshanDB数据库数据误删除找回实践案例

5.1、YashanDB表闪回

5.1.1、使用场景

5.1.2、闪回条件

5.1.3、delete操作闪回

5.1.4、drop操作闪回

5.1.5、truncate操作闪回

5.2、YashanDB物理备份与恢复（SQL命令）

5.2.1、查看数据库状态机归档模式状态

5.2.2、备份（全量）

5.2.3、恢复（全量）

评论