迁移 Oracle 到 PostgreSQL: 角色和权限

十一月 27, 2024

摘要：在本文中，您将了解角色和权限，以及 Oracle 和 PostgreSQL 之间的区别。

介绍

从 Oracle 迁移到 PostgreSQL 时，一个主要的区别是，每个数据库如何处理角色和权限。Oracle 的权限模型在企业系统中已经根深蒂固，具有精细的用户控制，以及用户和角色之间的严格区分。PostgreSQL 虽然同样强大，但处理角色和权限的方式不同，提供了灵活性和简单性，但它也需要 Oracle 用户转变思维方式。

本文为 Oracle 专家提供了，了解 PostgreSQL 中的角色和权限，以进行实施的实用指南，以应对结构上的差异、常见挑战和最佳实践，从而实现迁移的顺利完成。

了解角色和权限

在任何数据库或软件系统中，管理访问权限对于维护安全、组织和高效运营都非常重要。实现此目的的两个关键要素是角色和权限。

角色：角色对应一组权限的集合，用于定义用户可以在系统中执行的操作。通过给用户分配特定角色，管理员可以确保个体或团体仅拥有执行其任务所需的访问权限，从而降低未经授权操作的风险。例如，HR 系统中的经理角色可能有权查看和修改员工记录，而员工角色可能只有查看自己记录的权限。
权限：权限是授予给角色或单个用户的特定权限，允许他们执行特定操作，例如读取数据、修改数据或执行管理函数。权限可以是广义的（例如，完整的数据库控制）或狭义的（例如，对单个表的只读访问权限）。在数据库系统中，权限可以控制对数据对象的 SELECT、INSERT、UPDATE 和 DELETE 等操作。

角色和权限的组合创建了一个安全的环境，其中每个用户的能力都得到了明确定义，从而减少了安全漏洞，并使管理员的管理更加容易。

Oracle 中的角色和权限

在 Oracle 中，角色和权限的结构旨在提供对用户访问的严格控制。以下是基本概念：

用户角色：Oracle 区分用户和角色，其中用户是个体账户，而角色是分配给用户的权限集合。通过角色，可以轻松地在多个用户之间分配一组一致的权限。
系统权限：这些权限控制数据库范围的功能，如 CREATE USER、ALTER SYSTEM 和 CREATE TABLE。它们使用户能够执行影响整个数据库环境的任务。
对象权限：这些权限侧重于特定对象，如表、视图或序列，具有 SELECT、INSERT、UPDATE 和 DELETE 等权限。
粒度和复杂性：Oracle 的模型专为复杂的企业环境而设计，允许进行详细的权限配置，让用户访问控制强大而复杂。
普通企业角色：DBA 通常会使用预定义的角色，例如 CONNECT、RESOURCE 和 DBA，这些角色为一般用户、开发人员和管理员访问权限提供了基准权限。

该系统支持精细的权限管理，但当角色有重叠或权限需要定期更新时，可能会变得复杂。

PostgreSQL 中的角色和权限

PostgreSQL 采用简化的基于角色的访问控制（RBAC）模型，将用户和角色视为同一系统的一部分。以下是 PostgreSQL 中的一些区别和功能：

统一角色：与 Oracle 不同，PostgreSQL 将角色视为用户和组的统一实体，这意味着任何角色都可以具有权限，并且可以分配给其他角色。用户只是具有登录功能的角色。
继承：PostgreSQL 中的角色可以从其他角色继承权限，从而创建一个层次化的权限结构。例如，如果 developer 继承自 base_user，则它会自动获得分配给 base_user 的所有权限。
默认角色 – Public：默认情况下，PostgreSQL 具有一个授予给所有用户的 public 角色，这意味着分配给 public 的任何权限都会应用到所有用户上面。这很有用，但需要仔细管理以避免暴露不必要的权限。
权限类型：PostgreSQL 提供了类似的对象级权限（SELECT、INSERT、UPDATE 等），但与 Oracle 相比，内置的系统权限较少。数据库超级用户的角色通常用于管理控制。
角色创建的灵活性：PostgreSQL 允许用角色表示单个用户或组，从而简化权限分配，而无需为每个用户或组使用不同的构造。
授权和撤销命令：PostgreSQL 使用与 Oracle 相同的 GRANT 和 REVOKE 命令，但存在一些语法差异。通过将权限应用到模式范围或数据库范围的权限，可以更广泛地管理权限。

