CON_ID NAME OPEN_MODE
—— ——— ———-
2 PDB$SEED READ ONLY
3 SALESPDB READ WRITE
4 HRPDB READ WRITE
5 APP_ROOT READ WRITE
6 APP_PDB1 READ WRITE

# 切换到CDB
SQL> ALTER SESSION SET CONTAINER = cdb$root;

Session altered.

# 查看公共角色
SQL> SELECT role, common FROM cdb_roles WHERE common = ‘YES’ ORDER BY role;

ROLE COMMON
—————————— ——–
C##ADMIN_ROLE YES
C##MONITOR_ROLE YES
DBA YES
EXP_FULL_DATABASE YES
IMP_FULL_DATABASE YES

# 创建公共角色
SQL> CREATE ROLE c##app_read_role CONTAINER = ALL;

Role created.

SQL> CREATE ROLE c##app_write_role CONTAINER = ALL;

Role created.

SQL> CREATE ROLE c##app_admin_role CONTAINER = ALL;

Role created.

# 查看公共角色
SQL> SELECT role, common FROM cdb_roles WHERE common = ‘YES’ AND role LIKE ‘C##APP%’ ORDER BY role;

ROLE COMMON
—————————— ——–
C##APP_ADMIN_ROLE YES
C##APP_READ_ROLE YES
C##APP_WRITE_ROLE YES

# 验证公共角色创建成功

Session altered.

# 授予公共角色权限
SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, SELECT ANY TABLE TO c##app_read_role CONTAINER = ALL;

Grant succeeded.

SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, INSERT, UPDATE, DELETE TO c##app_write_role CONTAINER = ALL;

Grant succeeded.

SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, INSERT, UPDATE, DELETE, DROP, ALTER TO c##app_admin_role CONTAINER = ALL;

Grant succeeded.

