Oracle LiveLabs实验：探索 Oracle 23c 数据库的强大功能 - 架构权限和无锁预留

实验简介

Schema Privileges 和 Lock-Free Reservations均为23c的新特性，目前可以在23c 开发者免费版中测试。

实验申请地址见这里。

实验帮助见这里。

此实验给出的时长为7小时45分，实际上1个小时也就够了。

欢迎来到 Live Labs，我们在这里探索并深入研究 Oracle 数据库管理的迷人领域！ 在今天的会议中，我们将重点讨论两个有趣的主题：Oracle 架构级权限和无锁列保留。

架构级别权限
Oracle 架构级别权限在控制 Oracle 数据库内的访问和安全方面发挥着关键作用。 在 Oracle 中，模式可以被视为保存数据库对象（例如表、视图、过程等）的逻辑容器。 通过模式级别权限，我们可以授予或撤销用户或角色的权限，确保正确的个人对模式对象具有适当级别的访问权限。

在探索过程中，我们将深入探讨架构级权限的概念，了解各种可用的权限类型，例如 SELECT、INSERT、UPDATE、DELETE 等。 我们将学习如何向用户和角色授予和撤销这些权限，确保遵守最小权限原则，从而增强 Oracle 数据库的整体安全状况。

无锁列保留
接下来，我们将把注意力转移到无锁列预留的迷人世界。 在处理高度并发的系统和多用户环境时，管理数据一致性变得至关重要。 无锁列预留提供了一种有效的机制来实现这一点，而无需诉诸传统的锁定机制。

在我们的探索过程中，我们将揭开无锁列保留的内部工作原理，了解它们如何允许多个用户同时修改表中的特定列，而不会导致冲突或不一致。 我们将讨论实施此方法时的优点和注意事项，包括对性能的影响以及确保数据完整性所需的预防措施。

架构级别权限的主要优点：

• 细粒度访问控制：模式级别权限允许对模式内数据库对象的访问进行细粒度控制。 它使管理员能够在更细粒度的级别上授予或撤销权限，确保用户或角色只能访问必要的对象。

• 提高安全性：通过实施架构级别权限，您可以遵循最小权限原则，仅向用户提供执行任务所需的权限。 这有助于减轻未经授权的访问和潜在数据泄露的风险。

• 数据完整性：架构级别权限通过控制用户可以对架构对象执行的操作来维护数据完整性。 通过限制某些操作（例如更新或删除），您可以防止对关键数据的意外或恶意修改。

• 简化管理：通过架构级别权限，管理访问控制变得更加简化。 您可以在架构级别授予权限，从而简化管理流程，而不是为每个对象单独授予权限。

• 灵活性和可扩展性：架构级别权限在管理用户角色和权限时提供灵活性。 随着数据库的增长和发展，您可以轻松地在架构级别修改权限，以满足新的需求并确保不同用户组的正确访问级别。

无锁列预留的主要优点：

• 增强的并发性：无锁列保留允许多个用户同时修改特定列，而不会导致冲突或锁定争用。 这种方法提高了高并发系统中的并发性和可扩展性，减少了等待时间并增强了系统性能。

• 减少锁定开销：传统的锁定机制可能会带来大量开销，特别是在多个用户需要同时修改表中不同列的情况下。 无锁列预留消除了对锁的需求，最大限度地减少了争用并提高了整体系统效率。

• 改进的响应能力：通过消除锁定，无锁列保留使用户能够对特定列进行修改，而无需等待其他冲突的锁被释放。 这可以缩短响应时间并提供更具交互性的用户体验。

• 一致的数据访问：无锁列保留通过允许对特定列的并发修改来确保对数据的一致访问。 这有助于维护数据完整性并防止锁定冲突可能引起的不一致。

• 简化的事务管理：无锁列保留通过最大限度地减少对显式锁的需求并降低与死锁检测和解决相关的复杂性来简化事务管理。 这可以带来更简单、更稳健的事务工作流程。

在本实验室中，您将：

• 创建用户和表
• 授予架构权限
• 对无锁预留执行查询

为了举办这个研讨会，您需要：Oracle 23c 免费开发者版本数据库或在 LiveLabs 环境中运行的数据库。

实验 4：用户创建和表初始化

介绍

在本实验中，我们将重点关注为 Oracle 数据库环境设置必要的组件。 我们将创建两个用户，用户 1 和用户 2，并初始化架构 1 下的两个表。

目标

• 为 Oracle 数据库环境创建两个用户：用户 1 和用户 2。
• 初始化架构 1 下的两个表：inventory_no_reservations 和 inventory_reservations。
• 创建具有以下列的 inventory_no_reservations 表：id、product_name、数量和预算。
• 创建具有以下列的 inventory_reservations 表：id、product_name、数量、预算（可预留）和minimum_balance 约束。
• 将数据插入到 inventory_no_reservations 表中，其中包含两行：产品 A 和产品 B。
• 将数据插入到 inventory_reservations 表中，其中包含两行：产品 C 和产品 D。