Oracle 和 PostgreSQL 之间的主要区别

用户和角色

Oracle 区分用户（有权访问资源的账户）和角色（权限分组），而 PostgreSQL 中可以互换地使用用户、组和角色。

管理超级用户权限

PostgreSQL 有一个具有所有权限的超级用户角色，类似于 Oracle 的 SYSDBA。但是，建议谨慎使用超级用户角色，并依靠自定义角色来维护安全性。

授予和撤销权限的语法差异

虽然两个数据库都使用 GRANT 和 REVOKE，但 PostgreSQL 需要的权限级别较少。例如，在 PostgreSQL 中，CONNECT 和 TEMP 权限位于数据库级别，而不是像在 Oracle 中那样属于特定用户权限的一部分。

Oracle 用户需要注意的事项

将 Oracle 用户角色转换到 PostgreSQL

Oracle 用户习惯于定义具有特定权限的角色。在 PostgreSQL 中，可以考虑将带有 INHERIT 权限的用户组组合在一起，以复制 Oracle 角色的结构。例如，定义一个具有基本权限的角色 app_user，并将其分配给需要类似访问权限的各种用户。

明智地使用继承

PostgreSQL 中的继承机制提供了一种灵活的方式来授予权限，而无需重新定义角色。例如，如果您有一个 developer 角色还需要其他权限，可以给它分配为从 app_user 继承，并且仅添加额外的权限。这最大限度地减少了给每个用户执行大量 GRANT 命令的需要。

管理 Public 角色

默认情况下，PostgreSQL 通过 public 角色授予所有了用户访问某些对象的权限。请仔细审核该角色，以确保不会无意中分配敏感权限。您可以使用如下命令：

REVOKE ALL ON DATABASE mydb FROM public;
REVOKE ALL ON SCHEMA myschema FROM public;

处理超级用户权限

对于数据库的维护和高级别管理，PostgreSQL 的超级用户角色类似于 Oracle 的 SYSDBA。但是，最佳的做法是，仅在管理任务上面才使用超级用户。并且，还可以创建具有特定管理权限的角色，以进行数据库管理。

基于模式的权限

对于多用户环境，PostgreSQL 允许您在模式级别分配权限，这可以简化对大型数据库的访问控制。例如，要给所有 developer 角色的成员授予对一个模式的访问权限，可以执行以下操作：

GRANT USAGE ON SCHEMA dev_schema TO developer;
GRANT SELECT ON ALL TABLES IN SCHEMA dev_schema TO developer;

批量授予和撤销权限

在跨模式或表处理多个权限时，PostgreSQL 允许您在模式级别授予权限，从而提高权限管理效率。例如，给一个角色授予整个模式中的表的 SELECT 权限：

GRANT SELECT ON ALL TABLES IN SCHEMA reports TO analyst;

结论

要适配 PostgreSQL 的角色和权限模型，需要转变方法，特别是如果您习惯了 Oracle 对用户和角色的严格区分。PostgreSQL 提供了一种简化且灵活的访问模型，可以简化角色管理，但需要周到的配置来维护安全性。

通过利用 PostgreSQL 的权限继承、基于模式的权限，以及仔细审核 public 角色，Oracle 专家可以有效地过渡到 PostgreSQL，同时保持强大的访问控制。对于具有 Oracle 背景的新的 PostgreSQL 管理员，了解这些差异并应用最佳实践，可确保在 PostgreSQL 中提供一个安全、可扩展且可管理的环境。

了解更多

Oracle 到 PostgreSQL 迁移指南

介绍