# 查看公共角色权限
SQL> SELECT grantee, privilege, common FROM cdb_sys_privs WHERE grantee IN (‘C##APP_READ_ROLE’, ‘C##APP_WRITE_ROLE’, ‘C##APP_ADMIN_ROLE’) AND common = ‘YES’ ORDER BY grantee, privilege;

GRANTEE PRIVILEGE COMMON
—————- —————- ——–
C##APP_ADMIN_ROLE ALTER YES
C##APP_ADMIN_ROLE CREATE SESSION YES
C##APP_ADMIN_ROLE CREATE TABLE YES
C##APP_ADMIN_ROLE CREATE VIEW YES
C##APP_ADMIN_ROLE DELETE YES
C##APP_ADMIN_ROLE DROP YES
C##APP_ADMIN_ROLE INSERT YES
C##APP_ADMIN_ROLE UPDATE YES
C##APP_READ_ROLE CREATE SESSION YES
C##APP_READ_ROLE CREATE TABLE YES
C##APP_READ_ROLE CREATE VIEW YES
C##APP_READ_ROLE SELECT ANY TABLE YES
C##APP_WRITE_ROLE CREATE SESSION YES
C##APP_WRITE_ROLE CREATE TABLE YES
C##APP_WRITE_ROLE CREATE VIEW YES
C##APP_WRITE_ROLE DELETE YES
C##APP_WRITE_ROLE INSERT YES
C##APP_WRITE_ROLE UPDATE YES

# 验证公共角色权限配置成功

Session altered.

# 查看公共用户
SQL> SELECT username, common, account_status FROM cdb_users WHERE common = ‘YES’ AND username LIKE ‘C##APP%’ ORDER BY username;

USERNAME COMMON ACCOUNT_STATUS
—————————— ——– —————
C##APP_ADMIN YES OPEN
C##FGFGFGAPP_USER YES OPEN

# 授予公共用户角色
SQL> GRANT c##app_read_role TO c##app_user CONTAINER = ALL;

Grant succeeded.

SQL> GRANT c##app_admin_role TO c##app_admin CONTAINER = ALL;

Grant succeeded.

# 查看公共用户角色
SQL> SELECT grantee, granted_role, common FROM cdb_role_privs WHERE grantee IN (‘C##APP_ADMIN’, ‘C##FGFGFGAPP_USER’) AND common = ‘YES’ ORDER BY grantee, granted_role;

GRANTEE GRANTED_ROLE COMMON
———- ——————– ——–
C##APP_ADMIN C##APP_ADMIN_ROLE YES
C##FGFGFGAPP_USER C##APP_READ_ROLE YES

# 验证公共用户角色配置成功

Session altered.

# 配置公共角色密码
SQL> ALTER ROLE c##app_admin_role IDENTIFIED BY “Admin123#”;

Role altered.

SQL> ALTER ROLE c##app_read_role IDENTIFIED BY “Read123#”;

Role altered.

SQL> ALTER ROLE c##app_write_role IDENTIFIED BY “Write123#”;

Role altered.

# 查看公共角色
SQL> SELECT role, password_required FROM dba_roles WHERE role IN (‘C##APP_ADMIN_ROLE’, ‘C##APP_READ_ROLE’, ‘C##APP_WRITE_ROLE’) ORDER BY role;

ROLE PASSWORD_REQUIRED
—————————— ——————
C##APP_ADMIN_ROLE YES
C##APP_READ_ROLE YES
C##APP_WRITE_ROLE YES

# 验证公共角色管理配置成功

Session altered.

# 查看公共角色
SQL> SELECT role, common FROM cdb_roles WHERE common = ‘YES’ AND role LIKE ‘C##APP%’ ORDER BY role;

ROLE COMMON
—————————— ——–
C##APP_ADMIN_ROLE YES
C##APP_READ_ROLE YES
C##APP_WRITE_ROLE YES

# 查看公共角色权限
SQL> SELECT grantee, privilege, common FROM cdb_sys_privs WHERE grantee IN (‘C##APP_READ_ROLE’, ‘C##APP_WRITE_ROLE’, ‘C##APP_ADMIN_ROLE’) AND common = ‘YES’ ORDER BY grantee, privilege;

GRANTEE PRIVILEGE COMMON
—————- —————- ——–
C##APP_ADMIN_ROLE ALTER YES
C##APP_ADMIN_ROLE CREATE SESSION YES
C##APP_ADMIN_ROLE CREATE TABLE YES
C##APP_ADMIN_ROLE CREATE VIEW YES
C##APP_ADMIN_ROLE DELETE YES
C##APP_ADMIN_ROLE DROP YES
C##APP_ADMIN_ROLE INSERT YES
C##APP_ADMIN_ROLE UPDATE YES
C##APP_READ_ROLE CREATE SESSION YES
C##APP_READ_ROLE CREATE TABLE YES
C##APP_READ_ROLE CREATE VIEW YES
C##APP_READ_ROLE SELECT ANY TABLE YES
C##APP_WRITE_ROLE CREATE SESSION YES
C##APP_WRITE_ROLE CREATE TABLE YES
C##APP_WRITE_ROLE CREATE VIEW YES
C##APP_WRITE_ROLE DELETE YES
C##APP_WRITE_ROLE INSERT YES
C##APP_WRITE_ROLE UPDATE YES

# 验证公共角色成功

Session altered.

# 查看公共角色
SQL> SELECT role FROM dba_roles WHERE role LIKE ‘C##APP%’ ORDER BY role;

ROLE
——————————
C##APP_ADMIN_ROLE
C##APP_READ_ROLE
C##APP_WRITE_ROLE

# 测试公共角色在PDB中的使用
$ sqlplus c##app_user/”NewApp123#”@fgfgfgsalespdb

SQL*Plus: Release 19.0.0.0.0 – Production on Fri Mar 31 10:00:00 2026
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0

SQL> SHOW USER;

USER is “C##FGFGFGAPP_USER”

SQL> SELECT * FROM session_roles;

ROLE
——————————
C##APP_READ_ROLE

SQL> CREATE TABLE test_common_role (
id NUMBER,
name VARCHAR2(100)
);

Table created.

SQL> INSERT INTO test_common_role (id, name) VALUES (1, ‘Common Role Test’);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM test_common_role;

ID NAME
———- ——————-
1 Common Role Test

# 验证公共角色在PDB中的使用成功