任务 1：创建用户

connect / as sysdba
alter session set container = FREEPDB1;
connect system@FREEPDB1

-- 创建 schema1、用户 1 和用户 2。
CREATE USER s1 IDENTIFIED BY Welcome123;
CREATE USER u1 IDENTIFIED BY Welcome123;
CREATE USER u2 IDENTIFIED BY Welcome123;

-- 提供以下角色，以便用户可以连接到数据库、执行查询和访问数据库对象。
GRANT CONNECT to u1;
GRANT CREATE SESSION TO u1;
GRANT CONNECT to u2;
1
2
3
4
5
6
7
8
9
10
11
12
13

这里其实遗留了一个问题，就是CONNECT和CREATE SESSION有什么区别？
CREATE SESSION是权限，而CONNECT是角色。

SQL> col role for a10
SQL> select * from role_sys_privs where role='CONNECT';

ROLE       PRIVILEGE                                ADM COM INH
---------- ---------------------------------------- --- --- ---
CONNECT    SET CONTAINER                            NO  YES YES
CONNECT    CREATE SESSION                           NO  YES YES

SQL> select granted_role from dba_role_privs where grantee = 'U1' and default_role = 'YES';

GRANTED_ROLE
--------------------------------------------------------------------------------
CONNECT

SQL> select granted_role from dba_role_privs where grantee = 'U2' and default_role = 'YES';

GRANTED_ROLE
--------------------------------------------------------------------------------
CONNECT
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

目前为止，用户u1和u2仅有连接数据库的权限。

任务 2：在架构 1 下创建两个表

第一个表 inventory_no_reservations 将用作普通表，没有任何特殊功能。 第二个表 inventory_reservations 将使用无锁预留创建。 此功能可以有效管理特定列的预留，在我们的案例中，我们将其绑定到“预算”列。

CREATE TABLE s1.inventory_no_reservations (
  id NUMBER PRIMARY KEY,
  product_name VARCHAR2(50),
  quantity NUMBER,
  budget NUMBER
);

