七月 20, 2023
Oracle 角色是授予数据库用户的一组特权。数据库角色可以包含单个系统和对象权限以及其他角色。数据库角色使您能够在一个操作中向用户授予多个数据库权限。将权限组合在一起以简化权限管理非常方便。
Oracle 用法
Oracle 12c 引入了新的多租户数据库架构，支持创建通用角色和本地角色：
- 常见角色 — 在容器数据库 （CDB） 级别创建的角色。常见角色是存在于根以及每个现有和将来的可插入数据库 （PDB） 中的数据库角色。通用角色对于跨容器操作非常有用，例如确保普通用户在每个容器中都有一个角色。
- 本地角色 — 在特定可插拔数据库 （PDB） 中创建的角色。本地角色仅存在于单个可插入数据库中，并且只能包含在该角色所在的可插入数据库中应用的角色和特权。
常见角色名称必须以 c## 前缀开头。从 Oracle 12.1.0.2 开始，可以使用 COMMON_USER_PREFIX 参数更改这些前缀。
可以将CONTAINER子句添加到CREATE ROLE语句中以选择适用于角色的容器。
例子
创建通用角色。
show con_name;
CON_NAME
CDB$ROOT
CREATE ROLE c##common_role;
Role created.
创建本地角色。
show con_name;
CON_NAME
ORCLPDB
CREATE ROLE local_role;
Role created.
向数据库角色local_role授予特权和角色。
GRANT RESOURCE, ALTER SYSTEM, SELECT ANY DICTIONARY TO local_role;
被授予local_role角色的数据库用户现在拥有授予该角色的所有特权。
撤消数据库角色local_role的权限和角色。
REVOKE RESOURCE, ALTER SYSTEM, SELECT ANY DICTIONARY FROM local_role;
有关详细信息，请参阅 Oracle 文档中的配置权限和角色授权。
PostgreSQL 用法
在 PostgreSQL 中，没有登录权限的角色类似于 Oracle 中的数据库角色。PostgreSQL 角色与 Oracle 12c 中的常见角色最相似，因为它们在实例中所有数据库的范围内都是全局的。
- 角色在数据库实例级别定义，在 PostgreSQL 实例中的所有数据库中都有效。就数据库作用域而言，PostgreSQL 中的角色可以与 Oracle 12c 中的常见角色进行比较，因为它们是所有数据库的全局角色，而不是在每个数据库的单个作用域中创建的。
- PostgreSQL 中的
CREATE USER命令是CREATE ROLE命令的别名，但有一个重要的区别：使用CREATE USER命令时，它会自动添加LOGIN，以便角色可以作为数据库用户访问数据库。因此，要创建功能与 Oracle 角色类似的 PostgreSQL 角色，请务必使用CREATE ROLE命令。
具有连接权限的角色实质上是数据库用户。
- 角色是可以拥有对象并具有数据库权限的数据库实体。
- 角色可以被视为用户和/或组，具体取决于其使用方式。
- 角色在根级别定义，就数据库作用域而言，PostgreSQL 中的角色可以与 Oracle 12c 中的普通用户进行比较，因为它们是所有数据库的全局用户，而不是在特定数据库的单个作用域中创建的。
- 模式是独立于 PostgreSQL 中的角色/用户创建的。
| Oracle | PostgreSQL |
|---|---|
| 通用数据库用户 （12c） | 具有登录名的数据库角色 |
| 本地数据库用户 （12c） | 不适用 |
| 数据库用户 （11g） | 具有登录名的数据库角色 |
| 数据库角色 | 没有登录名的数据库角色 |
| 数据库用户与模式相同 | 数据库用户和模式是单独创建的 |
PostgreSQL 中的CREATE USER命令是CREATE ROLE命令的别名，但有一个重要的区别：CREATE USER命令会自动添加LOGIN参数，以便角色可以访问数据库并充当数据库用户。
例子
创建一个名为 myrole1 的新数据库角色，该角色将允许用户（为其分配角色）在 PostgreSQL 实例中创建新数据库。请注意，此角色将无法登录到数据库并充当数据库用户。此外，还可以向角色授予表hr.employees上的SELECT、INSERT和DELETE特权。
CREATE ROLE hr_role;
GRANT SELECT, INSERT,DELETE on hr.employees to hr_role;
通常，用作一组权限的角色不具有LOGIN属性，如前面的示例所示。
创建可以登录到数据库并指定密码的角色。
CREATE USER test_user1 WITH PASSWORD 'password';
CREATE ROLE test_user2 WITH LOGIN PASSWORD 'password';
CREATE USER与CREATE ROLE相同，只是它意味着可以登录到数据库。
当您初始化新的 PostgreSQL 实例时，将创建一个主用户作为数据库中功能最强大的用户。
创建可以登录到数据库的角色，并分配具有到期日期的密码。
CREATE ROLE test_user3 WITH LOGIN PASSWORD 'password' VALID UNTIL '2018-01-01';
创建一个功能强大的角色db_admin，使用户能够创建新数据库。此角色将无法登录到数据库。将此角色分配给数据库用户test_user1。
CREATE ROLE db_admin WITH CREATEDB;
GRANT db_admin TO test_user1;
创建新模式hello_world并在该模式内创建新表。
CREATE SCHEMA hello_world;
CREATE TABLE hello_world.test_table1 (a int);
总结
| 描述 | Oracle | PostgreSQL |
|---|---|---|
| 列出所有角色 | SELECT * FROM dba_roles; |
SELECT * FROM pg_roles; |
| 创建新角色 | CREATE ROLE c##common_role; 或 CREATE ROLE local_role1; |
CREATE ROLE test_role; |
| 将一个角色特权授予另一个数据库角色 | GRANT local_role1 TO local_role2; |
grant myrole1 to myrole2; |
| 向数据库角色授予对数据库对象的特权 | GRANT CREATE TABLE TO local_role; |
GRANT create ON DATABASE postgresdb to test_user; |
| 向角色授予对数据库对象的 DML 权限 | hr.employees to myrole1; |
GRANT INSERT, DELETE ON hr.employees to myrole1; |
| 列出所有数据库用户 | SELECT * FROM dba_users; |
SELECT * FROM pg_user; |
| 创建数据库用户 | CREATE USER c##test_user IDENTIFIED BY test_user; |
CREATE ROLE test_user WITH LOGIN PASSWORD 'test_user'; |
| 更改数据库用户的密码 | ALTER USER c##test_user IDENTIFIED BY test_user; |
ALTER ROLE test_user WITH LOGIN PASSWORD 'test_user'; |
| 外部身份验证 | 通过外部标识的用户提供支持 | 目前不支持；未来可以支持 AWS 身份和访问管理（IAM）用户 |
| 表空间配额 | Alter User c##test_user QUOTA UNLIMITED ON TABLESPACE users; |
不支持 |
| 向用户授予角色 | GRANT my_role TO c##test_user; |
GRANT my_role TO test_user; |
| 锁定用户 | ALTER USER c##test_user ACCOUNT LOCK; |
ALTER ROLE test_user WITH NOLOGIN; |
| 解锁用户 | ALTER USER c##test_user ACCOUNT UNLOCK; |
ALTER ROLE test_user WITH LOGIN; |
| 授予权限 | GRANT CREATE TABLE TO c##test_user; |
GRANT create ON DATABASE postgres to test_user; |
| 默认表空间 | ALTER USER C##test_user default tablespace users; |
ALTER ROLE test_user SET default_ tablespace = 'pg_global'; |
| 授予对表的选择权限 | GRANT SELECT ON hr.employees to c##test_user; |
GRANT SELECT ON hr.employees to test_user; |
| 授予对表的 DML 权限 | GRANT INSERT,DELETE ON hr.employees to c##test_user; |
GRANT INSERT,DELETE ON hr.employees to test_user; |
| 授予执行 | GRANT EXECUTE ON hr.procedure_name to c##test_user; |
grant execute on function "newdate"() to test_user;在括号内指定函数的参数类型。 |
| 限制用户连接 | CREATE PROFILE app_users LIMIT SESSIONS_PER_USER 5; ALTER USER C##TEST_USER PROFILE app_users; |
ALTER ROLE test_user WITH CONNECTION LIMIT 5; |
| 创建新的数据库模式 | CREATE USER my_app_schema IDENTIFIED BY password; |
CREATE SCHEMA my_app_schema; |
有关详细信息，请参阅 PostgreSQL 文档中的创建角色。