-- 注意reservable关键字
CREATE TABLE s1.inventory_reservations (
  id NUMBER PRIMARY KEY,
  product_name VARCHAR2(50),
  quantity NUMBER,
  budget NUMBER reservable CONSTRAINT minimum_balance CHECK (budget >= 400)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

任务 3：在每个表中插入几行

ALTER USER s1 QUOTA UNLIMITED ON users;
INSERT INTO s1.inventory_no_reservations
VALUES
(1, 'Product A', 10, 700), 
(2, 'Product B', 5, 200);
commit;

INSERT INTO s1.inventory_reservations
VALUES
(1, 'Product C', 8, 1000), 
(2, 'Product D', 3, 500);
commit;
1
2
3
4
5
6
7
8
9
10
11
12

实验 5：模式权限管理

介绍

在本实验中，我们将探讨 Oracle 中模式权限的管理。 通过向用户授予特定表的选择、插入、更新和删除权限，我们可以控制他们在模式内的访问。 我们还将检查模式特权与选择授权的功能。

加入我们，深入了解架构权限管理、以不同用户身份登录、查询表并观察架构权限对访问的影响。 我们将创建第三个表来演示没有模式权限的用户所面临的限制。 通过本实验，您将更深入地了解架构权限管理及其在 Oracle 数据库中的含义。

目标

• 了解 Oracle 中的模式权限管理。
• 了解授予对特定表的选择、插入、更新和删除权限。
• 将架构权限与选择授权及其限制进行比较。
• 观察架构权限对用户表访问的影响。
• 比较具有和不具有架构权限的用户访问能力。
• 演示基于架构权限的用户对新创建的表的访问限制。
• 探索无锁预留对性能和用户体验的好处。
• 区分常规表和无锁保留表。

先决条件

• 对Oracle数据库管理有基本的了解。
• 熟悉 SQL 和数据库概念，例如表、查询和用户权限。
• 具有创建和修改用户、表和特权的适当权限访问 Oracle 数据库环境。
• 现有架构或出于测试目的创建新架构的能力。 注意：确保您拥有执行实验室中提到的任务所需的访问权限和权限。

任务 1：向用户授予权限

connect system@FREEPDB1
GRANT SELECT, INSERT, UPDATE, DELETE ON s1.inventory_no_reservations TO u1;
GRANT ALL PRIVILEGES ON SCHEMA s1 TO u2;
1
2
3

任务 2：通过用户登录来测试架构权限新功能与选择授予的区别

架构级别权限：是在架构级别授予用户或角色的权限，允许他们对该架构内的所有对象执行某些操作。 这些权限通常应用于整个架构，而不是特定于对象的。 一些示例包括：创建、更改、删除、选择、插入、更新、删除和执行。

SELECT 授权：更具体，用于控制对模式内各个表或视图的读取访问。 当用户或角色被授予对特定表或视图的 SELECT 权限时，他们可以查询该特定对象的数据。 与架构级权限相比，SELECT 授权提供了更精细的控制级别，允许管理员限制对敏感数据的访问，同时允许对架构的其他部分进行读取访问。

任务 2 重点测试新的架构特权功能并将其与选择授予进行比较。 用户 1 将不具有架构级别权限，而用户 2 将具有。 在实验期间，我们将以每个用户身份登录并观察他们对架构中不同表的访问。

通过以用户 1 身份登录，我们将验证该用户并尝试查询表 1。正如预期的那样，由于缺乏架构权限，用户 1 将无法访问 inventory_reservations 表。

CONNECT u1/Welcome123@FREEPDB1;
show user
-- 成功
select * from s1.inventory_no_reservations;
-- 失败，无权限。ORA-00942: table or view does not exist
select * from s1.inventory_reservations;
1
2
3
4
5
6

但是，当我们以用户 2 身份登录时，我们将观察表 1 上的成功查询，并尝试查询架构中的第二个表 inventory_reservations。

CONNECT u2/Welcome123@FREEPDB1;
show user
-- 成功
select * from s1.inventory_no_reservations;
-- 成功
select * from s1.inventory_reservations;
1
2
3
4
5
6

此外，我们将基于架构 1 中现有的库存表创建第三个表 inventory_third_table。我们将向这个新表中插入数据并观察用户 1 和用户 2 的访问权限。而用户 2 将有权访问新表 表，用户 1 不会，突出显示架构权限对用户访问的影响。

connect system@FREEPDB1

CREATE TABLE s1.inventory_third_table (
  id NUMBER,
  product_name VARCHAR2(50),
  quantity NUMBER,
  budget NUMBER
);

INSERT INTO s1.inventory_third_table
VALUES
(3, 'Product E', 7, 29.99),
(4, 'Product F', 12, 39.99);
COMMIT;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

用户u1和u2尝试访问新建的表：

CONNECT u1/Welcome123@FREEPDB1;
show user
-- 失败，无权限
select * from s1.inventory_third_table;

CONNECT u2/Welcome123@FREEPDB1;
show user
select * from s1.inventory_third_table;
1
2
3
4
5
6
7
8

这太棒了！ 我们可以看出 u2 有权访问模式中的所有表。

至此，架构级别权限实验已完成！ 现在，您对如何在架构级别授予和管理权限有了深入的了解，从而允许不同的用户对架构内的表进行不同级别的访问。 这些知识使您能够对数据库对象实施安全、高效的访问控制。 继续利用架构级权限来确保组织良好且受控的数据库环境。 您在 Oracle 数据库管理方面的专业知识正在不断增长，并且您正在成为一名出色的数据库管理员！

该实验室将为下一次会议奠定基础，我们将在其中探索无锁预留功能。 我们将更新 inventory_reservations 表并检查常规表和无锁预留表之间的行为差异。 此功能解决了传统表引起的会话挂起问题，其中多次提交可能会导致延迟。

实验 6：无锁预留

介绍

在本实验中，我们将探讨无锁预留的概念及其对数据库事务的影响。 通过执行正常更新并利用无锁保留，我们将观察会话行为以及此功能的优点。

通过遵循本实验中的步骤，我们将深入了解正常更新期间会话的行为以及无锁预留的优势。 加入我们，探索这些令人惊叹的概念，并通过无锁预留体验改进的性能和用户体验。

目标

• 了解无锁预留的概念及其好处。
• 执行正常更新并观察会话行为，无需无锁预留。
• 体验由于未提交的更改和预算金额不足而导致的会话挂起。
• 探索无锁预留在提高性能和用户体验方面的优势。
• 使用无锁保留执行更新并观察是否存在会话挂起。
• 使用无锁预留处理由于低于“阈值”而导致的会话错误。
• 了解提交更改和回滚事务如何影响无锁预留。
• 通过无锁预留确认预算栏中预留金额的恢复。

先决条件

• 对数据库概念和事务有基本的了解。
• 熟悉 SQL 查询和语法。
• 访问 Oracle 数据库系统。
• 具有足够权限执行更新和创建表的用户凭据。
• 了解会话行为和事务管理。

任务1：正常更新

在数据库管理领域，Oracle 数据库中的传统更新曾经被认为是修改表中数据的标准方法。

任务 1 侧重于正常更新。 通过以用户 2 身份打开三个窗口，我们将对 inventory_no_reservations 表执行更新。 在窗口 1 中，我们将记录的预算减少 100，但不提交更改。 在窗口 2 中，我们将尝试将同一记录更新 100，从而由于窗口 1 中未提交的更改而导致会话挂起。类似地，在窗口 3 中，我们将把记录减少 200，从而导致另一次会话挂起。 然后，我们将提交窗口 1 中的更改，释放其他两个窗口之一。 如果任何释放的窗口在提交时遇到错误，我们将记录错误消息。 请注意，预算金额不足将导致中止。

-- 打开3个以用户u2连接的窗口
CONNECT u2/Welcome123@FREEPDB1;

-- 窗口1（成功，还未提交）
UPDATE s1.inventory_no_reservations
SET budget = budget - 100 where ID = 1;

-- 窗口2 （挂起）
UPDATE s1.inventory_no_reservations
SET budget = budget - 100
WHERE ID = 1;

COMMIT;

-- 窗口3（挂起）
UPDATE s1.inventory_no_reservations
SET budget = budget - 200
WHERE ID = 1;

COMMIT;

-- 窗口1提交，窗口2和窗口3解除挂起状态并提交。
commit;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

关键见解：随着 Oracle 不断发展以提供更高效和可扩展的替代方案（例如无锁预留），这种方法现在正逐渐成为过去。 与传统的更新过程不同，无锁预留利用先进的并发控制机制，使多个事务能够同时处理同一数据，而不会施加可能阻碍性能并引入潜在冲突的锁。

任务 2：无锁预留

这项创新培育了一个更加简化和响应迅速的数据处理环境，允许更快、更无缝的数据修改，而不会影响数据完整性。 无锁预留彻底改变了数据库处理更新的方式，在当前的数字环境中提供了更加优化和可扩展的解决方案。

任务 2 引入了无锁预留的概念。 使用相同的三个窗口，我们现在将对 inventory_reservations 表执行更新。 在窗口 1 中，我们将记录的预算减少 200，但无需做出承诺。 在窗口 2 中，我们将相同的记录减少 200，并且与之前不同的是，会话不会挂起。 在窗口 3 中，我们将尝试将记录减少 200，低于“阈值”，这将导致会话错误。 然后，我们将提交窗口 1 中的更改并回滚窗口 2 中的更改。最后，我们将在窗口 3 中再次运行事务，这应该会成功，因为我们已经恢复了预算列中的保留金额。

回顾下之前列的定义为：

budget NUMBER reservable CONSTRAINT minimum_balance CHECK (budget >= 400)
1

最初的预算为1000：

SQL> select budget from s1.inventory_reservations where id = 1;

    BUDGET
----------
      1000
1
2
3
4
5

注意：使用相同的 3 个窗口。

-- 窗口1
UPDATE s1.inventory_reservations
SET budget = budget - 200 where ID = 1;

-- 窗口2。没有阻塞，而且commit成功。因此其它窗口查询的结果为800
UPDATE s1.inventory_reservations
SET budget = budget - 200
WHERE ID = 1;

COMMIT;

-- 窗口1，提交
-- 现在所有会话查询结果为600
commit;

-- 窗口3，违反约束，报错
UPDATE s1.inventory_reservations
SET budget = budget - 300
WHERE ID = 1;
-- ORA-02290: check constraint (S1.MINIMUM_BALANCE) violated

-- 在窗口 2 中执行此语句以增加200。
UPDATE s1.inventory_reservations
SET budget = budget + 200
WHERE ID = 1;

COMMIT;

-- 转到窗口 3 并再次运行事务，它应该会成功，因为我们将 200 返还给保留列预算。
UPDATE s1.inventory_reservations
SET budget = budget - 300 where ID = 1;

COMMIT;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

恭喜您完成无锁预留实验室！ 您已经亲身体验了这一创新功能如何通过启用对特定列的并发更新而无需会话挂起来增强数据库性能。 通过无锁预留，您可以确保更顺畅、更高效的操作，提供无缝的用户体验。 继续利用这些强大的功能来优化您的数据库管理并在您的 Oracle 之旅中保持领先！

参考资料

Schema Privileges

了解此特性原理的文章，推荐看博客Schema-level privilege grants with Database 23c。

这里说明了Schema Privileges是介于繁琐的按对象赋予和简单但不安全的SELECT ANY之间的一种最优方案。

Lock-Free Reservations

Oracle 23c Help Center中的相关资料：

• Database Development Guide: Using Lock-Free Reservation
  这个文档比较详尽，占据了一章。

• Database Concepts: Lock-Free Reservation
  只是简介，占一小节。

Oracle Blogs中的相关博客：

• Lock-free reservation in 23c: how to start with
  这篇文章给出了一个示例，来源于Database Concepts，但测试更详尽。

• Lock-free reservation in 23c: scale your apps
  这篇文章的例子是作者自己写的，这个例子模拟了并行，证明了Lock-Free Reservation可提高应用的并发度。